Help clearing last search results!

[ElvisR]

I have a simple form. When I load it, it displays an old search result, i.e. Lastname Smith. I am not sure what I am not doing right.

The form is in two parts: the search form and the data entry (results) form.

Here is the code for the Results:

Option Compare Database

Private Sub cmdAll_Click()

Dim LSQL As String

'Clear criteria
GCriteria = ""

'Display all Doctors
LSQL = "select * from List"

Form_frmDoctors.RecordSource = LSQL
Form_frmDoctors.Caption = "Doctors (All doctors)"

End Sub

Private Sub cmdReport_Click()

'Open report
DoCmd.OpenReport "rptDoctors", acViewPreview, , GCriteria

End Sub

Private Sub cmdSearch_Click()

DoCmd.OpenForm "frmSearch", , , , , acDialog

End Sub

Private Sub DELETER_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

End Sub

Private Sub Form_Open(Cancel As Integer)

'Clear criteria when form is first opened
GCriteria = ""

End Sub

Private Sub Save_Click()
DoCmd.Save
End Sub

Private Sub Next_Record_Click()
On Error GoTo Err_Next_Record_Click


DoCmd.GoToRecord , , acNext

Exit_Next_Record_Click:
Exit Sub

Err_Next_Record_Click:
MsgBox Err.Description
Resume Exit_Next_Record_Click

End Sub
Private Sub Prev_Doctor_Click()
On Error GoTo Err_Prev_Doctor_Click


DoCmd.GoToRecord , , acPrevious

Exit_Prev_Doctor_Click:
Exit Sub

Err_Prev_Doctor_Click:
MsgBox Err.Description
Resume Exit_Prev_Doctor_Click

End Sub

Here is the code for the search:

Option Compare Database

Private Sub cmdClose_Click()

'Close form
DoCmd.Close

End Sub

Private Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter frmDoctors based on search criteria
Form_frmDoctors.RecordSource = "select * from List where " & GCriteria
Form_frmDoctors.Caption = "List (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearch"

End If

End Sub

Also, I would like to add a text box that shows the total number of records - same thing that you would see in the navigation bar, but without the current record number.

Thanks.

 

[Tim L]

Welcome to the Forums!

Form_frmDoctors.RecordSource = "select * from List where " & GCriteria

I'm going to hazard a guess here, has the Record Source for your form been saved into the form design? In the code snippit above you change the Record Source and I suspect that this was done and then the form was saved on closing it, without first resetting it to the original.

Ideally you should not change the Record Source of the form unless it is really necessary. In the case of just filtering records down it should not be necessary. If you are searching (filtering) for a particular record then set the Filter criteria and turn the filter on and off

Me.FilterOn = [True|False]

Where you use True to turn on the Filter and False to turn it off.

To have a look at how the filter criteria is set; open you form normally, then use Form Filter, and with the Form Filter Applied, go back to the design view and look at the form Properties\Data tab.

Alternatively:

1. Reset the Record Source for your form and ensure that after applying 'a search' that the form is not saved with the new record source, which what I think happened to screw it up for you originally.
2. Apply the 'All Doctors' code on opening the form:

   CALL cmdAll_Click

   in the On Open event of the form.

Although I reiterate that I think that you should use the filtering system as it can have less detrimental effects.

Incidentally, if you use the 'CODE' tags, it makes reading supplied code easier as indents are retained. The code tags can be applied by highlighting the code you have just pasted in and then clicking on the # icon above the text entry field. Also, try (if possible) to weed out any code that is not going to be needed to resolve the problem as it should make it easier for people to
focus on where the problem could be. For example, the navigation control code, report opening code, save code & close code are all superfluous.

HTH

Tim