Clearing Text/List boxes for new search

[ljungers]

I'm on the home streach of my project and found that my "Reset for New Search" command button not working as desired. What should happen is that when the button is clicked a Event Procedure is run. That event procedure should clear the text boxes that are used for a search query that loads the results into a list box on that same form.

When clicked, all is cleared in the text boxes, and the list box is cleared but shows column seperator lines in 1st row only . After entering new search data (let's say box 2 of the 3 boxes, and the search data is on the table) the "Search" button is clicked, the list box clears completely and nothing showing.

What I discovered is if I enter something in all 3 text boxes (say x, x, x) and click "Search" nothing happens, and then go back and delete 1st box, enter valid data in 2nd, delete 3rd and click the "Search" button again, the list box get populated like it should of after clearning the 1st search.

I can change any of the text box values and click "Search" not using the clear button, and that works loading new rows in that list box. Just the clear before a new search has problems.

Here is the code for the clear button click:

Private Sub clearButton_Click()
On Error GoTo Err_clearButton_Click
' clear the 3 text boxes, list box so that another search/query can be done
Me.WhatLastName.Value = ""
Me.WhatFirstName.Value = ""
Me.WhatMedRecNo.Value = ""
Me.SelectPrintItems.RowSource = ""
Me.Refresh

Exit_clearButton_Click:
Exit Sub

and here is the code for the search button click:

Private Sub SearchQuery_Click()
On Error GoTo Err_SearchQuery_Click

'Error checking to make sure user entered values for Last Name and First Name.
If Nz(Me.WhatLastName, "") & _
Nz(Me.WhatFirstName, "") & _
Nz(Me.WhatMedRecNo, "") = "" Then
MsgBox ("Please enter a Search value, all 3 may not be blank")
GoTo Exit_SearchQuery_Click
Else
Me.SelectPrintItems.RowSource = "Lukup_Query"
Me.SelectPrintItems.Requery

End If
Exit_SearchQuery_Click:
Exit Sub

The Lukup_Query is just a Select using the What... fields above in a WHERE statement to select for populating the list box.

Should work but I must be overlooking something that is obvious

[MMcCarthy]

'Me.SelectPrintItems.RowSource = ""

Comment out this line and see what happens

[ljungers]

'Me.SelectPrintItems.RowSource = ""

Comment out this line and see what happens

When commented out all text & list box get cleared (no col seperators) but functionality still remains the same.

[MMcCarthy]

When commented out all text & list box get cleared (no col seperators) but functionality still remains the same.

Try doing the same with the text boxes just leaving Me.Refresh

[ljungers]

Try doing the same with the text boxes just leaving Me.Refresh

When all are commented out the Clear does nothing, but the Search works every time with any changes to any of the text boxes.

[MMcCarthy]

When all are commented out the Clear does nothing, but the Search works every time with any changes to any of the text boxes.

     Me.WhatLastName = Null
    Me.WhatFirstName = Null
    Me.WhatMedRecNo = Null

Try this instead for the text boxes.

BTW are the textboxes bound to controls. If not the refresh should have worked.

Mary

[ljungers]

     Me.WhatLastName = Null
    Me.WhatFirstName = Null
    Me.WhatMedRecNo = Null

Try this instead for the text boxes.

BTW are the textboxes bound to controls. If not the refresh should have worked.

Mary

that seems to work ok, just when the form open 1st time and when the Clear button is clicked the entire table is listed in the list box (29230 records). In design mode the text boxes and list box all show Unbound

[MMcCarthy]

that seems to work ok, just when the form open 1st time and when the Clear button is clicked the entire table is listed in the list box (29230 records). In design mode the text boxes and list box all show Unbound

Try setting the listbox RowSource to "" again while leaving the textboxes set to null.

[ljungers]

Try setting the listbox RowSource to "" again while leaving the textboxes set to null.

That seems to work as to clearing text boxes, and the list box, except the column dividers for the 1st row show, but can live with that.

The search works OK now and the only thing is that the list box loads the entire table when the form is first opened.

[MMcCarthy]

That seems to work as to clearing text boxes, and the list box, except the column dividers for the 1st row show, but can live with that.

The search works OK now and the only thing is that the list box loads the entire table when the form is first opened.

Call the clear button event in the On load event of the form

Private Sub Form_Load()
 
   clearButton_Click
 
End Sub

Mary

[ljungers]

Call the clear button event in the On load event of the form

Private Sub Form_Load()
 
   clearButton_Click
 
End Sub

Mary

Thanks Mary that worked. I think the list box is loading but being cleared after. The reason I say this is the time it takes for the form to open.

[MMcCarthy]

Thanks Mary that worked. I think the list box is loading but being cleared after. The reason I say this is the time it takes for the form to open.

The other option is to put it in the on close event of the form.

[ljungers]

Thanks to all of you that have helped me even on a holiday. It works the way I wanted it to function.