Report query asking for parameters

  • Thread starter Thread starter Rocky
  • Start date Start date
R

Rocky

Hi,
I am using Access 2002 sp1. I have a form with a command button that I want
to use to preview a report. I am getting one of the values for the report
query from a label control on the form. Whenever I press the command button
I am prompted for the value, like in a parameter query. I know the query
is correct because I use the same values and procedure for a subform on the
form and it gets the subform data with no problem, the queries are
identical. Also if I type the data into the parameter prompt, the form will
preview without any problems. Any ideas how I can get this value into the
query without having to type it or paste it in the parameter prompt? I hope
this makes sense to someone and I am on the correct newsgroup to be asking
it. Any ideas or help would be appreciated.
Thanks,
Rocky
 
Normally , filters are passed into a report by putting something like this
in the OnClick event of the button:

Dim lngAccountNumber As Long, strCriteria As String

If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If

Can you run the query on its own without getting a parameter prompt?
 
If I just click the query it will give a parameter prompt. I am using the
query in a subform. I have a list box that I select items from another
query; when I click the list item, it loads the date of the item into a
label and shows the subform and then requeries the subform , using the date
that went into the label. The code looks like this:
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
subform2.Visible = True
subform2.Requery
Everything works fine opening and getting data into the subform. I used the
same query to make a report. The query follows and I realize it looks overly
complicated but it is the only way I could get it to work with the subform.
It was also prompting for parameters when I tried other queries.
SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
WHERE Format(testDate,"mmmm")=Format(label65.caption,"mmmm") And
Format(testDate,"dd")=Format(label65.caption,"dd") And
Format(testDate,"yyyy")=Format(label65.caption,"yyyy") And
Format(testDate,"Nn")=Format(label65.caption,"Nn") And
Format(testDate,"h")=Format(label65.caption,"h")
ORDER BY lastName;

In the command button that opens the report there is the following code, I
inserted the second and third lines.
Private Sub Command90_Click()
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
On Error GoTo Err_Command90_Click
Dim stDocName As String
stDocName = "NET Test List"
DoCmd.OpenReport stDocName, acPreview
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub

I know the numbers look high for the number of controls :) but it is really
only a simple form with a list box, monthview control, subform, a few labels
and a command button. I have done a lot of "experimenting" and have not
been naming controls. I am looking thru the code you posted and trying that
now. Thanks for the help.

Treebeard said:
Normally , filters are passed into a report by putting something like this
in the OnClick event of the button:

Dim lngAccountNumber As Long, strCriteria As String

If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If

Can you run the query on its own without getting a parameter prompt?



Rocky said:
Hi,
I am using Access 2002 sp1. I have a form with a command button that I want
to use to preview a report. I am getting one of the values for the report
query from a label control on the form. Whenever I press the command button
I am prompted for the value, like in a parameter query. I know the query
is correct because I use the same values and procedure for a subform on the
form and it gets the subform data with no problem, the queries are
identical. Also if I type the data into the parameter prompt, the form will
preview without any problems. Any ideas how I can get this value into the
query without having to type it or paste it in the parameter prompt? I hope
this makes sense to someone and I am on the correct newsgroup to be asking
it. Any ideas or help would be appreciated.
Thanks,
Rocky
Click to expand...
Click to expand...
 
I changed the code in the command button, I think I forgot to mention the
value I am trying to put in the query is a date. Now I am not getting
prompted for the parameter but I get an error message:

Syntax error (missing operator) in query experssion '(4/22/2003 10:30:00
AM)'.

Here is the new code in my report command button:
Private Sub Command90_Click()
Dim msg2 As Date
msg2 = List39.ItemData(List39.ListIndex)
On Error GoTo Err_Command90_Click
Dim stDocName As String
stDocName = "NET Test List"
DoCmd.OpenReport stDocName, acPreview, , msg2
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub

Rocky said:
If I just click the query it will give a parameter prompt. I am using the
query in a subform. I have a list box that I select items from another
query; when I click the list item, it loads the date of the item into a
label and shows the subform and then requeries the subform , using the date
that went into the label. The code looks like this:
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
subform2.Visible = True
subform2.Requery
Everything works fine opening and getting data into the subform. I used the
same query to make a report. The query follows and I realize it looks overly
complicated but it is the only way I could get it to work with the subform.
It was also prompting for parameters when I tried other queries.
SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
WHERE Format(testDate,"mmmm")=Format(label65.caption,"mmmm") And
Format(testDate,"dd")=Format(label65.caption,"dd") And
Format(testDate,"yyyy")=Format(label65.caption,"yyyy") And
Format(testDate,"Nn")=Format(label65.caption,"Nn") And
Format(testDate,"h")=Format(label65.caption,"h")
ORDER BY lastName;

