Button_Click event in Access VBA code doesn't open queries as expected

Question

I've written the following VBA code to execute some queries off of a form with two text boxes and a button to run the appropriate query. When I perform the click event though, nothing happens... my queries don't open regardless of input, and even with no input the error msgbox doesn't display. I'm still pretty new to VBA and coding in general so I might be missing something obvious, but I can't see any glaring issues. On button click the debug.print does print, so the code is being accessed, it just isn't doing anything. I imagine it has to do with the nested If statements and specifically the opening of the queries, but I can't figure it out despite multiple hours of googling.

This is the button click event.

Private Sub RunQ_Click()

Dim Frm As Form
Dim MdlF As TextBox
Dim PartF As TextBox

Set Frm = Forms("SearchFormParts")
Set MdlF = Forms!SearchFormParts.Controls("ModelNoF")
Set PartF = Forms!SearchFormParts.Controls("PartNoF")
Debug.Print "this code is working2"
With Forms!SearchFormParts.Controls("RunQ")
    If .OnClick = "" Then
        If MdlF = vbNullString Then
            If PartF = vbNullString Then
                DoCmd.OpenQuery ("CCModel")
            Else
                DoCmd.OpenQuery ("CCBoth")
            End If
        ElseIf PartF = vbNullString Then
            If MdlF = vbNullString Then
                DoCmd.OpenQuery ("CCPart")
            Else
                DoCmd.OpenQuery ("CCBoth")
            End If
        Else: MsgBox ("Please enter a value in either or both fields!")
        End If
    End If
End With

End Sub

Thank you :)

I can't figure it out despite multiple hours of googling - yet 30 seconds of debugging would have fixed it. F9 to toggle breakpoints, F8 to step through, Shift+F8 to step over; then you have the locals toolwindow, the immediate pane (Ctrl+G). Place a breakpoint on that With instruction, run it, then press F8 once to get to the If statement. Hover the .OnClick token and you'll see why next time you press F8 execution jumps to End If and out of the procedure. — Mathieu Guindon
– Mathieu Guindon, Commented Jul 5, 2017 at 20:43

Erik A · Accepted Answer · 2017-07-05 20:30:09Z

1

If .OnClick = "" Then results to false. It is set to [Event Procedure].

This causes none of your query code to be run. Remove it, and the corresponding End If

answered Jul 5, 2017 at 20:30

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathieu Guindon Over a year ago

That. Also remove that With block, replace that instruction separator colon Else: with an actual line break, and remove all these redundant parentheses in OpenQuery and MsgBox calls.

Collectives™ on Stack Overflow

Button_Click event in Access VBA code doesn't open queries as expected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related