Split form with 2 command buttons

[jeds]

Each button runs a query and I have got the first button to display the query results in the bottom of the split form.

Private Sub UpdateSignIn_Click()
On Error GoTo UpdateSignIn_Click_Err
    DoCmd.RunCommand acCmdRefresh
    DoCmd.OpenQuery "SignInOrderSort", acViewNormal, acEdit
    DoCmd.Close
    Me.Requery
    Me.Refresh
 
UpdateSignIn_Click_Exit:
    Exit Sub
UpdateSignIn_Click_Err:
    MsgBox Error$
    Resume UpdateSignIn_Click_Exit
End Sub

I had a lot of trouble getting the results to display in the form instead of just opening the query until I added the

DoCmd.Close
    Me.Requery
    Me.Refresh

Both buttons are calling select queries from the same table and the same fields in the same order. The above query sorts all fields by the 3rd field
(lookup values from related table). The second query is pulling only certain values from the lookup field. Clicking the button for the second field opens the query with the correct results but it will not display in the form.

Private Sub ListOfficers_Click()
On Error GoTo ListOfficers_Click_Err
    DoCmd.RunCommand acCmdRefresh
    DoCmd.OpenQuery "CheckOfficers", acViewNormal, acEdit
    DoCmd.Close
    Me.Requery
    Me.Refresh
 
ListOfficers_Click_Exit:
    Exit Sub
ListOfficers_Click_Err:
    MsgBox Error$
    Resume ListOfficers_Click_Exit
End Sub

It seems to me if the first button worked the the 2nd button should also close the query and display the results in the bottom of the split form.

Any suggestons appreciated.
Not a coder

jeds

Looking further into the event properties for the first button, when I click the ... button the VB code editor opens with the code pasted 1st in my post.

While my second button opens the macro editor. If I highlight the 2nd button in design view and click on view code, the VB editor opens with the code for the first button. If I pick the name of the second event procedure from the dropdown top left of the VB editor, the the procedure for the second button is there.

I have been researching and teaching myself Access for 3 months and the choice between macros, events and expressions has become no less of a blur.

Guess I should try and remove everything from the macro editor for the 2nd event (imagine I am mixing terminology now) and see if the vb code runs. Will post.

 

[jeds]

Guess I should try and remove everything from the macro editor for the 2nd event (imagine I am mixing terminology now) and see if the vb code runs. Will post.

The event runs (the query opens and closes) but the results do not show in the form. :banghead:

jeds

 

[jeds]

Yep well the answer was simple (thought of it myself!). The record source for the form is the 1st query.

So now I have to code the button(s) so they change the record source.
Input appreciated.

Mods, is there a required number of posts to add my sig?:

Guess this stuff is for more complicated minds...but I just can't let go. Arrrrgh!

 

[jeds]

Change record source for command buttons

WooHoo! figgered it out:

Private Sub UpdateSignIn_Click()
On Error GoTo UpdateSignIn_Click_Err
    DoCmd.RunCommand acCmdRefresh
    DoCmd.OpenQuery "SignInOrderSort", acViewNormal, acEdit
    DoCmd.Close
    [COLOR=blue][B]Form.RecordSource = "SignInOrderSort"
[/B][/COLOR]    Me.Requery
    Me.Refresh
    
UpdateSignIn_Click_Exit:
    Exit Sub
UpdateSignIn_Click_Err:
    MsgBox Error$
    Resume UpdateSignIn_Click_Exit
End Sub

Record source = name of the other query for the other button.

jeds