In the command button that opens the report there is the following code, I
inserted the second and third lines.
Private Sub Command90_Click()
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
On Error GoTo Err_Command90_Click
Dim stDocName As String
stDocName = "NET Test List"
DoCmd.OpenReport stDocName, acPreview
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub

I know the numbers look high for the number of controls :) but it is really
only a simple form with a list box, monthview control, subform, a few labels
and a command button. I have done a lot of "experimenting" and have not
been naming controls. I am looking thru the code you posted and trying that
now. Thanks for the help.

Treebeard said:
Normally , filters are passed into a report by putting something like this
in the OnClick event of the button:

Dim lngAccountNumber As Long, strCriteria As String

If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If

Can you run the query on its own without getting a parameter prompt?



Rocky said:
Hi,
I am using Access 2002 sp1. I have a form with a command button that
Click to expand...
I
want
to use to preview a report. I am getting one of the values for the report
query from a label control on the form. Whenever I press the command button
I am prompted for the value, like in a parameter query. I know the query
is correct because I use the same values and procedure for a subform
Click to expand...
on
the
form and it gets the subform data with no problem, the queries are
identical. Also if I type the data into the parameter prompt, the form will
preview without any problems. Any ideas how I can get this value into the
query without having to type it or paste it in the parameter prompt?
Click to expand...
I
hope
this makes sense to someone and I am on the correct newsgroup to be asking
it. Any ideas or help would be appreciated.
Thanks,
Rocky
Click to expand...
Click to expand...
Click to expand...
 
The problem with your query is that it was only meant to be used with that
subform. It is referencing objects on the subform.
Create another query and set it as the data record source of the report. Put
the following sql in the query:

SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
ORDER BY lastName;

Put this in the OnClick Event on your button

Dim dteTestDate As Date, strCriteria As String, stDocName As String
stDocName = "NET Test List"
If IsDate(me.List39.ItemData(List39.ListIndex)) then
dteTestDate = me.List39.ItemData(List39.ListIndex)
strCriteria = "[testDate] = #" & Format$(dteTestDate , "mm/dd/yyyy ,
H:MM ") & "#"
DoCmd.OpenReport stDocName , acViewPreview, , strCriteria
DoCmd.SelectObject acReport, stDocName , False
Else
msgBox "me.List39.ItemData(List39.ListIndex) is not a date."

End If

You might have to play around with the Format$ command. I'm not sure what
the is "Nn" in the following line you wrote.
Format(testDate,"Nn")=Format(label65.caption,"Nn")
Click to expand...

It is my understanding that a Date variable can only have a h(hour),
m(minute) , s(second) , d(day), m(month), or y(year).

Jack
If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If
Click to expand...
Click to expand...


Rocky said:
If I just click the query it will give a parameter prompt. I am using the
query in a subform. I have a list box that I select items from another
query; when I click the list item, it loads the date of the item into a
label and shows the subform and then requeries the subform , using the date
that went into the label. The code looks like this:
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
subform2.Visible = True
subform2.Requery
Everything works fine opening and getting data into the subform. I used the
same query to make a report. The query follows and I realize it looks overly
complicated but it is the only way I could get it to work with the subform.
It was also prompting for parameters when I tried other queries.
SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
WHERE Format(testDate,"mmmm")=Format(label65.caption,"mmmm") And
Format(testDate,"dd")=Format(label65.caption,"dd") And
Format(testDate,"yyyy")=Format(label65.caption,"yyyy") And
Format(testDate,"Nn")=Format(label65.caption,"Nn") And
Format(testDate,"h")=Format(label65.caption,"h")
ORDER BY lastName;

In the command button that opens the report there is the following code, I
inserted the second and third lines.
Private Sub Command90_Click()
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
On Error GoTo Err_Command90_Click
Dim stDocName As String
stDocName = "NET Test List"
DoCmd.OpenReport stDocName, acPreview
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub

I know the numbers look high for the number of controls :) but it is really
only a simple form with a list box, monthview control, subform, a few labels
and a command button. I have done a lot of "experimenting" and have not
been naming controls. I am looking thru the code you posted and trying that
now. Thanks for the help.

