need help getting a subset of records into form

[daSpud]

I have a table with names and addresses. I want to be able to select out those with a given house number (say 1511). Some numbers have only one house (3868) and some have 2 (1511). If there is only 1 then I can go straight to my next form to edit that table entry. If there is more than one then I want to have a form that shows those names and addresses and allow the user to pick the proper one for the edit.

In addition to the house number search I also have a name search that I want to use that same secondary form when there are more than one name match (say "Jones" or "Smith").

The case for 1 works just fine. How do I do the other case? I don't want to make a query on this new form because it can be used for different search results.

frmMultipleLots contains a listbox that I am trying to get the results into so the user can select the correct record.

The way this is now I get all 179 records listed in the listbox. At one point the listbox contained all the records, but the record count at the bottom had the correct record count of 2. Not sure what I changed to get that, but never had only 2 records in the listbox.

Thank you very much for any help.





Dim RecordCount As Long
DocName = "frmHomeOwner"
RecordCount = DCount("Lot_No", "tblHomeOwner", "[First_Street_Number] LIKE '" & [TextIn] & "*' ")
If RecordCount = 1 Then
DoCmd.OpenForm DocName, , , "[First_Street_Number] LIKE '" & [TextIn] & "*' "
Else
MsgBox RecordCount & " houses with number " & TextIn
DoCmd.OpenForm "frmMultipleLots", , , "[First_Street_Number] LIKE '" & Forms![frmOwnerListByLot]![TextIn] & "*' "
End If

[OBP]

I know that this is predominantly a VBA Forum, but you can do what you want just by using a Parameter Query for the Record Source of the form.
The Parameter can be set from the form calling the data.
The other alternative is to use VBA to set the Form's Filter.

[daSpud]

I know that this is predominantly a VBA Forum, but you can do what you want just by using a Parameter Query for the Record Source of the form.
The Parameter can be set from the form calling the data.
The other alternative is to use VBA to set the Form's Filter.

I have programmed in other systems (Clarion and WinDev) but not in Access. I am taking this project one statement at a time and trying to learn the "lingo" of Access. I have seen the Record Source for the form but didn't know what to put in there.

What would the Parameter look like? What would the Form Filter look like?

Thank you for the reply.

[OBP]

Here is an example of a parameter query where the parameters are set by a form.
I always use queries to supply data to forms as they are more versatile.

[OBP]

Using a form filter is usually easier than parameter queries when you need to filter on a lot of fields.
So here is an original search by by Allen Browne in 2000.

[daSpud]

Using a form filter is usually easier than parameter queries when you need to filter on a lot of fields.
So here is an original search by by Allen Browne in 2000.

This looks like a perfect example for me. Thank you very much.