Make and Update Query Not Running in VBA

[sross81]

Hello,

I have a form that when I delete a record I want two queries to run that go through and determine if any records need to be updated in the table because of that delete. There are two tables involved. When the delete occurs it deletes from the Issues table. If there is no longer a related issue in the table I want a field in the Encounter table called SecondEyesNeeded to be set to false.

I have a make table query that determines who should no longer be true that creates a new table and then I have an update query that takes all of the values that come back in the new table and relates to the Encounter table to do the update.

I have tried with VBA and a macro and although the queries work just fine when I run them on my own they do not seem to have any affect through this routes. I have stepped through it and it goes through every step but the value in the table is never updated.

Is there a better way to do what I am doing?

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_IssueUpdate_SecEyesRemove-1"
DoCmd.OpenQuery "qry_IssueUpdate_SecEyesRemove-2"
DoCmd.SetWarnings True

 

[AccessMSSQL]

Try commenting out your Docmd.SetWarnings False and step through the code again. See if you get any errors.

 

[sross81]

Thank you for replying!

I tried what you said and I see that I don't get any errors, but it adds 0 rows and updates 0 rows when I know it should be updating 1 row because I have checked in the queries manually. Here is my updated code. I tried some other examples I found online with switching the make and update query to SQL instead of just trying to get it to openquery.

Is this something to do with having it in the Form_Delete event?

Private Sub Form_Delete(Cancel As Integer)
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "SELECT Encounters.EncounterNbr, Issues.EncounterNbr AS IssueEncBlank, Encounters.[2ndEyesNeeded] INTO tble_RemoveSecondEyesNeeded " & _
"FROM Encounters LEFT JOIN Issues ON Encounters.EncounterNbr = Issues.EncounterNbr " & _
"WHERE (((Issues.EncounterNbr) Is Null) AND ((Encounters.[2ndEyesNeeded])=True));"
strSQL2 = "UPDATE tble_RemoveSecondEyesNeeded INNER JOIN Encounters ON tble_RemoveSecondEyesNeeded.EncounterNbr = Encounters.EncounterNbr SET Encounters.[2ndEyesNeeded] = False " & _
"WHERE (((Encounters.[2ndEyesNeeded])=True));"

'DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
'DoCmd.SetWarnings True

End Sub

 

[sross81]

I just realized that another thing stood out to me when I just ran that query. It deleted the record after it ran my sql code, but for my sql code to know what to update the record had to be deleted first so the Form_Delete event is the wrong event.

I changed to this event. I have never used it before, but it solved my problem:

Private Sub Form_AfterDelConfirm(Status As Integer)
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "SELECT Encounters.EncounterNbr, Issues.EncounterNbr AS IssueEncBlank, Encounters.[2ndEyesNeeded] INTO tble_RemoveSecondEyesNeeded " & _
"FROM Encounters LEFT JOIN Issues ON Encounters.EncounterNbr = Issues.EncounterNbr " & _
"WHERE (((Issues.EncounterNbr) Is Null) AND ((Encounters.[2ndEyesNeeded])=True));"
strSQL2 = "UPDATE tble_RemoveSecondEyesNeeded INNER JOIN Encounters ON tble_RemoveSecondEyesNeeded.EncounterNbr = Encounters.EncounterNbr SET Encounters.[2ndEyesNeeded] = False " & _
"WHERE (((Encounters.[2ndEyesNeeded])=True));"

'DoCmd.SetWarnings False
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
'DoCmd.SetWarnings True
End Sub