Display "No Record" message on failed find 1

[jpstroud]

Hey folks. I have some code that works fine, but I'd like to add more to it. Here is the code:

Dim optArray As Variant, searchText As String, frmName, optValue As Object
    
optArray = Array("Last_Name", "First_Name", "CertNo", "SSN")
Set frmName = Forms("frm_CertifiedApplicants")
Set optValue = frmName.Controls(optArray(Me.searchFrame))
    
If Me.txt_SearchText = Null Or Me.txt_SearchText = "" Then
    Exit Sub
Else
    searchText = Me.txt_SearchText.Value
    If searchText = prevSearchText And Me.searchFrame.Value = prevSearchValue Then
        frmName.SetFocus
        optValue.SetFocus
        DoCmd.FindNext
    Else
        frmName.SetFocus
        optValue.SetFocus
        DoCmd.FindRecord searchText, acAnywhere, False, acSearchAll, True, acCurrent, True
        prevSearchText = searchText
        prevSearchValue = Me.searchFrame.Value
    End If
End If
Me.SetFocus
Me.txt_SearchText.SetFocus

Now, as some of you may notice, this is a search function called by a button on a pop-up form, designed to find records on another form. The people I work with have trouble with the normal find dialog that pops up, so I've gone this route (don't ask me WHY they have troubles, I can't seem to figure that one out).

One thing that this code does not do, is display a message when the search either A) Fails outright, or B) cannot find any more records (ie, has already found 5 smiths, no more in db).

Anyone have improvements on the code as it is now, or ideas on how to add the msgbox that I mention?

Thanks!

 

[PHV]

Anyway, replace this:
If Me.txt_SearchText = Null Or Me.txt_SearchText = "" Then
By something like this:
If Trim(Me.txt_SearchText & "") = "" Then
as the condition 'Me.txt_SearchText = Null' is NEVER true.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jpstroud]

That helps tighten up the code some. I've modified the code as follows:

Dim optArray As Variant, searchText As String, frmName, optValue As Object, tmpRecord As Long
    
optArray = Array("Last_Name", "First_Name", "CertNo", "SSN")
Set frmName = Forms("frm_CertifiedApplicants")
Set optValue = frmName.Controls(optArray(Me.searchFrame))
tmpRecord = frmName.CurrentRecord
    
If Me.txt_SearchText = Null Or Me.txt_SearchText = "" Then
    MsgBox "Please enter a search criteria.", , "Find error"
    Exit Sub
Else
    searchText = Me.txt_SearchText.Value
    If searchText = prevSearchText And Me.searchFrame.Value = prevSearchValue Then
        frmName.SetFocus
        optValue.SetFocus
        DoCmd.FindNext
    Else
        frmName.SetFocus
        optValue.SetFocus
        DoCmd.FindRecord searchText, acAnywhere, False, acSearchAll, True, acCurrent, True
        prevSearchText = searchText
        prevSearchValue = Me.searchFrame.Value
    End If
    If tmpRecord = frmName.CurrentRecord Then
        MsgBox "No records", , "Find results"
    End If
End If
Me.SetFocus
Me.txt_SearchText.SetFocus

This new code allows me to get a message whenever there are no (new) results from the search. However, if someone has a better way of doing it, please let me know.

Since I have not defined a recordset, trying to do NoMatch doesn't work very well (at least I haven't been able to get it to work).

 

[jpstroud]

Star for PHV for submitting a useful response. Thanks! If anyone else has good coding tips for the above, please let me know.

 

[wreded]

Ok, i'm thicker than i thought. i can't seem to get this thing functioning. Is it fired through an "OK" button? And i cannot get the "Set optValue=frmName.Controls(optArray (Me.searchFrame)) to function. i've tried using the name of my search form and the form i'm searching from, both to no avail.
Thanks,
Dave Wrede

 

[jpstroud]

I can send you a mockup of the MDB file if you'd like.

The main form calls a popup search form. Typing into the popup form, and hitting search leaves the form up and searches the main form.

optValue is based on an array of Radio Buttons. The default is to search on the Last Name, but the user can select First Name, SSN, or Cert Number if they so choose. Each radio button value corresponds to the correct search field so that when it's selected it's value is dropped in, and the correct field is searched on.

Hope that helps.

 

[wreded]

Thanks! i'll have to give that a try. i come from a dBASE/DOS background so i'm more used to a "find" function that displays the record and goes away or complains if there is no "find" and then goes away. That, plus having users that are more used to shovels and screwdrivers than computers makes me try to make things as idiot proof as possible.
Thanks again.
DAve

 

[jpstroud]

That's why the huge chunks of code is most of my databases. I work with a bunch of librarians/consultants and very few of them are computer savvy.

 

[wreded]

i guess i'll have to see that MDB mockup after all. i just can't wrap my brain around it without seeing it.
Thanks,
Dave

 

[wreded]

jpstroud,
i've tried getting this thing to work, but to no avail. i've even simplified it to search on two fields; no luck. Could You please send me a copy of the mockup MDB?
Thanks,
Dave

 

[jpstroud]

Sure. I forgot to do this, as I managed to get it to work. (was wondering why I had a backup copy on my desktop with no data in it, I was preparing it for you)

Jeremy

 

[wreded]

Ok Jeremy,
Since they don't want us to post e-mails here, how do we go about this?
Dave

 

[jpstroud]

I'm trying to find some free webspace to post it to, that will let you access it. (the last part being the problem). Do you know of any?

 

[wreded]

Merry Christmas/Happy New Year/etc./etc.
Been a busy time over the holidays.
Unfortunately i don't know of any free webspace.
Let's see if You can e-mail it to:
wreded at rocketmail dot com
and it gets through.
Thanks,
Dave

 

[wreded]

--Got it!
Thanks alot!
Dave

 

[jpstroud]

Great. Let me know what you come up with on it.