Find Record Button 1

[edbloom]

I need help creating the VBA code for the following pseudo code.

procedure btnFind_Click()

Ask for Last_Name & First_Name
Search table (recordset)
If Record Count > 1
list all records in popup box
allow double-click selection of right person
populate form
ElseIf Record Count = 1
populate form
Else
display error MsgBox
End-if

End procedure

 

[BoxHead]

It seems that since the search criteria of Last_Name And First_Name could return multiple records, you migt be better served using the Combobox Wizard: selecting the "Find a record on my form" option and including Last_Name, First_Name, and whatever other criteria fields would be relevant. Sort by last_name.

A user would select the combobox and begin typing the last name to jump to records with that name.

It's just a thought. It would eliminate mis-spelled names, null values as criteria, etc.


HTH

John

When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[edbloom]

OK.

then show me how to do this in VBA, please.

 

[ssatech]

How bout something like this:
Using a hypothetical database with a table named "Contacts".
Fields:
ContactID
FirstName
LastName
Etc…

"Ask for Last_Name & First_Name"
1) Create a query name: qryFind
SELECT Contacts.ContactID, contacts.FirstName, contacts.LastName, [LastName] & " " & [FirstName] AS Name
FROM contacts
WHERE ((([LastName] & " " & [FirstName]) Like "*" & [Forms]![frmFind]![Name] & "*"));

"Search table (recordset)
If Record Count > 1
list all records in popup box"

2)Create a form called: frmContactList
Record Source: qryFind

In Design View of this form, say the textbox ContactID. Code the On-Click event something like:

Private Sub ContactID_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContacts"

stLinkCriteria = "[ContactID]=" & Me![ContactID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

3) Create a Form called: frmContacts
Record Source: Contacts ‘this is the table called Contacts”

4) Create another form, with an unbound field named "Name" and call it “frmFind”

Add a command button and call it cmdOK and code the OnClick event like:

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

DoCmd.OpenForm "frmContactList", acFormDS


Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox Err.Description
Resume Exit_cmdOK_Click

End Sub


Type some last name and first name in the field and click OK.

***This example is just an idea - work with it***