Creating a Looping Query in VBA

[jmofstead]

I have no idea what I'm doing wrong. Esentially what I want to do is create a code that if a facility name equals a specific facility I want to run a macro.
For some reason i've run into a problem that since i'm using a continuous form, it only queries the first record. So I've attempted to create a code that would loop through all the records, but unfortunately I am not doing it correctly.
Here's my code:


Private Sub Form_Close()
DoCmd.OpenForm "Background"
If Me.Facility_Name = "Santaquin" Then
MsgBox "test does this work SQ", vbOKOnly
' DoCmd.SetWarnings (Off)
DoCmd.RunMacro "Santaquin Macro"
'DoCmd.SetWarnings (WarningsOn)
Else
GoTo NextRecord
Do Until EndRecord
End

Loop
End Sub

I get an error saying Block If without End If and I can find where I need to put the end if...
Then if I put the end if right befoer the end sub, I get a Label not defined error.
I have no idea what i should be doing, any help would be much appreciated.
Thanks!
Julie

 

[jzwp22]

For an IF..THEN statement, the structure can be as follows

IF..THEN
your code for when the condition of the IF statement is true
END IF

or

IF...THEN
your code for when the condition of the IF statement is true

ELSE

your code for when the condition of the IF statement is false

END IF


Looking at your code, you have END not END IF, so that is what the error refers.
However, I see you also have a loop DO UNTIL...LOOP. The loop must be contained completely within the IF statement for it to work, you have the END of the IF THEN in the middle of the loop.

Private Sub Form_Close()
DoCmd.OpenForm "Background"
If Me.Facility_Name = "Santaquin" Then
MsgBox "test does this work SQ", vbOKOnly
' DoCmd.SetWarnings (Off)
DoCmd.RunMacro "Santaquin Macro"
'DoCmd.SetWarnings (WarningsOn)
Else
GoTo NextRecord
Do Until EndRecord
End [COLOR="Red"]IF[/COLOR]
Loop
End Sub

Even with my suggestions, I don't think the code will accomplish what you want, but I'm not clear as to what your ultimate goal is. It sounds like you are entering the facility name somewhere and you want to do run a macro based on that selection. What does the macro do? We'll need a better understaning of the details of what you are trying to do. We also may need to know a little bit more about your table structure.