Treebeard said:
Normally , filters are passed into a report by putting something like this
in the OnClick event of the button:

Dim lngAccountNumber As Long, strCriteria As String

If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If

Can you run the query on its own without getting a parameter prompt?



Rocky said:
Hi,
I am using Access 2002 sp1. I have a form with a command button that
Click to expand...
I
want
to use to preview a report. I am getting one of the values for the report
query from a label control on the form. Whenever I press the command button
I am prompted for the value, like in a parameter query. I know the query
is correct because I use the same values and procedure for a subform
Click to expand...
on
the
form and it gets the subform data with no problem, the queries are
identical. Also if I type the data into the parameter prompt, the form will
preview without any problems. Any ideas how I can get this value into the
query without having to type it or paste it in the parameter prompt?
Click to expand...
I
hope
this makes sense to someone and I am on the correct newsgroup to be asking
it. Any ideas or help would be appreciated.
Thanks,
Rocky
Click to expand...
Click to expand...
Click to expand...
 
Thank you sir! I got it to work with some very minor tweaking like you said
it might need in the date time format. This got me over a big hurdle in
this project. I appreciate your time and effort helping me.
Rocky

Treebeard said:
The problem with your query is that it was only meant to be used with that
subform. It is referencing objects on the subform.
Create another query and set it as the data record source of the report. Put
the following sql in the query:

SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
ORDER BY lastName;

Put this in the OnClick Event on your button

Dim dteTestDate As Date, strCriteria As String, stDocName As String
stDocName = "NET Test List"
If IsDate(me.List39.ItemData(List39.ListIndex)) then
dteTestDate = me.List39.ItemData(List39.ListIndex)
strCriteria = "[testDate] = #" & Format$(dteTestDate , "mm/dd/yyyy ,
H:MM ") & "#"
DoCmd.OpenReport stDocName , acViewPreview, , strCriteria
DoCmd.SelectObject acReport, stDocName , False
Else
msgBox "me.List39.ItemData(List39.ListIndex) is not a date."

End If

You might have to play around with the Format$ command. I'm not sure what
the is "Nn" in the following line you wrote.
Format(testDate,"Nn")=Format(label65.caption,"Nn")
Click to expand...

It is my understanding that a Date variable can only have a h(hour),
m(minute) , s(second) , d(day), m(month), or y(year).

Jack
If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If
Click to expand...
Click to expand...


Rocky said:
If I just click the query it will give a parameter prompt. I am using the
query in a subform. I have a list box that I select items from another
query; when I click the list item, it loads the date of the item into a
label and shows the subform and then requeries the subform , using the date
that went into the label. The code looks like this:
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
subform2.Visible = True
subform2.Requery
Everything works fine opening and getting data into the subform. I used the
same query to make a report. The query follows and I realize it looks overly
complicated but it is the only way I could get it to work with the subform.
It was also prompting for parameters when I tried other queries.
SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
WHERE Format(testDate,"mmmm")=Format(label65.caption,"mmmm") And
Format(testDate,"dd")=Format(label65.caption,"dd") And
Format(testDate,"yyyy")=Format(label65.caption,"yyyy") And
Format(testDate,"Nn")=Format(label65.caption,"Nn") And
Format(testDate,"h")=Format(label65.caption,"h")
ORDER BY lastName;

In the command button that opens the report there is the following code, I
inserted the second and third lines.
Private Sub Command90_Click()
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
On Error GoTo Err_Command90_Click
Dim stDocName As String
stDocName = "NET Test List"
DoCmd.OpenReport stDocName, acPreview
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub

I know the numbers look high for the number of controls :) but it is really
only a simple form with a list box, monthview control, subform, a few labels
and a command button. I have done a lot of "experimenting" and have not
been naming controls. I am looking thru the code you posted and trying that
now. Thanks for the help.

Treebeard said:
Normally , filters are passed into a report by putting something like this
in the OnClick event of the button:

Dim lngAccountNumber As Long, strCriteria As String

If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If

Can you run the query on its own without getting a parameter prompt?



Hi,
I am using Access 2002 sp1. I have a form with a command button
Click to expand...
Click to expand...
that
I into
the
Click to expand...
Click to expand...
 
You're welcome. Normally I would use something like this

[testDate] = #" & Format$(dteTestDate , "mm/dd/yyyy") & "#"

but apparently your "Test Date" has the time associated with it, I believe.
You are going to have to figure out how to extract the hour and minute .

