Solved Customize Search Box (2 Viewers)

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
198
This is SearchBox
Code:
Private Sub txtSearchBox_AfterUpdate()
If Me.txtSearchBox = 0 Then
        DoCmd.GoToRecord acForm, Me.Name, acNewRec
      
    Else
        With Me.RecordsetClone
            .FindFirst "EmployeeID = " & Nz(Me.txtSearchBox, 0)
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
    End If
End Sub
it's work properly. but I have a Combobox as cboStatus and have 7 row data.
1. Active
2. Vacation
3. , 4, 5, 6, 7 etc.

I want to do...
* txtSearchBox search Active and Vacation Only and others data 3,4,5,6,7 is ignored.
* If dont matched data give message "Enter Employee ID Data not Match", If matched just display data record.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:29
Joined
Oct 29, 2018
Messages
12,079
Hi. Try adding the criteria to your FindFirst.

.FindFirst "(Status=1 Or Status=2) And EmployeeID=" & Nz(Me.txtSearchBox,0)
 
Last edited:

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
198
Hi. Try adding the criteria to your FindFirst.

.FundFirst "(Status=1 Or Status=2) And EmployeeID=" & Nz(Me.txtSearchBox,0)
Thanks for reply. But it now work
Error : RunTime error 438
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:29
Joined
Oct 29, 2018
Messages
12,079
I apologize, I have wrote you missing a word "now = not".
Still it is not work.. Hope you good advice again...
Hi. What is error 348? Did you also catch the typo I made? FundFirst instead of FindFirst?
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
198
Yes, I correction and put FindFirst.
second time try it also :
Code:
 .FindFirst " & Me.cboStatus.Value = 1 Or 2 & " And EmployeeID = " & Me.txtSearchBox & """
Still same
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:29
Joined
Oct 29, 2018
Messages
12,079
Yes, I correction and put FindFirst.
second time try it also :
Code:
 .FindFirst " & Me.cboStatus.Value = 1 Or 2 & " And EmployeeID = " & Me.txtSearchBox & """
Still same
Hmm, that's not what I wrote though. Also, what was the error message?
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
198
No, First i try.
.FindFirst "(Status=1 Or Status=2) And EmployeeID=" & Nz(Me.txtSearchBox,0)
and second time try
.FindFirst " & Me.cboStatus.Value = 1 Or 2 & " And EmployeeID = " & Me.txtSearchBox & """
but both not work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:29
Joined
Oct 29, 2018
Messages
12,079
No, First i try.
.FindFirst "(Status=1 Or Status=2) And EmployeeID=" & Nz(Me.txtSearchBox,0)
and second time try
.FindFirst " & Me.cboStatus.Value = 1 Or 2 & " And EmployeeID = " & Me.txtSearchBox & """
but both not work.
The second will definitely not work. Is the name of the field "Status?"
 

GK in the UK

Registered User.
Local time
Today, 17:29
Joined
Dec 20, 2017
Messages
217
I think DBguy mean to replace 'Status' with the name of your control.

If you're only ever interested in status 1 or 2 you could try:

if EmployeeID is text:

.FindFirst "( Me.cboStatus = 1 OR Me.cboStatus = 2 ) AND EmployeeID = '" & me.txtSearchBox & "'"

if EmployeeID is numeric:

.FindFirst "( Me.cboStatus = 1 OR Me.cboStatus = 2 ) AND EmployeeID = " & nz(me.txtSearchBox,0)


Which is more or less what he said in post #2
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
198
I think DBguy mean to replace 'Status' with the name of your control.

If you're only ever interested in status 1 or 2 you could try:

if EmployeeID is text:

.FindFirst "( Me.cboStatus = 1 OR Me.cboStatus = 2 ) AND EmployeeID = '" & me.txtSearchBox & "'"

if EmployeeID is numeric:

.FindFirst "( Me.cboStatus = 1 OR Me.cboStatus = 2 ) AND EmployeeID = " & nz(me.txtSearchBox,0)


Which is more or less what he said in post #2
Thanks for reply.
EmployeeID is Numeric (AutoNumbers)
This is code I put in After_Update Event

Code:
 With Me.RecordsetClone
            '.FindFirst "EmployeeID = " & Nz(Me.txtSearchBox, 0)
            .FindFirst "(Me.cboStatus = 1 OR cboStatus = 2 ) AND EmployeeID = " & Nz(Me.txtSearchBox, 0)
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
Here is Run-Time error 3070. Message descriptions is "Database Engine does not recognize Me.cboStatus as a valid field name "
But here I check name is same, see the attached screenshot.
Capture.JPG
 

GK in the UK

Registered User.
Local time
Today, 17:29
Joined
Dec 20, 2017
Messages
217
Is this what you're after ?

.FindFirst " [FieldName] = " & Me.cboStatus & " AND EmployeeID = " & nz(me.txtSearchBox,0)


Where FieldName if the table column name in your Employee table.
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
198
Where FieldName if the table column name in your Employee table.
.FindFirst "StatusID = " & Me.cboStatus & " AND EmployeeID = " & Nz(Me.txtSearchBox, 0)
StatusID is table field name
cboStatus is form field name.
Still not work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:29
Joined
Oct 29, 2018
Messages
12,079
Can you post a sample db with test data?
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
198
Hi. Thanks. Here's what I did with your code.
Code:
            .FindFirst "StatusID In(1,2) AND EmployeeID = " & Nz(Me.txtSearchBox, 0)
Oh ! Before I try " Instr(1;2) "
Thank you much, Its work. I appreciated.

Can you tell me where I put message box "If record is not matched"
Because, new user dont understand when DB not run/moving.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:29
Joined
Oct 29, 2018
Messages
12,079
Oh ! Before I try " Instr(1;2) "
Thank you much, Its work. I appreciated.

Can you tell me where I put message box "If record is not matched"
Because, new user dont understand when DB not run/moving.
Try it this way:
Code:
            If .NoMatch Then
                MsgBox "No match!"
            Else
                Me.Bookmark = .Bookmark
            End If
 

Users Who Are Viewing This Thread (Users: 0, Guests: 2)

Top Bottom