Need Code to do Record Search on Form

[airbus2]

Hi guys,

I know this is an Access 101 question but her it goes.

I have a form called Employees, with a control source of tblEmployees. The form also has numerous subforms attached linked via parent/child and is working fine. All these forms are based on "EmployeeNumber" I want to have a textbox (unbound) on the Employees form that will do a search/find or filter once you hit "ENTER" that is based on EmployeeNumber and pull that record forward. Dont want to use a combo box, we have over 5000 emoloyees.

 

[Remou]

You can use the same code that is generated for a combo box, that is on the lines of:

'Needs reference to Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset

Set rs=Me.RecordsetClone
rs.FindFirst "EmployeeNumber=" & Me.txtEmployeeNumber
If Not rs.NoMatch Then
'Record found
  Me.Bookmark=rs.Bookmark
Else
  'Some message here
End If

 

[TheAceMan1]

How are ya airbus2 . . .

In the [blue]AfterUpdate[/blue] event of the search textbox (which should be on the mainform), copy/paste the following:

[blue]   Dim Cri As String, tbxName As String
   
   tbxName = "[purple][b][i]SearchTextboxName[/i][/b][/purple]"
   
   If Trim(Me(tbxName) & "") <> "" Then
      Cri = "[EmployeeNumber] = " & Me(tbxName)
      Me.Recordset.FindFirst Cri
   End If
[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

airbus2 . . .

Hit submitt too soon.

Be sure to go over one of the [blue]links[/blue] at the bottom of my post. The links reference etiquette here in the forums.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[airbus2]

Thanks guys, I really appreciate your help.

I am getting an error message. Not sure what Form Event property to use for unbound box. (Before Update, On Open, On Load, etc....)

Error message: The expression On Load you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

Private Sub Form_AfterUpdate()
Dim Cri As String, Employees As String

tbxName = "SearcheEmployeeNumber"

If Trim(Me(Employees) & "") <> "" Then
Cri = "[EmployeeNumber] = " & Me(Employees)
Me.Recordset.FindFirst Cri
End If

End Sub

Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![EmployeeID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub



and unbound text box code

Private Sub Text86_BeforeUpdate()
'Needs reference to Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "EmployeeNumber=" & Me.txtEmployeeNumber
If Not rs.NoMatch Then
'F9 Employee Found
Me.Bookmark = rs.Bookmark
Else
'F9 Employee Not Found
End If
End Sub


What do I have wrong in here?

 

[TheAceMan1]

airbus2 . . .

For starters:

[blue]In the [purple]AfterUpdate event[/purple] of the search textbox (which should be on the mainform) . . .[/blue]

Your using the BeforeUpdate! Either Remou's code or mine should reside in the textbox [blue]AfterUpdate[/blue] event.

Get rid of the forms [blue]AfterUpdate[/blue] event & code.
BTW: although your getting rid of it, see the error in [purple]purple[/purple] below:

[blue]Private Sub Form_AfterUpdate()
   Dim Cri As String, [purple][b]Employees[/b][/purple] As String
   
   [purple][b]tbxName[/b][/purple] = "SearcheEmployeeNumber"
   
   If Trim(Me(Employees) & "") <> "" Then
      Cri = "[EmployeeNumber] = " & Me(Employees)
      Me.Recordset.FindFirst Cri
   End If

End Sub[/blue]

Also, using Converting DoMenuItem to RunCommand, I can't place:

[blue]DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
[/blue]

I believe your going to a new record. Let me us know.

Make the changes and try again . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[airbus2]

Hey AceMan1 & Remou

I continue to get the same error message. Not sure why. Also, when I open the db and then select the Employee Test Form, it automatically populates with the first record.

Specifics of DB:

Form Name: Employees Test Form
Pulling Info from: EmployeeNumber
Unbound textbox name: EmployeeFind
Table Name: Employees

The table primary key is under EmployeeID if that makes a difference. Are there any relationship builds I need to do?

I have a 6 button control bar within the header of each form in the db. One button is designed to go to the Employee Test Form. Could we build a code that when the button is selected that a popup requests the employee number then pulls up the employee info on the Employee Test Form?

Thanks in advance.

 

[TheAceMan1]

airbus2 . . .

Did you remove the code in the [blue]Before Update[/blue] event?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[airbus2]

Yes Sir.

The Employee Test Form, was a new form I created to use while this gets figured out. That is the only code associated with this form.

 

[Remou]

If you still have this:

[tt]Private Sub Text86_BeforeUpdate()[/tt]

In your code, change it to:

[tt]Private Sub Text86_BeforeUpdate(Cancel As Integer)[/tt]

The error you show is usually caused by missing the parameter.

 

[airbus2]

The only code is under After Update. There is currently no code for Before Update.

 

[Remou]

Open a new database and reproduce the form to see if you still get the same error. It is possible that your current database is corrupted.

 

[airbus2]

I created a new db with one tbl & one form.

Here are the parameters
Table Name: Employees
Form Name: EmployeesInfo
Field Name: EmployeeNumber
Unbound Field Name: EmployeeFind (This is where the employee number is entered.)

The code was placed in After_Update on the EmployeeFind field. When you enter an employee number, it just moves to the next record and not to the record reaquested.

Here is the code pasted from db:

Private Sub EmployeeFind_AfterUpdate()
'Needs reference to Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "EmployeeNumber=" & Me.EmployeeNumber
If Not rs.NoMatch Then
'Record found
Me.Bookmark = rs.Bookmark
Else
'Some message here
End If

End Sub

 

[Remou]

Change this line:
[tt]rs.FindFirst "EmployeeNumber=" & Me.EmployeeNumber[/tt]

To refer to the control that you use to enter employee number, from the above that would be EmployeeFind, so:
[tt]rs.FindFirst "EmployeeNumber=" & Me.EmployeeNumber[/tt]

You should check that something has been entered before you search.

 

[PHV]

I think Remou meant this:
rs.FindFirst "EmployeeNumber=" & Me!EmployeeFind

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[airbus2]

Hey guys,

in adding
rs.FindFirst "EmployeeNumber=" & Me!EmployeeFind
I am getting error message 3464.

 

[Remou]

Thanks, PHV

 

[PHV]

If EmployeeNumber is not defined as numeric in Employees:

rs.FindFirst "EmployeeNumber='" & Me!EmployeeFind & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[airbus2]

THANKS GUYS!!! IT WORKS GREAT!!!!

One questions though. Actually 2 questions.

First, Building on what we have, how do I make the entire EmployeeInfo form blank when a user initally selects it? Currently it goes to the first record in the database.

Also, when a user enters the employee number into our EmployeeFind box, the number stays there, what do I need to add to the code so that the number in the box is no longer present.

 

[Remou]

There are a number of things you can do, but all of them are a little awkward.

1. You can hide the controls;
2. You can use an empty recordset, however, you must set allow additions to true or no controls will show;
3. You can ask the user for an employee number before you open the form;
4. You can use a dummy recordset;
5. You can unbind the controls.

The EmployeeFind box can be blanked by setting it to null after you have found the relevant record.