Access 2010 ComboBox query parameter

Question

I have a form, based on a query.  I've added a combo box using the third option from the wizard where it states:

"Find a record on my form based on the value I selected in my combo box."

This works, sort of.  When I open the form, the query asks for the parameter and the form loads with the dataset.  When I click the combo box, it's as if the query is running again, asking for the parameter.  

Is there a way to have the combo box receive it's parameter when I'm opening the form?  As it stands now, I have to type the parameter in twice, once when the form opens and again when I click the combo box.

Answer 1

If you are restricting the form's recordset on the basis of a parameter on one column, and using the combo box to navigate to a record on the basis of another column, then you should not need to reference the parameter in the combo box's RowSource.

For example you might restrict an employees form on the basis of a parameter on a Department column, and then use a combo box to navigate to an employee.  In this hypothetical scenario the form's RecordSource property might be the following query:

SELECT *

FROM Employees

WHERE Department = [Enter Department:];

The RowSource property of the unbound navigational combo box would then be like this:

SELECT EmployeeID, FirstName & " " & LastName

FROM Employees

WHERE Department = Form!Department

ORDER BY LastName, FirstName;

i.e. the parameter in the RowSource property to restrict the list of employees to those in the selected department is a reference to the form's  Department control, not to the original parameter.

If the original parameter is a reference to a control in another unbound dialogue form, which is a better approach, (or even another bound form) from which the employees form is opened, the parameters in both queries can be the same provided that the calling form remains open, e.g.

WHERE Department = Forms!frmEmployeeDlg!cboDepartment;

The above is a hypothetical example of course and probably bears little relation to your actual set-up, but if you are attempting something along these lines then I don't think the control wizard would be able to handle it.  You'd need to hand-craft it.

If the above does not point you in the right direction, post back with a clear description of what you are attempting to do in real world terms, rather than how you are attempting to do it in database terms.

Answer 2

What is the SQL for query?