'Find form' only returing records matching 1 text box 2

[ttellis]

I have a form called (Find Employee) set up that is only used to find records from a table (BadgeData) and returns the record on another form (IndivData). On the Find Employee form, there are three text boxes that are unbound but exist in the BadgeData table(Badge Number, FName, & LName). I created a command button (btnFindBadgeData) to use the data entered on the Find Employee form, search the BadgeData table and return the record on the IndivData form. The problem is...the command button is only reading data from the LName text box. If data is entered into any other text box (and not LName), then the IndivData form pops up empty.

I am a bit new at coding, so I can't figure out why this is happening. I searched other threads and found similar issues, but nothing that would truely help me with my problem. If anyone can help me figure out what's wrong here or point me to a thread that will help, I'd greatly appreciate it!

Here's the code:

 Private Sub btnFindBadgeData_Click()
On Error GoTo Err_btnFindBadgeData_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "IndivData"
    stLinkCriteria = "[CardNum]=" & "'" & Me![CardNum] & "'"
    stLinkCriteria = "[FName]=" & "'" & Me![FName] & "'"
    stLinkCriteria = "[LName]=" & "'" & Me![LName] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    

Exit_btnFindBadgeData_Click:
    Exit Sub

Err_btnFindBadgeData_Click:
    MsgBox Err.Description
    Resume Exit_btnFindBadgeData_Click
    
End Sub

Tiffany

 

[930driver]

You're setting the stLinkCriteria 3 times. The second and third overwrite the previous.

Something like this might be what you're looking for:

stLinkCriteria = "[CardNum]=" & "'" & Me![CardNum] & "'" _
& "OR [FName]=" & "'" & Me![FName] & "'" _
& "OR [LName]=" & "'" & Me![LName] & "'"


"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[PHV]

Try something like this :
stLinkCriteria = "[CardNum]='" & Me![CardNum] & "' OR " _
& "[FName]='" & Me![FName] & "' OR " _
& "[LName]='" & Me![LName] & "'"

You may also consider the Like comparaison operator.


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

 

[ttellis]

Thank you both, 930driver & PHV!! The simplest things can be the hardest, huh? I learned something new:

The second and third overwrite the previous.

Tiffany

 

[ttellis]

Okay...same form, new issue.

I changed the original code as suggested in the above posts. I created in If statment to either open the correct IndivData form or return a 'Not Found' message. It still worked fine until I added more code to set the unbound fields to null after the If statement ran. Now, no records are being returned!! The data I'm using to test with is data I've gotten from the record source table. I tried taking the last code that I added back out and it still doesn't return any records. What did I do???

 Private Sub btnFindBadgeData_Click()
On Error GoTo Err_btnFindBadgeData_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "IndivData"
    If (stLinkCriteria = "[CardNum]=" & "'" & Me![CardNum] & "'" _
        & "OR [FName]=" & "'" & Me![FName] & "'" _
        & "OR [LName]=" & "'" & Me![LName] & "'") Then
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
        Beep
        MsgBox "No employee data was found." _
            & vbCrLf & vbCrLf _
            & "Please try again.", vbInformation
        CardNum.SetFocus
    End If
        Me.[CardNum] = Null
        Me.[LName] = Null
        Me.[FName] = Null

Exit_btnFindBadgeData_Click:
    Exit Sub

Err_btnFindBadgeData_Click:
    MsgBox Err.Description
    Resume Exit_btnFindBadgeData_Click
    
End Sub

Tiffany

 

[930driver]

You're testing to see if a string variable is equal to a particular string. Since a string variable is initiallized to "" this will always return false and your Else statement will run.

Put your check for records in the On Open event of form IndivData. This is where you will have the recordset available. If there are no records close that form and return to the calling form.

If Me.Recordsetclone.Count > 0 Then
'go
Else
Docmd.close
'... and so on

"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"