Parameter Queries

[Guest]

I am building a form with a parent/child relationship. The child sub-form is control source is a query with begin and end dates as the parameters. I want to pass the dates from another form which activates the parent/child form via a command button. How do I code this. Any help would be greatly appreciated

Davi

 

[Van T. Dinh]

Not sure if I understood your set up correctly but let's
say that you have Form1 with Controls txtStartDate and
txtEndDate then you can simply use the references to these
Controls as the Parameters for the Query being used as the
RecordSource for the SubForm. Something like:

SELECT ...
FROM ...
WHERE [DateField]
BETWEEN Forms!Form1!txtStartDate
AND Forms!Form1!txtEndDate

If Form1 is open and there are date values in the
Controls, Access will use these values to filter the
Recordset for the Subform.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
I am building a form with a parent/child relationship.

The child sub-form is control source is a query with begin
and end dates as the parameters. I want to pass the dates
from another form which activates the parent/child form
via a command button. How do I code this. Any help would
be greatly appreciated.

 

[Guest]

Van

Thanks for getting back to me. A few clarifications. I wanted to use a query I built in the SQL view rather than using a programatic query. When I put the Forms!Form!txtStartDate in the query and tried to save it Access "blew" up twice. I am wondering if I would be better with a VBA query. However, I don't know which event (Open or Load) to place the query. My query is an outer join (salesman to sales joined on salesmanid). When I put the query in the Load event I would get the entire query recordset in my subform plus it would not hide salesmanid in the subform grid. I need all the help I can get. Thanks

David

 

[Van T. Dinh]

Sorry, I don't follow your description and I can't relate what you described
with what you wrote in the original post. I am not even sure what you meant
by programmatic Query or VBA Query???

Since you mentioned in your first post that you want to pass the value from
another Form using the CommandButton, i.e. CommandButton_Click Event, I am
not sure why you mentioned the Open / Load Event (of which Form?). Also,
"blew up" didn't exactly give any useful details for anyone to analyse. It
would be much better if you describe what happened / error number /error
message.

Perhaps you should describe the relevant Table Structure / Forms (you
mentioned 2) with names, which one got the Subform, the RecordSources for
the Forms / Subform, the SQL String (of the Query) and the steps you wants
Access to follow.

--
HTH
Van T. Dinh
MVP (Access)

Van,

Thanks for getting back to me. A few clarifications. I wanted to use a

query I built in the SQL view rather than using a programatic query. When I
put the Forms!Form!txtStartDate in the query and tried to save it Access
"blew" up twice. I am wondering if I would be better with a VBA query.
However, I don't know which event (Open or Load) to place the query. My
query is an outer join (salesman to sales joined on salesmanid). When I put
the query in the Load event I would get the entire query recordset in my
subform plus it would not hide salesmanid in the subform grid. I need all
the help I can get. Thanks.

 

[Guest]

Van,

This post is lengthy, but I want to try to explain everything I have tried to get the program to work.

The form in question is based on the SalesmanTbl table with the primary key SalesmanID. I created a subform based on the SalesTbl table with a key of AutoID and SalesDt. SalesmanID is an index on the Sales table. I linked the tables on SalesmanID. I then wrote an event for the subform's Load event.

The code is below:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

'With rs
Set .ActiveConnection = cn
.Source = "SELECT SMan.SalesmanID, Sales.SalesDt, Sales.AutoID, Sales.DocFeesAmt " & _
"FROM SalesmanTbl AS SMan LEFT JOIN " & _
"[SELECT SalesmanID,SalesDt, AutoID, (TitleFeeAmt+ArbitrationFeeAmt) AS DocFeesAmt " & _
"FROM SalesTbl " & _
"WHERE SalesDt BETWEEN " & _
" #" & DateAdd("y", -6, CDate(Forms!PayrollWeekEndingDtFrm.WeekEndingDt.Value) - 6) & "# " & _
"AND #" & CDate(Forms!PayrollWeekEndingDtFrm.WeekEndingDt.Value) & "# " & _
"ORDER BY SalesmanID,SalesDt,AutoID]. AS Sales " & _
"ON SMan.SalesmanID = Sales.SalesmanID " & _
"ORDER BY Sales.SalesmanID, Sales.SalesDt, Sales.AutoID;"
.LockType = adLockOptimistic
.CursorType = adOpenForwardOnly
.CursorLocation = adUseServer
.Open
End With

Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing

The problem with this code is that for each Salesman the entire recordset would display i.e. the Link field wasn't working.

I tried a number of alternatives (none of which worked) and decided to use another approach: the Database Query object and build a parameter query for the beginning and ending date. When I hard code the dates the form/sub form relation works great; however, I don't know the syntax for passing the parameters via a form rather than the standard query prompt for the dates. The query object is shown below:

