carry value from SQL to Query

[David]

Group,
I have a form with multiple drop down boxes and check
boxes. The form writes the selected values to multiple
tables. The JOIN is a field called drn which is in every
table. On the form I have a button to launch a query. I
would like the query to retrieve the drn value from the
active form or [Data Request].drn Here is my query, but
it is prompting the user to enter the DNR.

SELECT [ESN Request].*, [ESN Request].[Data Request Number]
FROM [Data Request] RIGHT JOIN [ESN Request] ON [Data
Request].[Data Request Number]=[ESN Request].[Data Request
Number]
WHERE ((([ESN Request].[Data Request Number])=[Data
Request].[Data Request Number]));

HOWEVER, this returns multiple records, how can I limit
the selection to the Data Request Number for the active
FORM? I have tried to create a private variable in the
form's code, but can not get the value to pass to the
query.

Any help you can provide would be greatly appreciated.

 

[Guest]

Correction the common field to link all of the tables
together is called [Data Request Number]. The query runs,
but retrieves all records. I have tried to use a variable
in the "ON CLICK" routine, but can not get that value to
the query.

Sorry for the confusing email, my relationships work
well. And if I ask the user to enter the [Data Request
Number] the query works fine.

 

[Ken Snell]

You can use a reference to a control on the form:

WHERE [ESN Request].[Data Request
Number])=[Forms]![FormName]![ComboBoxName];

where FormName is the name of the form that contains the value in the combo
box, and ComboBoxName is the name of the combo box on that form.

--
Ken Snell
<MS ACCESS MVP>

Correction the common field to link all of the tables
together is called [Data Request Number]. The query runs,
but retrieves all records. I have tried to use a variable
in the "ON CLICK" routine, but can not get that value to
the query.

Sorry for the confusing email, my relationships work
well. And if I ask the user to enter the [Data Request
Number] the query works fine.

-----Original Message-----
Group,
I have a form with multiple drop down boxes and check
boxes. The form writes the selected values to multiple
tables. The JOIN is a field called drn which is in every
table. On the form I have a button to launch a query. I
would like the query to retrieve the drn value from the
active form or [Data Request].drn Here is my query, but
it is prompting the user to enter the DNR.

SELECT [ESN Request].*, [ESN Request].[Data Request Number]
FROM [Data Request] RIGHT JOIN [ESN Request] ON [Data
Request].[Data Request Number]=[ESN Request].[Data Request
Number]
WHERE ((([ESN Request].[Data Request Number])=[Data
Request].[Data Request Number]));

HOWEVER, this returns multiple records, how can I limit
the selection to the Data Request Number for the active
FORM? I have tried to create a private variable in the
form's code, but can not get the value to pass to the
query.

Any help you can provide would be greatly appreciated.
.