May 4th, 2017, 09:31 AM
Join Date: Nov 2016
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
currRecord = Me.CurrentRecord
'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
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"