Search Box VBA Help

[toleafs]

Hi

I am using the following code, the issue I am having is that when an invalid "Account Number" is entered in the unbound search box (cboReportName) the form goes completely blank. Can someone help with code saying if there are no results give Msgbox " No records ....." and set focus back on cboReportName. I tried the following but no luck.

Thanks in advance for your help

MsgBox "Match Not Found For: " & cboReportName & " - Please Try Again.", _
, "Invalid Search Criterion!"
cboReportName.SetFocus


-----------------------------
Dim LSQL As String
Dim LSearchString As String
Dim RecordCount As String
Dim lRecCount As Integer

If Len(cboReportName) = 0 Or IsNull(cboReportName) = True Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criteria!"
Exit Sub

Else

LSearchString = cboReportName

'Filter results based on search string
LSQL = "select * from qRECS"
LSQL = LSQL & " where strAccountNumber = " & LSearchString & ""

Form_frmRECS.RecordSource = LSQL

lblTitle.Caption = "Account Number Details: Filtered by: '" & LSearchString & "'"

'Clear search string
cboReportName = ""

MsgBox "Results have been filtered. For Account Number: " & LSearchString & ".", vbOKOnly, "Account Search Criteria!"

End If

 

[ranman256]

none of this code is needed.
show a form of all records, (continuous form)
enter id into the search box, use the code below to filter:

sub txtBox_Afterupdate()

if isNull(txtBox) then
   me.filterOn = false

else
   me.filter = "Account='" & txtBox & "'"
   me.filterOn = true
end if
end sub

 

[toleafs]

thanks for the reply. I just realized I forgot to add there are 2 subforms in the form...my aplogies