Create searchbox in Access

Question

I use an Access database to manage employee information. I want to create a search box, so that when I enter the employee id the rest of the information could be filled automatically.

I created a function to try to get each column's value and assign it when I click the button, but it's not working.

Here is the code:

Public Function GetEmployeeName() As ADODB.Recordset
    Dim rst As ADODB.Recordset
    Set GetEmployeeName = CurrentProject.Connection.Execute("select EmployeeName From EInfor where EmployeeID = " & txtEID.Value)
End Function

Private Sub btnSearch_Click()
    txtEmployeeName = GetEmployeeName()
End Sub

score 1 · Accepted Answer · answered May 22 '16 at 16:16

Seems to me that GetEmployeeName() should return the EmployeeName which matches a given EmployeeID. In that case, you don't need a Recordset. A DLookup expression can give you what you need.

Public Function GetEmployeeName(ByVal pEID As Long) As Variant
    GetEmployeeName = DLookup("EmployeeName", "EInfor", "EmployeeID=" & pEID)
End Function

That function expects you to supply the target EmployeeID as a parameter, so it is not hard-wired to the value of a control on one particular form.

If no matching EmployeeName is found, the function returns Null. In your command button's click event, you can use Nz() to substitute something else for Null.

Private Sub btnSearch_Click()
    Me!txtEmployeeName.Value = Nz(GetEmployeeName(Me!txtEID.Value), "unknown")
End Sub

thank you for your very useful advices, it works well – Royy May 23 '16 at 17:35 — Royy, May 23 '16 at 17:35

Leviathan · Answer 2 · 2016-05-22T16:22:49.647

You try to set the default property of txtEmployeeName (which is the Value property) to GetEmployeeName() which returns an ADODB.Recordset which has the default property Fields, which is a collection.

This can't possibly work.

Try this instead:

txtEmployeeName = GetEmployeeName().Fields(0)

If you want to update multiple textboxes you should consider only using one SELECT statement to retrieve all needed values at once:

"select * From EInfor where EmployeeID = " & CLng(txtEID.Value)

And then you can update the textboxes like this:

txtEmployeeName    = rst.Fields("EmployeeName")
txtEmployeeBoss    = rst.Fields("EmployeeBoss")
txtEmployeeAddress = rst.Fields("EmployeeAddress")
txtEmployeeSalary  = rst.Fields("EmployeeSalary")

Note the following changes:

Using CLng() I made sure that only a number can be passed in the SQL code (this is a security vulnerability, see How does the SQL injection from the "Bobby Tables" XKCD comic work? for an example).
I merged the two procedures you have: Put everything in btnSearch_Click.

score 0 · Answer 3 · answered May 23 '16 at 02:48

You can create a ComboBox that filters the record set as you type. This is very useful when you are searching for records that are similar to what you are looking for but may not be an exact match!

Hit Alt+F11 and right click the form you created before. Paste in the script below.

[![Option Compare Database

Private Sub ComboSelect_Change()

    '  You need to use String delimiters if you want to use a Text Field like:
    '  Me.Filter "\[ATextFieldInRecordSource\] = """ & Me.FilterComboBox & """"

    '  For a Numeric Field, use something like this:
    '  Me.Filter "\[ANumericFieldInRecordSource\] = " & Me.FilterComboBox
    '  Me.FilterOn = True

    Me.\[Customer_Query subform1\].Form.Filter = "\[Company_Name\] Like '*" &
                     Replace(Me.ComboSelect.Text, "'", "''") & "*'"
    Me.\[Customer_Query subform1\].Form.FilterOn = True

End Sub][1]][1]



Notice a few things:
•   The subform is named Customer_Query subform1’
•   The combobox is named ComboSelect’
•   Finally, the ‘like clause’ is used in combination with the wildcard character.
•   Like '*" & Replace(Me.ComboSelect.Text, "'", "''") & "*'"

When you type text into the combobox, the results in the subform are dynamically re-queried.

Create searchbox in Access

3 Answers3