Enter Parameter Value problem

B

bgreenspan

This is kind of a long post, but hopefully better than being vague with
the details...

I have a database with 3 tables named tblDockets, tblFamilyMembers, and
tblActions. The relationships are 1 to Many from tblDockets to
tblFamilyMembers and 1 to Many from tblFamilyMembers to tblActions.
Each table has numerous fields, but the primary keys are txtDocketNo -
a user-entered value in tblDockets , FamilyRecID - an autonumbered
value in tblFamilyMembers, and ActionsRecID - an autonumbered value in
tblActions. These are the fields used to join the tables.

My ultimate goal is a query result (to display on a form or report)
with one row per Docket despite multiple records per Family or Action.
I hope this makes some sense.

My approach has been a two step process. First a query to find all of
the matching records followed by a query on the first query results to
eliminate the duplicates by txtDocketNo.

The first query looks like this (with some of the many fields
eliminated and some extra carriage returns to make the line fit here)

strSQL = "SELECT
tblDocket.DocketRecID,tblDocket.txtDocketNo,tblDocket.txtProject,
tblDocket.txtProduct,tblDocket.txtDescription, tblDocket.txtClient,
tblFamilyMembers.familyRecID,tblFamilyMembers.txtFamilyMemberNo,tblFamilyMembers.txtDocketNo,
tblFamilyMembers.txtApplNo,tblFamilyMembers.dteFilingDate,
tblActions.ActionsRecID,tblActions.FamilyRecID,tblActions.txtActionNo,
tblActions.txtAction,tblActions.dteDueDate,
FROM (tblDocket LEFT JOIN tblFamilyMembers ON tblDocket.txtDocketNo =
tblFamilyMembers.txtDocketNo) LEFT JOIN tblActions ON
tblFamilyMembers.FamilyRecID = tblActions.FamilyRecID WHERE " &
strwhere & ";"

The strwhere is built from a form as neatly described elsewhere (MS
website). This works perferctly fine when called from

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_Docket1",strSQL)

My next step is to query the results in qryDynamic_Docket1 as follows
(with MOST of the fields eliminated for simplicity):

strSQL2 = "SELECT [qryDynamic_Docket1]!tblDocket.DocketRecID,
First([qryDynamic_Docket1]!tlbDocket.txtDocketNo) AS
FirstOftxtDocketNo,
First(qryDynamic_Docket1.txtAttyAgent) AS FirstOftxtAttyAgent,
First(qryDynamic_Docket1.txtProject) AS FirstOftxtProject,
First(qryDynamic_Docket1.txtProduct) AS FirstOftxtProduct,
First(qryDynamic_Docket1.txtDescription) AS FirstOftxtDescription,
First(qryDynamic_Docket1.txtDescrAbstract) AS FirstOftxtDescrAbstract,
First(qryDynamic_Docket1.txtInLicensed) AS FirstOftxtInLicensed,
First(qryDynamic_Docket1.txtLicensor) AS FirstOftxtLicensor FROM
qryDynamic_Docket1
GROUP BY (qryDynamic_Docket1.tblDocket.DocketRecID);"

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_Docket",
strSQL2)

When this statement is reached, I am to Enter Parameter Value for
qryDynamic_Docket1!tblDocket.txtDocketNo. If I simply click OK I get
the exact results I want MINUS the critically important txtDocketNo
values. If I enter something, that something gets put into each row,
an equally unacceptable result.

At first I thought I was not properly referencing the value (being
consantly confused with the ! and .) This may still be the case. But
other than that, I am baffled.

Sorry again for the long post. Can you help???

Thanks,
-Bernie
 
M

Marshall Barton

This is kind of a long post, but hopefully better than being vague with
the details...

