carry value from SQL to Query

  • Thread starter Thread starter David
  • Start date Start date
D

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.
 
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.
 
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.
.
Click to expand...
Click to expand...
 
Back
Top