Closing a form automatically

[accessnewb]

Hi all,


I just started using access last week and it's been OK so far. But today, I discovered this very annoying issue:

I have created a form and a search button in that. When I click on the button, a query is executed, and I am taken to a new form with the search results. Now, the problem is, I have to explicitly close the search result everytime in order to do a new search. If I don't do that then I am still shown the old results. That kinda sucks. Is there any way to recity this? Some in-built feature in access??

[boblarson]

1. Why are you creating a new table for the search instead of just using the query results in a form?

2. Yes, you have to understand that a table that you are viewing is just another query. It isn't really the table. It is a system VIEW of the data. You can never really look at the actual table. So, like any query you have to requery it to get any changes that have occurred since you opened it last.

[June7]

If you change the search criteria then the form must be closed then reopened or requeried. Is the button VBA or macro code? Post code for analysis.

[accessnewb]

@borlarson: I am really new to access, and I don't know how to code in vb as of yet. I wrote a query and the results of the query is automatically generated in a new table. I also created another button to generate the result in a form. But I need to close the table/form in order to view the new result.

@June7: The code is not creating tables! The results of the query are automatically generated in a table. A part of the query is below:

SELECT *
FROM tableName AS t
WHERE (t.FieldName1 Like IIf(IsNull(FORMS!FormName!Text34),"*",(FORMS!FormN ame!Text34)) Or (IIf(IsNull(FORMS!FormName!Text34),t.FieldName1 is null))) And (t.FieldName2 Like IIf(IsNull(FORMS!FormName!Text35),"*",(FORMS!FormN ame!Text35)) Or (IIf(IsNull(FORMS!FormName!Text35),t.FieldName2 is null))) ;

[boblarson]

@June7: The code is not creating tables! The results of the query are automatically generated in a table. A part of the query is below:

SELECT *
FROM tableName AS t
WHERE (t.FieldName1 Like IIf(IsNull(FORMS!FormName!Text34),"*",(FORMS!FormN ame!Text34)) Or (IIf(IsNull(FORMS!FormName!Text34),t.FieldName1 is null))) And (t.FieldName2 Like IIf(IsNull(FORMS!FormName!Text35),"*",(FORMS!FormN ame!Text35)) Or (IIf(IsNull(FORMS!FormName!Text35),t.FieldName2 is null))) ;

a PART of the query is below? What is the rest of it?

[accessnewb]

@boblarson: sorry, I should have explained better. There are like 20 fields in the table and the query is the same for each field. I thought it would be redundant to post the entire query, so jus copy-pasted the query for 2 of the fields.

[boblarson]

So in other words you are just opening a query. You said it was opening in a table but what you really meant was that it was just opening the query (which looks like what a table looks like when you open one).

So you have to close that query to run it again. You should be able to hit the red exclamation mark from the query to requery it once you've changed the criteria on the form. But the simplest is to just build a form based on the query (and you can display it in datasheet view which will then look like it is the table/query) and then in your code or macro to open the results form you can issue a requery to update the recordset of the form so it doesn't have to be closed and reopened.

[accessnewb]

and then in your code or macro to open the results form you can issue a requery to update the recordset of the form so it doesn't have to be closed and reopened.

Can you explain how I can do this? Like I said, I am super new to access and I have no clue how to do that. Maybe you can post a link to some code?

[boblarson]

First off, go to your query in the navigation pane and click on the query to select it but don't open it. Then go to the CREATE TAB and under the forms area click MORE FORMS which then should give you a DATASHEET option. Select that and your form should be built to display your results from your query. Name it something like frmResults. Then with your other form (the one which has the criteria and, I assume, the button to open the query currently) go into design view and then select the button and go to the event properties. Does it say [Embedded Macro] there?

[accessnewb]

Yep, it does say [embedded macro] there.

[June7]

Don't know about Bob, but I use only VBA, not macros, for coding. Either way, code would have to be modified to check if the form is open and if it is, requery, if not open then open. I have no idea if macro can handle this. In VBA I use IsLoaded, something like:

If CurrentProject.AllForms("formname").IsLoaded Then
formname.Requery
Else
DoCmd.OpenForm "formname"
End If

[accessnewb]

@June7: For what event do I use that code? For the OnLostFocus event?

[June7]

Maybe in the 'Search' button click event.

[accessnewb]

@June7: The code doesn't work. I still need to explicitly close the results tab to requery

[June7]

Did you do as VBA procedure? If so, the Event property needs to be [Event Procedure].

Post your complete procedure and/or project for analysis.