I have a database with 3 tables named tblDockets, tblFamilyMembers, and
tblActions. The relationships are 1 to Many from tblDockets to
tblFamilyMembers and 1 to Many from tblFamilyMembers to tblActions.
Each table has numerous fields, but the primary keys are txtDocketNo -
a user-entered value in tblDockets , FamilyRecID - an autonumbered
value in tblFamilyMembers, and ActionsRecID - an autonumbered value in
tblActions. These are the fields used to join the tables.

My ultimate goal is a query result (to display on a form or report)
with one row per Docket despite multiple records per Family or Action.
I hope this makes some sense.

My approach has been a two step process. First a query to find all of
the matching records followed by a query on the first query results to
eliminate the duplicates by txtDocketNo.

The first query looks like this (with some of the many fields
eliminated and some extra carriage returns to make the line fit here)

strSQL = "SELECT
tblDocket.DocketRecID,tblDocket.txtDocketNo,tblDocket.txtProject,
tblDocket.txtProduct,tblDocket.txtDescription, tblDocket.txtClient,
tblFamilyMembers.familyRecID,tblFamilyMembers.txtFamilyMemberNo,tblFamilyMembers.txtDocketNo,
tblFamilyMembers.txtApplNo,tblFamilyMembers.dteFilingDate,
tblActions.ActionsRecID,tblActions.FamilyRecID,tblActions.txtActionNo,
tblActions.txtAction,tblActions.dteDueDate,
FROM (tblDocket LEFT JOIN tblFamilyMembers ON tblDocket.txtDocketNo =
tblFamilyMembers.txtDocketNo) LEFT JOIN tblActions ON
tblFamilyMembers.FamilyRecID = tblActions.FamilyRecID WHERE " &
strwhere & ";"

The strwhere is built from a form as neatly described elsewhere (MS
website). This works perferctly fine when called from

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_Docket1",strSQL)

My next step is to query the results in qryDynamic_Docket1 as follows
(with MOST of the fields eliminated for simplicity):

strSQL2 = "SELECT [qryDynamic_Docket1]!tblDocket.DocketRecID,
First([qryDynamic_Docket1]!tlbDocket.txtDocketNo) AS
FirstOftxtDocketNo,
First(qryDynamic_Docket1.txtAttyAgent) AS FirstOftxtAttyAgent,
First(qryDynamic_Docket1.txtProject) AS FirstOftxtProject,
First(qryDynamic_Docket1.txtProduct) AS FirstOftxtProduct,
First(qryDynamic_Docket1.txtDescription) AS FirstOftxtDescription,
First(qryDynamic_Docket1.txtDescrAbstract) AS FirstOftxtDescrAbstract,
First(qryDynamic_Docket1.txtInLicensed) AS FirstOftxtInLicensed,
First(qryDynamic_Docket1.txtLicensor) AS FirstOftxtLicensor FROM
qryDynamic_Docket1
GROUP BY (qryDynamic_Docket1.tblDocket.DocketRecID);"

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_Docket",
strSQL2)

When this statement is reached, I am to Enter Parameter Value for
qryDynamic_Docket1!tblDocket.txtDocketNo. If I simply click OK I get
the exact results I want MINUS the critically important txtDocketNo
values. If I enter something, that something gets put into each row,
an equally unacceptable result.

At first I thought I was not properly referencing the value (being
consantly confused with the ! and .) This may still be the case. But
other than that, I am baffled.
Click to expand...


Since tblDocket is not defined anywhere in your query, it
will cause a prompt. Not only that, but you can only use a
field reference with a construct like
[table/query name].[field name]

I think everything should be fine if you just get rid of the
tblDocket and use [qryDynamic_Docket1].DocketRecID and
[qryDynamic_Docket1].txtDocketNo.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't get Enter Parameter Value boxes to disappear 1
Enter Parameter Value Message Pops Up 4
"Enter Parameter Value" when sorting 4
Enter Parameter Value 1
Why am I asked to enter parameter value? 2
Why am I asked to enter parameter value? (Second try) 3
Enter Parameter Value keeps appearing 1
Dynamic query problem 15

Top