Solved Customize Search Box

[smtazulislam]

This is SearchBox

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.

 

[theDBguy]

Hi. Try adding the criteria to your FindFirst.

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

 

[smtazulislam]

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

 

[smtazulislam]

Thanks for reply. But it now work
Error : RunTime error 438

I apologize, I have wrote you missing a word "now = not".
Still it is not work.. Hope you good advice again...

 

[theDBguy]

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]

Yes, I correction and put FindFirst.
second time try it also :

 .FindFirst " & Me.cboStatus.Value = 1 Or 2 & " And EmployeeID = " & Me.txtSearchBox & """

Still same

 

[theDBguy]

Yes, I correction and put FindFirst.
second time try it also :

 .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]

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]

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?"

 

[smtazulislam]

Hi, anyone can help me this issue. I appreciated.

 

[GK in the UK]

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]

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

 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.

 

[GK in the UK]

Of course my bad you need to put the table field name !

 

[GK in the UK]

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]

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]

Can you post a sample db with test data?

 

[smtazulislam]

Okay, Please check... My all of entry code is deleted.

 

[theDBguy]

Okay, Please check... My all of entry code is deleted.

Hi. Thanks. Here's what I did with your code.

            .FindFirst "StatusID In(1,2) AND EmployeeID = " & Nz(Me.txtSearchBox, 0)

 

[smtazulislam]

Hi. Thanks. Here's what I did with your 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]

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:

            If .NoMatch Then
                MsgBox "No match!"
            Else
                Me.Bookmark = .Bookmark
            End If