Closing a form automatically

[accessnewb]

yeah, I coded it as a VBA procedure and the event property is [Event Procedure].



Here is the code:

Private Sub searchTable_Click()

If CurrentProject.AllForms("Q_SearchResult").IsLoaded Then
Me.Requery
Else
DoCmd.OpenQuery "Q_Products", acViewNormal
End If

End Sub


I did Me.requery because formName.requery did not work.


Q_SearchResult is a form and Q_Products is the query

[June7]

This code is behind button on form that opens the results form, right? So Me. is not correct. And if the form is not open, don't you want to open it, not the query?

If CurrentProject.AllForms("Form_Q_SearchResult").IsL oaded Then
Form_Q_SearchResult.Requery
Else
DoCmd.OpenForm "Form_Q_SearchResult.Requery"
End If

[accessnewb]

I modified the code:

Private Sub searchTable_Click()
If CurrentProject.AllForms("Q_SearchResult").IsLoaded Then
Q_SearchResult.Requery
Else
DoCmd.OpenForm "Q_SearchResult", acNormal
End If
End Sub

Now when I click on the search button for the first time, the results are properly displayed. But when I click the search button a second time (without closing the results window), then I get the error "Run-time error '424': Object required"

[June7]

Try the form name with the Form prefix in the Requery line.
Form_Q_SearchResult.Requery

If you look at the list of objects in the VBA editor, you will see this prefix. So VBA needs it but the Access methods (DoCmd.OpenForm), where the form name is in quotes, apparently don't.

[boblarson]

Try the form name with the Form prefix in the Requery line.
Form_Q_SearchResult.Requery

I would HIGHLY suggest NOT using the Form_Q_SearchResult syntax. Using Form_ can do strange things (including opening up a copy of the form as hidden). It is much better to use

Forms!Q_SearchResult.Requery

[June7]

Interesting, Bob. I have never encountered any issues with that syntax. Will have to watch for. I do know about the other syntax. It is what must be used in Access controls and query objects.

[accessnewb]

@bob and June: I tried the code and it worked (the 'Forms!Q_SearchResult.Requery' code), but there is still one tiny little glitch. The search results are retrieved but it doesn't automatically go to the search results page :-(. I have to manually tab the page to view it.

[accessnewb]

hey solved it! I used the openForm code in the if condition and it worked:

Private Sub searchTable_Click()
If CurrentProject.AllForms("Q_SearchResult").IsLoaded Then
Forms!Q_SearchResult.Requery
DoCmd.OpenForm "Q_SearchResult", acNormal
Else
DoCmd.OpenForm "Q_SearchResult", acNormal
End If
End Sub


Is this code OK?

[June7]

I guess it's ok if it works. I would have thought if the form is open the OpenForm would not be needed and might even cause error. What do you mean by 'tab the page'?

[boblarson]

Interesting, Bob. I have never encountered any issues with that syntax.

It is something that isn't widely known and I can't tell you exactly all of the issues it can cause but apparently it has something to do with the way it is invoked compared to referring to it using the form's collection.

[accessnewb]

What do you mean by 'tab the page'?

I mean there are 2 forms, one to enter data in the search fields and hit 'search' and the other is where the results form. What I meant by "tab the page" was I have to manually tab the results form from the search form, and it doesn't do it automatically when I hit "search". Anyway, it worked now after the "openForm" code. I am not sure if it is OK though.

[boblarson]

Just a little info for you - You can set your Search button's property named "DEFAULT" to YES and then when you just hit your enter key from anywhere on that form it will click it.

[accessnewb]

@bob: thanks for the tip! Search button is much better now