Using a form to select records

[Sandra Grawunder]

I would like to use a popup form with a main form to
select records based on the value typed in a textbox i.e.
if I type in Smith I get a list of all the records named
Smith. Then, when I select the correct record from this
popup form, have the fields on a subform within the main
form filled in with the contact information.
Main form = frmPledge
Popup form = frmMLSelect (based on qryMasterList)
Subform = frmMasterList (based on qryMasterList)
PK of PPID links frmMasterList to frmPledge
NameID would be the criteria to select the records based
upon the value typed into the textbox.
How do I set this up?

Thanks so much,

Sandra G

 

[Bruce]

Since nobody has jumped in, I'll take a crack at this.
May I suggest using a combo box instead of a text box? If
you set the Autoexpand property to Yes it will
automatically try to fill in the rest of the name when you
start typing, starting with the first possibility in
alphabetical order. It will also allow the user to search
more easily for variant spellings or data entry errors.
Call the combo box cboCustName, and use the following code
as the combo box's Row Source (assuming that CustomerName
is the name of the field in the underlying table):
SELECT DISTINCT tblMasterList.CustomerName
FROM tblNames
ORDER BY tblNames.CustomerName;

This code will limit the list to a single instance of each
name. In the combo box's After Update event, put
something like (underscore means no line break):

'Find the records that match the selection
DoCmd.ApplyFilter , "[CustomerName]=""" & cboCustName_
& """"

The profusion of quotes is needed because CustomerName is
(I assume) a text field. I think there are other ways of
writing the code, but this should work. If the rest of
the

I have placed code similar to the above into a form's
header, which I can make visible by clicking a command
button on the main form, or hide by clicking a command
button in the header. You could do the showing and hiding
in any number of other ways, such as making the combo box
visible or not.
If you prefer to use a pop-up form I think you will need
to set up a parameter query as the Record Source for
frmMasterList. The criteria would then refer to the pop-
up form, something like: [Forms]![frmMLselect]!
[cboCustName]. First, set up the parameter query. When
that works as expected, create the form. Put a command
button on the pop-up form to hide it, not to close it.
I have to admit I have not done this sort of filtering in
a subform, so I am not sure if everything works exactly
the same way as filtering a main form. Hope this is of
some help.