Msg box

[sk88]

so I have this in my search form which works perfectly fine:

Private Sub cmdSearch_Click()


    Dim LSQL As String
    Dim LSearchString As String
    
    If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        LSearchString = txtSearchString
        
        'Filter results based on search string
        LSQL = "select * from tblAudit"
        LSQL = LSQL & " where MRN_Number LIKE '*" & LSearchString & "*'"
        
        Form_FrmAuditTool.RecordSource = LSQL
        
       
        
        'Clear search string
        txtSearchString = ""
        
        MsgBox "Results have been filtered.  Patient MRN_Number containing " & LSearchString & "."
        
    End If
      
      

    
End Sub

Now, I am having trouble in adding a line as to if the searched is not found or unavailable, a message box should say something like "your searched field cannot be found"

Where do I add this in my above code and how?

could someone please help? thank you!

[Robeen]

You can try using a 'Select Count(*)' query using your criteria and then - if your Count is 0 - you can put that message box up saying it could not be found.

[sk88]

You can try using a 'Select Count(*)' query using your criteria and then - if your Count is 0 - you can put that message box up saying it could not be found.

Thanks Robeen.
But that is not what I want actually.
I don't want to create a query.

What I have created was a search function.. So like I have a empty field on top of the form where if I type "ROBEEN" and clicked SEARCH. I would get the results in the subform of ROBEEN information.
but what if ROBEEN is not available? I need a msg box which will say something like "the person ROBEEN is not available or could not be found"

[imintrouble]

this is just my generic idea, but might an else if statement work? like,

else if LSQL = null
MsgBox "Sorry, " & LSearchString & " couldn't be found."

end if

its been a while but i think this MIGHT work, even if it isn't very elegant

[Robeen]

How about using the DLookup function - something like this [not functional code - you'll have to pass the value of your search textbox to the function]:

If IsNull(DLookUp("[AnyField]", "tblAudit", "[ID] = SearchField")) Then
MsgBox "Search item not found."
End If

[sk88]

this is just my generic idea, but might an else if statement work? like,

else if LSQL = null
MsgBox "Sorry, " & LSearchString & " couldn't be found."

end if

its been a while but i think this MIGHT work, even if it isn't very elegant

Thanks imintrouble! But it didn't really work

[sk88]

How about using the DLookup function - something like this [not functional code - you'll have to pass the value of your search textbox to the function]:

If IsNull(DLookUp("[AnyField]", "tblAudit", "[ID] = SearchField")) Then
MsgBox "Search item not found."
End If

Thanks Robeen!
I think I am very close in getting there. There is a slight issue though.
I added your codes to it and now two MSGBOX pops up even when the record has been found.

I know somewhere it is not right and I am not very good with Access.
This is how my codes look like now:

Private Sub cmdSearch_Click()


    Dim LSQL As String
    Dim LSearchString As String
    
    If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        LSearchString = txtSearchString
        
        'Filter results based on search string
        LSQL = "select * from tblAudit"
        LSQL = LSQL & " where MRN LIKE '*" & LSearchString & "*'"
        
        Form_FrmAuditTool_sub.RecordSource = LSQL
        
       
        
        'Clear search string
        txtSearchString = ""
        
        MsgBox "Results have been filtered.  Patient MRN containing " & LSearchString & "."
        

        
        
    End If
    
      
      If IsNull(DLookup("[MRN]", "tblAudit", "[MRN] = txtSearchString")) Then
MsgBox "Search item not found."
End If

    
End Sub

[Robeen]

LSQL is not what the sql statement returns - it IS the SQL statement that you are building.

[Robeen]

Your second MsgBox is outside your first If statement.

So - you get one messagebox inside your first If statement and then another one outside it.

I think there is a problem with that DLookup statement of yours.
It needs to be something like this:

 
If IsNull(DLookup(("[MRN]", "tblAudit", "[MRN] = '" & LSearchString & "'")) Then
   msgbox "Not In Table"
Else
   msgbox "In Table"
End If

P.S. Make sure that LSearchString - or txtSearchString has the value from your text box when the second If Statement is executed.
I think it should be inside your first If statement.

[sk88]

Your second MsgBox is outside your first If statement.

So - you get one messagebox inside your first If statement and then another one outside it.

I think there is a problem with that DLookup statement of yours.
It needs to be something like this:

 
If IsNull(DLookup(("[MRN]", "tblAudit", "[MRN] = '" & LSearchString & "'")) Then
   msgbox "Not In Table"
Else
   msgbox "In Table"
End If

It gives me the msgbox "Not in table" for even when the it is in table

[Robeen]

I think that may be because at the time when you are running my DLookup - there may not be a value in LSearchString.

Verify that LSearchString DOES have a value.

[sk88]

I think that may be because at the time when you are running my DLookup - there may not be a value in LSearchString.

Verify that LSearchString DOES have a value.

okay. its working now!!!
Thank you so much ROBEEN!!

[Robeen]

You're welcome! Happy to Help!