Full Version: Form prompt user for value then display selected records
UtterAccess Forums > Microsoft® Access > Access Forms
wolfman99
I'm stuck trying to figure out how to 'filter' a form for records based upon a LastName provided by the user at runtime.
The problem is that the records to display only have a primary key to the Lastname field in a related table.
Otried to use a parameter query that works fine by prompting the user for the lastname from tblNames and then displaying all the records from tblMain. But when I included the query in the Docmd.Openform WHERE condition, the user is prompted twice for the LastName and regardless of the input nothing is displayed. I'm sure the WHERE is failing because of the parameter to a related table in the underlying query.
Any tips on how to create the SQL to use in the OpenForm WHERE? Perhaps it needs to include the JOIN statement? Not sure if the OpenForm WHERE is meant to handle complicated SQL statements or not.
Jack Cowley
Create a form with a combo box and a command button. In the combo box display the LastName from the related table with the PK as the first column and hidden. Use code like this in the command button:
oCmd.OpenForm "NameOfYourFormToOpen", , , "[NameOfIDField] = " & Me.NameOfComboBox
Oassume that each last name has a unique PK...
hth,
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.