Clearing Text/List boxes, display of SQL query statement

[ljungers]

Have Form-1 with 3 text boxes and 1 command button. With any of the 3 boxes filled out and button is clicked, a Macro is performed that Opens a Query that has a WHERE clause that uses the 3 test boxes of data from form-1.

1) How do I cause the SQL command window not to show for that SQL command?

The query results is placed in a ListBox on Form-2 and desired rows in the list box are selected/clicked, followed by a click on a button that calls a VBA routine that extracts the bound column of the selected rows and calls a Report that prints the selected rows. When printing is finished the next thing that is called is Open Form-1 (returning to starting point).

What I need to do is clear the 3 text boxes on Form-1 and the list box on Form-2. Even when all 3 text boxes are changed (new data or cleared) the results that show after the query on Form-2 list box remain the same (does not change from the 1st used as to the list box). Can never seem to rest or clear these boxes.

2) How or where can I clear the Text Boxes and the List Box prior to their use on 2nd and subsequent uses? Would that be a properties setting or would I do this in the existing VBA code or new VBA code for an event?

Thanks

[MMcCarthy]

Have Form-1 with 3 text boxes and 1 command button. With any of the 3 boxes filled out and button is clicked, a Macro is performed that Opens a Query that has a WHERE clause that uses the 3 test boxes of data from form-1.

1) How do I cause the SQL command window not to show for that SQL command?

The query results is placed in a ListBox on Form-2 and desired rows in the list box are selected/clicked, followed by a click on a button that calls a VBA routine that extracts the bound column of the selected rows and calls a Report that prints the selected rows. When printing is finished the next thing that is called is Open Form-1 (returning to starting point).

What I need to do is clear the 3 text boxes on Form-1 and the list box on Form-2. Even when all 3 text boxes are changed (new data or cleared) the results that show after the query on Form-2 list box remain the same (does not change from the 1st used as to the list box). Can never seem to rest or clear these boxes.

2) How or where can I clear the Text Boxes and the List Box prior to their use on 2nd and subsequent uses? Would that be a properties setting or would I do this in the existing VBA code or new VBA code for an event?

Thanks

1)
Close the query in the macro as soon as you've used it.
2)
Me.Refresh will reset the form.

Merry Christmas

Mary

[ljungers]

Thanks for the reply and closing the query makes scense, but I am not sure where I should perform a close of that query, and how it should be done.

The Me.Refresh, is that to be placed in my VBA code. Do I use the form name with this refresh? I'm new to the VB coding and have had help with the code I'm using now.

Thanks

[ljungers]

I tried the Me.Refresh in both places of my VBA (after the DoCmd.openreport and the "back to main form" button. Seams to clear and accept new input after cycling two time's (click the select button and then click back, then select) and you see the new selected data, but the text boxew still have the original sealected data.

[ljungers]

I should clear up some possible confusion at this point. What I have is 2 forms, 1 report, 1 macro with a query and open form.

The 1st form Form-1, has 3 text boxes, 2 command buttons (Exit, Search).
1, 2 or 3 text boxes may be filled in or left blank. Clicking on the Exit button uses OnClick = ‘Event Procedure’ that performs a VBA “DoCmd.Quit”.
Clicking on the Search button uses OnClick = “Lukup_Macro” a macro, that 1st performs ‘OpenQuery’ called “LukupQuery” that uses the data in the Form-1 text boxes in a WHERE statement in a SELECT then 2nd performs ‘OpenForm’ named form-2.

Form-2 has 1 list box, 2 command buttons (Back to Lookup, Print Selected). List box is populated by the results of the query. 1 or multiple rows in the list box may be clicked/selected. The Print Selected button uses OnClick = ‘Event Procedure’ and performs a VBA PrintSelectedItems_Click that fetches selected info in the list box and performs a “DoCMD.OpenReport” that prints a report for each row selected, and returns to Form-1 when report is finished printing.

This all works great, the way it should work, BUT what I’m having a problem doing is:

Clearing out the text boxes on Form-1 when the button ‘Back to Lookup’ is clicked on (should they change there mind after looking at the results) so that new data may be entered for a new search.
Form-2 list box also needs to be cleared out as well. When returning back to Form-1 prior entered data remains and is displayed, and even if you change this information in these text boxes, clicking on Search the ListBox in Form-2 still displays the prior (1st results) from the query. This also happens when returning to Form-1 from printing the report on Form-2.

I need to clear/null out these text and list boxes so that a new search and selection can be done. I hope this clears up any confusion that may of arisen from my 1st post.

Thanks

[ljungers]

I guess what I want to know is how to reference a text box on another form while I'm working with the current form in VBA. I want to move blanks/null to the fields that are on another form.

[MMcCarthy]

I guess what I want to know is how to reference a text box on another form while I'm working with the current form in VBA. I want to move blanks/null to the fields that are on another form.

As long as the other form is open you can reference the texbox with:

Forms![FormName]![TextboxName]

Mary

[MMcCarthy]

I should clear up some possible confusion at this point. What I have is 2 forms, 1 report, 1 macro with a query and open form.

The 1st form Form-1, has 3 text boxes, 2 command buttons (Exit, Search).
1, 2 or 3 text boxes may be filled in or left blank. Clicking on the Exit button uses OnClick = ‘Event Procedure’ that performs a VBA “DoCmd.Quit”.
Clicking on the Search button uses OnClick = “Lukup_Macro” a macro, that 1st performs ‘OpenQuery’ called “LukupQuery” that uses the data in the Form-1 text boxes in a WHERE statement in a SELECT then 2nd performs ‘OpenForm’ named form-2.

Form-2 has 1 list box, 2 command buttons (Back to Lookup, Print Selected). List box is populated by the results of the query. 1 or multiple rows in the list box may be clicked/selected. The Print Selected button uses OnClick = ‘Event Procedure’ and performs a VBA PrintSelectedItems_Click that fetches selected info in the list box and performs a “DoCMD.OpenReport” that prints a report for each row selected, and returns to Form-1 when report is finished printing.

This all works great, the way it should work, BUT what I’m having a problem doing is:

Clearing out the text boxes on Form-1 when the button ‘Back to Lookup’ is clicked on (should they change there mind after looking at the results) so that new data may be entered for a new search.
Form-2 list box also needs to be cleared out as well. When returning back to Form-1 prior entered data remains and is displayed, and even if you change this information in these text boxes, clicking on Search the ListBox in Form-2 still displays the prior (1st results) from the query. This also happens when returning to Form-1 from printing the report on Form-2.

I need to clear/null out these text and list boxes so that a new search and selection can be done. I hope this clears up any confusion that may of arisen from my 1st post.

Thanks

When back to lookup is clicked add this line to the code

Forms![Form-1_Name].Refresh