Microsoft Access Development
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Remember me

Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Add This Thread To:   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
Unread Dev Articles Community Forums Sponsor:
Old May 4th, 2017, 09:31 AM
marcyharris61 marcyharris61 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
Join Date: Nov 2016
Posts: 4 marcyharris61 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 32 m 39 sec
Reputation Power: 0
VBA Code working spuractically

I have code that works sometimes: What I am trying to accomplish is I have three tables tbl_ActiveCodeLU, tbl_Master and tbl_CodesInLaborReport. I have a form that when you click on the "Verify Active Task Codes" button it takes the field WriterTaskNumber/Name and compares it to the tbl_ActiveCodeLU if it is the table it will put the WriterTaskName/Number data in the ActiveWritingCode field and make it Red to show that it is a duplicate, if it isn't in the table it puts "Request From Finance" in the ActiveWritingCode field. That code works and thought the background might be needed. At that point there is a checkbox next to the ActiveWritingCode field. If the ActiveWritingCode field says "Request From Finance" then the taskcode needs to be requested (there is another process for that which works). Once they request the taskcode they click the checkbox. This is the code that is working spuractically. What I would like it to do is when clicked it would take the writertasknumber/name and put it in the ActiveWritingCode field and also add it to the tbl_ActiveCodeLU. Also, I need it to take the ActiveWritingCode field and compare it to the tbl_CodesInLaborReport and if it is in the table put Yes in the WritingCodeInLaborReport field. It all works except the last part. Sometimes it works on the click, then the next time it doesn't but it will work if you forward to the next record and then go back to that form. Any help or suggestions of a better way of doing it is appreciated
Private Sub CheckAWC_Click()

If Me.CheckAWC = True Then
Me.ActiveWritingCode = Me.WriterTaskNumberName
End If

currRecord = Me.CurrentRecord
DoCmd.SetWarnings False

DoCmd.OpenQuery "UpdateQuery"

DoCmd.SetWarnings True

'currRecord = Me.CurrentRecord
'DoCmd.GoToRecord acDataForm, "frm_Regulatory", acGoTo, currRecord

Set MyDB = CurrentDb()
For Each qdf In MyDB.QueryDefs
If qdf.Name = "WritingCodeLaborReportQuery" Then
Query_Exists = True
MyDB.QueryDefs.Delete "WritingCodeLaborReportQuery"
End If
Next qdf
Writecode = Me.ActiveWritingCode
sSQL = "SELECT tbl_CodesInLaborReport.CodeInLaborCurrentMth, tbl_Master.[ActiveWritingCode] " & _
"FROM tbl_Master INNER JOIN tbl_CodesInLaborReport ON tbl_Master.[ActiveWritingCode] = tbl_CodesInLaborReport.CodeInLaborCurrentMth" & _
" where tbl_Master.[ActiveWritingCode] = " & """" & Writecode & """" & ";"

Set qdef = MyDB.CreateQueryDef("WritingCodeLaborReportQuery", sSQL)

sSQL = "select CodeInLaborCurrentMth from WritingCodeLaborReportQuery"
Set rs = MyDB.OpenRecordset(sSQL)

If rs.RecordCount <> 0 Then
Me.WritingCodeinLaborReport = "Yes"
Me.WritingCodeinLaborReport = "No"
End If

End Sub

Reply With Quote

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > VBA Code working spuractically

Developer Shed Advertisers and Affiliates

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap