Create recordset from combo choice

[sparky5]

I have a table called Contacts and a form with two combo boxes for searching for records either by name or property name. The following code works fine for finding the first record, but I want to be able to show on the form all (and only) those records which match the combo box entry. Currently rowsource for name box is:
***************************************************
SELECT [Last Name1] FROM Contacts UNION SELECT [Last Name2] FROM Contacts
ORDER BY Contacts.[Last Name1];
***************************************************
and code is:
***************************************************
Private Sub Combo214_AfterUpdate()
' Find the record that matches the control for Last Name search
Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name1]= '" & Me.[Combo214] & "' OR [Last Name2]= '" & Me.[Combo214] & "'"
rs.FindNext "[Last Name1]= '" & Me.[Combo214] & "' OR [Last Name2]= '" & Me.[Combo214] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Combo214.Value = ""
txtFirstName1.SetFocus
End Sub
***************************************************
Rowsource for property box is:
***************************************************
SELECT Contacts.PropertyID, Contacts.PropertyName
FROM Contacts
ORDER BY Contacts.PropertyName;
***************************************************
and code is:
***************************************************
Private Sub Combo212_AfterUpdate()
' Find the record that matches the control for Property Name search
Dim rs As dao.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[PropertyID] = " & Str(Nz(Me![Combo212], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Combo212.Value = ""
cboPropertyName.SetFocus
End Sub
****************************************************

Any help is appreciated!

 

[RichO]

Try filtering the form...

Private Sub Combo214_AfterUpdate()
Me.Filter = "[Last Name1]= '" & Me.[Combo214] & "' OR [Last Name2]= '" & Me.[Combo214] & "'"
Me.FilterOn = True
End Sub

Then adapt this code for the property combo box as well.

 

[sparky5]

Thank you again Rich for coming to my rescue. The part you sent worked out well, but when I tried to do it for the "Property" combo box it failed big time. First I used the following:
************************
Private Sub Combo212_AfterUpdate()
Me.Filter = "[PropertyID] = " & Me.[Combo212]
Me.FilterOn = True
End Sub
************************
This unfortunately only produced one record which happened to be the very last possible match. I then tried the following:
************************
Private Sub Combo212_AfterUpdate()
Me.Filter = "[PropertyName] = " & Me.[Combo212]
Me.FilterOn = True
End Sub
************************
And I got an error message: "Error '2001': You canceled the previous operation"

Now I have developed a lump on the right side of head from banging off this wall trying to figure out what to try next. I have various combinations of the string segment using the single quotes similar to the "Name" combo box, but nothing seems to work. If could help me work this one out I would appreciate it... (again).

 

[RichO]

Is PropertyID a numeric field or text? If it's a text data type you would need to put pseudo 'quotes' around it like the names are done. That's most likely the problem with the example where you used PropertyName instead of ID.

Otherwise it looks like you did it right. All I can suggest is attaching a scaled down DB with your form and table(s) because I can't see a problem with your example.

 

[sparky5]

Thanks again Rich. How can I attach my db on the forum here? I would be appreciate it if you could take a look at it. Have a great weekend.

 

[RichO]

Create a blank database. Then import all objects from your original database that are necessary to make the form operate correctly. Strip most of the records out of your table, compact & repair database, send it to a zip folder and attach it.

I have Access 2000 so I can work with 2000 and A97. If you have a later version you will also need to convert to a previous version of Access.

 

[sparky5]

Rich, I apologize for being such a complete idiot on this, but how do I go about doing what you explained? Can I not just attach my db as it is now? I think everything in there is needed to use the whole db. I appreciate your help on this.

 

[RichO]

Because file size is an issue with attachments on a forum it might be easier if you just e-mail it to me. It sounds like a simple problem with a quick fix. I'll PM you about it.