SELECT SMan.SalesmanID, Sales.SalesDt, Sales.AutoID, (Sales.TitleFeeAmt+Sales.ArbitrationFeeAmt) AS DocFeesAmt
FROM SalesTbl AS Sales RIGHT JOIN SalesmanTbl AS SMan ON Sales.SalesmanID = SMan.SalesmanID
WHERE (((Sales.SalesDt) Between WeekStartDt And WeekEndingDt))
ORDER BY SMan.SalesmanID, Sales.SalesDt, Sales.AutoID;

I have a form with a command button that passes the dates to the form/subform correctly, but I can not get the query to recognize these parameters. I know the data is being passed because I use it to calculate another field on the form.

I tried various variations of your original suggestion for modifying the query. My attempts were:

Forms!"DateFrm"!"WeekEndingDt"
Forms!'DateFrm'!'WeekEndingDt'
Forms![DateFrm]![WeekEndingDt]"
Forms.[DateFrm].[WeekEndingDt]

where DateFrm is the form passing the date. I am only showing EndingDt to save typing time.

Some of these attempts gave errors when I ran the query or sometimes I got the Send Error Report to Microsoft message.

Thanks for you time and patience.

David

 

[Van T. Dinh]

No. AFAIK, it won't work this way as you assign a *previously created*
Recordset as the RecordSource for the Subform which prevents the filtering
by the LinkParentFields / LinkChildFields. Note that one of the Microsoft
engineers previously advised that the most efficient way to populate a
Subform is to use Table or a saved Query or an SQL String as the
RecordSource for the Subform.

I am not sure why you have to use a Recordset which is not as efficient.
Also, you have to do your own filtering rather than letting the Link Fields
to do the work for you.

--
HTH
Van T. Dinh
MVP (Access)

Van,

This post is lengthy, but I want to try to explain everything I have tried to get the program to work.

The form in question is based on the SalesmanTbl table with the primary

key SalesmanID. I created a subform based on the SalesTbl table with a key
of AutoID and SalesDt. SalesmanID is an index on the Sales table. I linked
the tables on SalesmanID. I then wrote an event for the subform's Load
event.

The code is below:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

'With rs
Set .ActiveConnection = cn
.Source = "SELECT SMan.SalesmanID, Sales.SalesDt,

Sales.AutoID, Sales.DocFeesAmt " & _

"FROM SalesmanTbl AS SMan LEFT JOIN " & _
"[SELECT SalesmanID,SalesDt, AutoID,

(TitleFeeAmt+ArbitrationFeeAmt) AS DocFeesAmt " & _

"FROM SalesTbl " & _
"WHERE SalesDt BETWEEN " & _
" #" & DateAdd("y", -6,

CDate(Forms!PayrollWeekEndingDtFrm.WeekEndingDt.Value) - 6) & "# " & _

"AND #" &

CDate(Forms!PayrollWeekEndingDtFrm.WeekEndingDt.Value) & "# " & _

"ORDER BY SalesmanID,SalesDt,AutoID]. AS Sales " & _
"ON SMan.SalesmanID = Sales.SalesmanID " & _
"ORDER BY Sales.SalesmanID, Sales.SalesDt, Sales.AutoID;"
.LockType = adLockOptimistic
.CursorType = adOpenForwardOnly
.CursorLocation = adUseServer
.Open
End With

Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing

The problem with this code is that for each Salesman the entire recordset

would display i.e. the Link field wasn't working.

I tried a number of alternatives (none of which worked) and decided to use

another approach: the Database Query object and build a parameter query for
the beginning and ending date. When I hard code the dates the form/sub form
relation works great; however, I don't know the syntax for passing the
parameters via a form rather than the standard query prompt for the dates.
The query object is shown below:

SELECT SMan.SalesmanID, Sales.SalesDt, Sales.AutoID,

(Sales.TitleFeeAmt+Sales.ArbitrationFeeAmt) AS DocFeesAmt

FROM SalesTbl AS Sales RIGHT JOIN SalesmanTbl AS SMan ON Sales.SalesmanID = SMan.SalesmanID
WHERE (((Sales.SalesDt) Between WeekStartDt And WeekEndingDt))
ORDER BY SMan.SalesmanID, Sales.SalesDt, Sales.AutoID;

I have a form with a command button that passes the dates to the

form/subform correctly, but I can not get the query to recognize these
parameters. I know the data is being passed because I use it to calculate
another field on the form.

I tried various variations of your original suggestion for modifying the query. My attempts were:

Forms!"DateFrm"!"WeekEndingDt"
Forms!'DateFrm'!'WeekEndingDt'
Forms![DateFrm]![WeekEndingDt]"
Forms.[DateFrm].[WeekEndingDt]

where DateFrm is the form passing the date. I am only showing EndingDt to save typing time.

Some of these attempts gave errors when I ran the query or sometimes I got

the Send Error Report to Microsoft message.