How clear VBA variable on another form

[ts_hunter]

I have a search form (frmSearch) that has a Listbox (SearchResults) that is populated by a query. The WHERE clause on that query is 'like *searchtext*', and I get 'searchtext' from a field on the screen that the user can use to narrow the list of records in the Listbox. Once the user double clicks on the record they want, I put up an Edit form for them to modify the record. On the 'Form Close' event of the Edit form, I have this code:



Forms("frmSearch").SearchResults.Requery

Which reloads the SearchResults list box, however I would like to clear the 'searchtext' variable on the the first form so I see all the updated records on the first form. In the line of code above, I reference a Listbox on the other form, is there VBA code to set the 'searchtext' = ""?

[June7]

AFAIK, a query cannot reference a VBA variable. So I am somewhat confused by what you describe.

[ssanfu]

a field on the screen

I would guess that you are using an unbound text box control on a form.
In VBA you can use
Me.TextBox = "" or
Me.TextBox = NULL

Replace "TextBox" with the name of the actual control. Then requery the list box.

[ts_hunter]

AFAIK, a query cannot reference a VBA variable. So I am somewhat confused by what you describe.

Here is the VBA code that sets up the variable:



Here is the Query in Design mode:

[ts_hunter]

I would guess that you are using an unbound text box control on a form.
In VBA you can use
Me.TextBox = "" or
Me.TextBox = NULL

Replace "TextBox" with the name of the actual control. Then requery the list box.

I tried this but I still get a compile error (below). Isn't the 'Me' reference for the current form?

[June7]

Which line is triggering the error?

Using a variable and a hidden textbox in addition to the user input textbox seems overly complicated.

That is the listbox RowSource query? Could be:

SELECT Plan_ID, BOSS_Plan_Name, BOSS_CSM FROM BOSS_Data WHERE Plan_ID LIKE "*" & [SearchFor] & "*";

What is important about preserving trailing space? Never seen that.

Suggest the textbox AfterUpdate event instead of Change event.

Where is the code that opens the Edit form?

If you want to provide db for analysis, follow instructions at bottom of my post.

[ts_hunter]

Using a variable and a hidden textbox in addition to the user input textbox seems overly complicated.

What is important about preserving trailing space? Never seen that.

-This is sample code I found on the net, the hidden box works for me, so I kept it. The trailing space is so someone can't enter an invalid Plan ID.

Suggest the textbox AfterUpdate event instead of Change event.

I want the list to refresh after each keystroke


Anyway, I found the proper syntax to clear the text boxes in the calling form:


[Forms]![frmSearch]![HiddenSrchText] = ""
[Forms]![frmSearch]![SearchFor] = ""

[June7]

So you had the code behind the Edit form to clear the boxes on the Search form.

An alternative is to open the Edit form in dialog mode. This suspends code execution on the calling form until Edit form is closed.

Then code could be on the Search form.

Me.HiddenSrchText = ""
Me.SearchFor = ""