• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Customize Search Box (1 Viewer)

smtazulislam

Member
Local time
Today, 20:24
Joined
Mar 27, 2020
Messages
213
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, 10:24
Joined
Oct 29, 2018
Messages
12,532
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, 20:24
Joined
Mar 27, 2020
Messages
213
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, 10:24
Joined
Oct 29, 2018
Messages
12,532
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, 20:24
Joined
Mar 27, 2020
Messages
213
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, 10:24
Joined
Oct 29, 2018
Messages
12,532
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, 20:24
Joined
Mar 27, 2020
Messages
213
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, 10:24
Joined
Oct 29, 2018
Messages
12,532
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:24
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, 20:24
Joined
Mar 27, 2020
Messages
213
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:24
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, 20:24
Joined
Mar 27, 2020
Messages
213
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, 10:24
Joined
Oct 29, 2018
Messages
12,532
Can you post a sample db with test data?
 

smtazulislam

Member
Local time
Today, 20:24
Joined
Mar 27, 2020
Messages
213
Okay, Please check... My all of entry code is deleted.
 

Attachments

  • HRDB.accdb
    832 KB · Views: 14

smtazulislam

Member
Local time
Today, 20:24
Joined
Mar 27, 2020
Messages
213
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, 10:24
Joined
Oct 29, 2018
Messages
12,532
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

Top Bottom