Try searching the help on date format.

Jack


Rocky said:
Thank you sir! I got it to work with some very minor tweaking like you said
it might need in the date time format. This got me over a big hurdle in
this project. I appreciate your time and effort helping me.
Rocky

Treebeard said:
The problem with your query is that it was only meant to be used with that
subform. It is referencing objects on the subform.
Create another query and set it as the data record source of the report. Put
the following sql in the query:

SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
ORDER BY lastName;

Put this in the OnClick Event on your button

Dim dteTestDate As Date, strCriteria As String, stDocName As String
stDocName = "NET Test List"
If IsDate(me.List39.ItemData(List39.ListIndex)) then
dteTestDate = me.List39.ItemData(List39.ListIndex)
strCriteria = "[testDate] = #" & Format$(dteTestDate , "mm/dd/yyyy ,
H:MM ") & "#"
DoCmd.OpenReport stDocName , acViewPreview, , strCriteria
DoCmd.SelectObject acReport, stDocName , False
Else
msgBox "me.List39.ItemData(List39.ListIndex) is not a date."

End If

You might have to play around with the Format$ command. I'm not sure what
the is "Nn" in the following line you wrote.
Format(testDate,"Nn")=Format(label65.caption,"Nn")
Click to expand...

It is my understanding that a Date variable can only have a h(hour),
m(minute) , s(second) , d(day), m(month), or y(year).

Jack
If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If
Click to expand...


Rocky said:
If I just click the query it will give a parameter prompt. I am using the
query in a subform. I have a list box that I select items from another
query; when I click the list item, it loads the date of the item into a
label and shows the subform and then requeries the subform , using
Click to expand...
the
date
that went into the label. The code looks like this:
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
subform2.Visible = True
subform2.Requery
Everything works fine opening and getting data into the subform. I
Click to expand...
used
the
same query to make a report. The query follows and I realize it looks overly
complicated but it is the only way I could get it to work with the subform.
It was also prompting for parameters when I tried other queries.
SELECT firstName, lastName, social, testType, testDate, approvedBy, phone,
recieptNumber, paid
FROM TBL_NetSignUP
WHERE Format(testDate,"mmmm")=Format(label65.caption,"mmmm") And
Format(testDate,"dd")=Format(label65.caption,"dd") And
Format(testDate,"yyyy")=Format(label65.caption,"yyyy") And
Format(testDate,"Nn")=Format(label65.caption,"Nn") And
Format(testDate,"h")=Format(label65.caption,"h")
ORDER BY lastName;

In the command button that opens the report there is the following
Click to expand...
Click to expand...
code,
I
inserted the second and third lines.
Private Sub Command90_Click()
msg = List39.ItemData(List39.ListIndex)
Label65.Caption = msg
On Error GoTo Err_Command90_Click
Dim stDocName As String
stDocName = "NET Test List"
DoCmd.OpenReport stDocName, acPreview
Exit_Command90_Click:
Exit Sub
Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click
End Sub

I know the numbers look high for the number of controls :) but it is really
only a simple form with a list box, monthview control, subform, a few labels
and a command button. I have done a lot of "experimenting" and have not
been naming controls. I am looking thru the code you posted and
Click to expand...
trying
that
now. Thanks for the help.

Normally , filters are passed into a report by putting something
Click to expand...
like
this
in the OnClick event of the button:

Dim lngAccountNumber As Long, strCriteria As String

If IsNumeric(Me.Account) then
strCriteria = "[Account] = " & Me.Account
DoCmd.OpenReport "YourReportName", acViewPreview, , strCriteria
End If

Can you run the query on its own without getting a parameter prompt?



Hi,
I am using Access 2002 sp1. I have a form with a command button
Click to expand...
that
I
want
to use to preview a report. I am getting one of the values for the
report
query from a label control on the form. Whenever I press the command
button
I am prompted for the value, like in a parameter query. I know the
query
is correct because I use the same values and procedure for a
Click to expand...
subform
on
the
form and it gets the subform data with no problem, the queries are
identical. Also if I type the data into the parameter prompt, the form
will
preview without any problems. Any ideas how I can get this value into
the
query without having to type it or paste it in the parameter
Click to expand...
prompt?
I
hope
this makes sense to someone and I am on the correct newsgroup to be
asking
it. Any ideas or help would be appreciated.
Thanks,
Rocky
Click to expand...
Click to expand...
Click to expand...
 
Back
Top