parameter query info in form

[Microsoft]

Greetings,

I have a question about data from a parameter query in a form. I know that
you can put the data passed to a parameter query into a text box on a report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date] & "
and " & [Type an ending date] ). I want to know if there is any way to do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods. I
want to put the dates on the form.

Best regards,
Scott B

 

[Rick B]

Yes. Just put the parameter in an unbound text box just like on a report.

Rick B


Greetings,

I have a question about data from a parameter query in a form. I know that
you can put the data passed to a parameter query into a text box on a report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date] & "
and " & [Type an ending date] ). I want to know if there is any way to do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods. I
want to put the dates on the form.

Best regards,
Scott B

 

[Rick B]

Well, I thought I had done this before. Turns out I did it differently. I
am not sure if this can be done. The test I just did, did not give the
results I expected. Sorry.

Rick B


Greetings,

I have a question about data from a parameter query in a form. I know that
you can put the data passed to a parameter query into a text box on a report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date] & "
and " & [Type an ending date] ). I want to know if there is any way to do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods. I
want to put the dates on the form.

Best regards,
Scott B

 

[Microsoft]

Rick,

I'm glad you responded the second time, because I have been getting a #NAME
error when I do it and I was going to ask you how you did it. It surprised
me the this does not work because most thing that work for reports work for
forms as well.

Please let me know if you think of how you did it.

Best regards,
Scott B

 

[fredg]

Well, I thought I had done this before. Turns out I did it differently. I
am not sure if this can be done. The test I just did, did not give the
results I expected. Sorry.

Rick B

Greetings,

I have a question about data from a parameter query in a form. I know that
you can put the data passed to a parameter query into a text box on a report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date] & "
and " & [Type an ending date] ). I want to know if there is any way to do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods. I
want to put the dates on the form.

Best regards,
Scott B

Create an unbound form.
Add 2 text controls.
Name one "BeginDate", name the other "EndDate".
Set the Format property of each control to a Date format.
Name this form "ParamForm".

Add a command button to the ParamForm.
Code it's click event:
Me.Visible = False

Then use, as query criteria:
Between forms!ParamForm!BeginDate AND forms!ParamForm!EndDate

Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Code the Form's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Form's Close event:
DoCmd.Close acForm, "ParamForm"

Open this form. It will open the ParamForm. Enter the 2 dates.
When you click the command button, the ParamForm will become not
visible. The Form will display the query results.
When the Form is closed it will close the ParamForm.

The parameters will show on the form.

 

[Rick B]

In the form I was thinking of, I pull records from a particular client
number and I promt the user for that number. I display the number in the
form header, but I did not pull it from the prompt, I simply pull the client
number from the first detail. This will not work in your case.

Hopefully someone will respond. If the form is based on a saved query, you
might be able to reference the prompt by using something like
Queries![SomeQueryName]![Type a beginning date] in your text box. I have
not tried this, but it's an idea.

The other thing you might try is putting a text box and then use vba code to
change the control source to the paramerter with something like

me.[SomeTextBox] = Queries![SomeQueryName]![Type a beginning date]

again, not tested, just an idea.

Good luck.

Rick B


Rick,

I'm glad you responded the second time, because I have been getting a #NAME
error when I do it and I was going to ask you how you did it. It surprised
me the this does not work because most thing that work for reports work for
forms as well.

Please let me know if you think of how you did it.

Best regards,
Scott B

 

[Microsoft]

Fred,

I followed your directions and it worked and it didn't. The paramform
works, but I first get the query parameter dialog box, then the paramform
box. And the unbound text box on the query form says "Between and".

Any thoughts?

Best regards,
Scott

Well, I thought I had done this before. Turns out I did it differently. I
am not sure if this can be done. The test I just did, did not give the
results I expected. Sorry.

Rick B

Greetings,

I have a question about data from a parameter query in a form. I know that
you can put the data passed to a parameter query into a text box on a report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date] & "
and " & [Type an ending date] ). I want to know if there is any way to do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods. I
want to put the dates on the form.

Best regards,
Scott B

Create an unbound form.
Add 2 text controls.
Name one "BeginDate", name the other "EndDate".
Set the Format property of each control to a Date format.
Name this form "ParamForm".

Add a command button to the ParamForm.
Code it's click event:
Me.Visible = False

Then use, as query criteria:
Between forms!ParamForm!BeginDate AND forms!ParamForm!EndDate

Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Code the Form's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Form's Close event:
DoCmd.Close acForm, "ParamForm"

Open this form. It will open the ParamForm. Enter the 2 dates.
When you click the command button, the ParamForm will become not
visible. The Form will display the query results.
When the Form is closed it will close the ParamForm.

The parameters will show on the form.

 

[fredg]

Fred,

I followed your directions and it worked and it didn't. The paramform
works, but I first get the query parameter dialog box, then the paramform
box. And the unbound text box on the query form says "Between and".

Any thoughts?

Best regards,
Scott

Well, I thought I had done this before. Turns out I did it differently. I
am not sure if this can be done. The test I just did, did not give the
results I expected. Sorry.

Rick B

Greetings,

I have a question about data from a parameter query in a form. I know that
you can put the data passed to a parameter query into a text box on a report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date] & "
and " & [Type an ending date] ). I want to know if there is any way to do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods. I
want to put the dates on the form.

Best regards,
Scott B

Create an unbound form.
Add 2 text controls.
Name one "BeginDate", name the other "EndDate".
Set the Format property of each control to a Date format.
Name this form "ParamForm".

Add a command button to the ParamForm.
Code it's click event:
Me.Visible = False

Then use, as query criteria:
Between forms!ParamForm!BeginDate AND forms!ParamForm!EndDate

Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Code the Form's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Form's Close event:
DoCmd.Close acForm, "ParamForm"

Open this form. It will open the ParamForm. Enter the 2 dates.
When you click the command button, the ParamForm will become not
visible. The Form will display the query results.
When the Form is closed it will close the ParamForm.

The parameters will show on the form.

Arrgh!!! I mis-spoke.
Here is my goof (this would work fine in a report, however).
Instead of having an unbound control in the form and coding it's
control source (as indicated above), leave it's control source blank.

Code the Form's Load event:
[ControlName] = "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Please make sure you did in fact change the query criteria syntax to:
Between forms!ParamForm!BeginDate and forms!ParamForm!EndDate

 

[Microsoft]

Fred,

Big step forward. Everything works except that I still get the query dialog
box before the ParamForm dialog box. But it works if I enter the dates in
both boxes. If I hit cancle on the query dialog box, I get an error saying
the open form action has been canceled. Have I not coded the open form
event correctly? Here is the code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamForm", , , , , acDialog
End Sub

Best regards,
Scott B

Fred,

I followed your directions and it worked and it didn't. The paramform
works, but I first get the query parameter dialog box, then the paramform
box. And the unbound text box on the query form says "Between and".

Any thoughts?

Best regards,
Scott

On Mon, 17 May 2004 10:44:09 -0500, Rick B wrote:

Well, I thought I had done this before. Turns out I did it

differently.
I

am not sure if this can be done. The test I just did, did not give the
results I expected. Sorry.

Rick B

Greetings,

I have a question about data from a parameter query in a form. I know that
you can put the data passed to a parameter query into a text box on a report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning

date]
& "

and " & [Type an ending date] ). I want to know if there is any way

to
do

this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date

periods.
I

want to put the dates on the form.

Best regards,
Scott B

Create an unbound form.
Add 2 text controls.
Name one "BeginDate", name the other "EndDate".
Set the Format property of each control to a Date format.
Name this form "ParamForm".

Add a command button to the ParamForm.
Code it's click event:
Me.Visible = False

Then use, as query criteria:
Between forms!ParamForm!BeginDate AND forms!ParamForm!EndDate

Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Code the Form's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Form's Close event:
DoCmd.Close acForm, "ParamForm"

Open this form. It will open the ParamForm. Enter the 2 dates.
When you click the command button, the ParamForm will become not
visible. The Form will display the query results.
When the Form is closed it will close the ParamForm.

The parameters will show on the form.

Arrgh!!! I mis-spoke.
Here is my goof (this would work fine in a report, however).
Instead of having an unbound control in the form and coding it's
control source (as indicated above), leave it's control source blank.

Code the Form's Load event:
[ControlName] = "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Please make sure you did in fact change the query criteria syntax to:
Between forms!ParamForm!BeginDate and forms!ParamForm!EndDate

 

[fredg]

Fred,

Big step forward. Everything works except that I still get the query dialog
box before the ParamForm dialog box. But it works if I enter the dates in
both boxes. If I hit cancle on the query dialog box, I get an error saying
the open form action has been canceled. Have I not coded the open form
event correctly? Here is the code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamForm", , , , , acDialog
End Sub

Best regards,
Scott B

Fred,

I followed your directions and it worked and it didn't. The paramform
works, but I first get the query parameter dialog box, then the paramform
box. And the unbound text box on the query form says "Between and".

Any thoughts?

Best regards,
Scott

On Mon, 17 May 2004 10:44:09 -0500, Rick B wrote:

Well, I thought I had done this before. Turns out I did it differently.
I
am not sure if this can be done. The test I just did, did not give the
results I expected. Sorry.

Rick B

Greetings,

I have a question about data from a parameter query in a form. I know
that
you can put the data passed to a parameter query into a text box on a
report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date]
& "
and " & [Type an ending date] ). I want to know if there is any way to
do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods.
I
want to put the dates on the form.

Best regards,
Scott B

Create an unbound form.
Add 2 text controls.
Name one "BeginDate", name the other "EndDate".
Set the Format property of each control to a Date format.
Name this form "ParamForm".

Add a command button to the ParamForm.
Code it's click event:
Me.Visible = False

Then use, as query criteria:
Between forms!ParamForm!BeginDate AND forms!ParamForm!EndDate

Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Code the Form's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Form's Close event:
DoCmd.Close acForm, "ParamForm"

Open this form. It will open the ParamForm. Enter the 2 dates.
When you click the command button, the ParamForm will become not
visible. The Form will display the query results.
When the Form is closed it will close the ParamForm.

The parameters will show on the form.

Arrgh!!! I mis-spoke.
Here is my goof (this would work fine in a report, however).

Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Instead of having an unbound control in the form and coding it's
control source (as indicated above), leave it's control source blank.

Code the Form's Load event:
[ControlName] = "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Please make sure you did in fact change the query criteria syntax to:
Between forms!ParamForm!BeginDate and forms!ParamForm!EndDate

The Report Open procedure is correct.

I suspect that there is a slight difference in the actual bracketed
text used in the query criteria and the bracketed text used in the VBA
code, i.e. forms!ParamForm!BeginDate is not the same as
forms!ParamForm!Begin Date. The text must be identical.

I also notice you are using frmParamForm as the parameter form's name.
That's OK as long as you have also used it in the VBA [ControlName] =
etc. statement in the Form's Load event (I used forms!ParamForm!etc.).

It is also correct that you will get that Canceled Open Form message
when you cancel the query parameter.

If you can't find the problem, please copy and paste into a reply
message the query SQL Where clause...
(Where [YourDateField] Between forms!ParamForm! etc.) as well as the
Form's Load event statement (Did you remember to change [ControlName]
to the actual name of the control on the form?) and post it.

 

[Microsoft]

Fred, I checked the VBA and query and they are the same except for brackets.
I took the brackets out of the query and Access put them back (they are in
the SQL). I put them in the VBA and now it won't work. I took them back
out and it still does not work. Here is the code for open, close and load
and also the sql from the query.

Best regards,
Scott

Private Sub Form_Close()
DoCmd.Close acForm, "frmParamForm"
End Sub

Private Sub Form_Load()
[Text26] = "You Chose Dates Between " & Forms!frmParamForm!BeginDate & " and
" & Forms!frmParamForm!EndDate
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamForm", , , , , acDialog
End Sub

SELECT tblRunInfo.run_number, tblCallType.call_type, tblRunInfo.first_name1,
tblRunInfo.mi1, tblRunInfo.last_name1, tblRunInfo.first_name2,
tblRunInfo.mi2, tblRunInfo.last_name2, tblRunInfo.address1,
tblRunInfo.address2, tblRunInfo.city, tblRunInfo.state,
tblRunInfo.run_sheet, tblRunInfo.bfir, tblRunInfo.pcr, tblRunInfo.ekg,
tblRunInfo.misc, tblRunInfo.call_date, tblRunInfo.call_time,
tblCallType.call_type_ID, tblRunInfo.zip
FROM tblCallType INNER JOIN tblRunInfo ON tblCallType.call_type_ID =
tblRunInfo.call_type_ID
WHERE (((tblRunInfo.call_date)="YUou Chose Dates Between " &
[Forms]![frmParamForm]![BeginDate] & " and " &
[Forms]![frmParamForm]![EndDate]))
ORDER BY tblRunInfo.call_date, tblRunInfo.call_time,
tblCallType.call_type_ID;

Fred,

Big step forward. Everything works except that I still get the query dialog
box before the ParamForm dialog box. But it works if I enter the dates in
both boxes. If I hit cancle on the query dialog box, I get an error saying
the open form action has been canceled. Have I not coded the open form
event correctly? Here is the code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamForm", , , , , acDialog
End Sub

Best regards,
Scott B

On Mon, 17 May 2004 20:27:49 -0400, Microsoft wrote:

Fred,

I followed your directions and it worked and it didn't. The paramform
works, but I first get the query parameter dialog box, then the paramform
box. And the unbound text box on the query form says "Between and".

Any thoughts?

Best regards,
Scott

On Mon, 17 May 2004 10:44:09 -0500, Rick B wrote:

Well, I thought I had done this before. Turns out I did it differently.
I
am not sure if this can be done. The test I just did, did not give the
results I expected. Sorry.

Rick B

Greetings,

I have a question about data from a parameter query in a form. I know
that
you can put the data passed to a parameter query into a text box on a
report
to, for example, show the dates the report is for if that is what the
parameter query is based on (i.e. ="Between " & [Type a beginning date]
& "
and " & [Type an ending date] ). I want to know if there is any

way
to

do
this in a form. I have a form that lists the events for a certain date
gotten from a query that asks for the begining and ending date periods.
I
want to put the dates on the form.

Best regards,
Scott B

Create an unbound form.
Add 2 text controls.
Name one "BeginDate", name the other "EndDate".
Set the Format property of each control to a Date format.
Name this form "ParamForm".

Add a command button to the ParamForm.
Code it's click event:
Me.Visible = False

Then use, as query criteria:
Between forms!ParamForm!BeginDate AND forms!ParamForm!EndDate

Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Code the Form's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the Form's Close event:
DoCmd.Close acForm, "ParamForm"

Open this form. It will open the ParamForm. Enter the 2 dates.
When you click the command button, the ParamForm will become not
visible. The Form will display the query results.
When the Form is closed it will close the ParamForm.

The parameters will show on the form.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Arrgh!!! I mis-spoke.
Here is my goof (this would work fine in a report, however).
Now add an unbound control to the form you are using to display the
query results:
= "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Instead of having an unbound control in the form and coding it's
control source (as indicated above), leave it's control source blank.

Code the Form's Load event:
[ControlName] = "Between " & forms!ParamForm!BeginDate & " and " &
forms!ParamForm!EndDate

Please make sure you did in fact change the query criteria syntax to:
Between forms!ParamForm!BeginDate and forms!ParamForm!EndDate

The Report Open procedure is correct.

I suspect that there is a slight difference in the actual bracketed
text used in the query criteria and the bracketed text used in the VBA
code, i.e. forms!ParamForm!BeginDate is not the same as
forms!ParamForm!Begin Date. The text must be identical.

I also notice you are using frmParamForm as the parameter form's name.
That's OK as long as you have also used it in the VBA [ControlName] =
etc. statement in the Form's Load event (I used forms!ParamForm!etc.).

It is also correct that you will get that Canceled Open Form message
when you cancel the query parameter.

If you can't find the problem, please copy and paste into a reply
message the query SQL Where clause...
(Where [YourDateField] Between forms!ParamForm! etc.) as well as the
Form's Load event statement (Did you remember to change [ControlName]
to the actual name of the control on the form?) and post it.

 

[fredg]

Fred, I checked the VBA and query and they are the same except for brackets.
I took the brackets out of the query and Access put them back (they are in
the SQL). I put them in the VBA and now it won't work. I took them back
out and it still does not work. Here is the code for open, close and load
and also the sql from the query.

Best regards,
Scott

Private Sub Form_Close()
DoCmd.Close acForm, "frmParamForm"
End Sub

Private Sub Form_Load()
[Text26] = "You Chose Dates Between " & Forms!frmParamForm!BeginDate & " and
" & Forms!frmParamForm!EndDate
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmParamForm", , , , , acDialog
End Sub

SELECT tblRunInfo.run_number, tblCallType.call_type, tblRunInfo.first_name1,
tblRunInfo.mi1, tblRunInfo.last_name1, tblRunInfo.first_name2,
tblRunInfo.mi2, tblRunInfo.last_name2, tblRunInfo.address1,
tblRunInfo.address2, tblRunInfo.city, tblRunInfo.state,
tblRunInfo.run_sheet, tblRunInfo.bfir, tblRunInfo.pcr, tblRunInfo.ekg,
tblRunInfo.misc, tblRunInfo.call_date, tblRunInfo.call_time,
tblCallType.call_type_ID, tblRunInfo.zip
FROM tblCallType INNER JOIN tblRunInfo ON tblCallType.call_type_ID =
tblRunInfo.call_type_ID
WHERE (((tblRunInfo.call_date)="YUou Chose Dates Between " &
[Forms]![frmParamForm]![BeginDate] & " and " &
[Forms]![frmParamForm]![EndDate]))
ORDER BY tblRunInfo.call_date, tblRunInfo.call_time,
tblCallType.call_type_ID;

On Tue, 18 May 2004 20:38:49 -0400, Microsoft wrote:

*** snipped ***

The Report Open, Close, and the Form Load event appear O.K.

This part is not correct in the Query SQL:

WHERE (((tblRunInfo.call_date)="YUou Chose Dates Between " &
[Forms]![frmParamForm]![BeginDate] & " and " &
[Forms]![frmParamForm]![EndDate]))


From my original reply to you:

Then use, as query criteria:
Between forms!ParamForm!BeginDate AND forms!ParamForm!EndDate

Change it to (all on one line. Also, the Between statement
does NOT use the = sign):

WHERE (((tblRunInfo.call_date) Between
[Forms]![frmParamForm]![BeginDate] and
[Forms]![frmParamForm]![EndDate]))

The brackets in the above SQL are O.K.

You may have confused the syntax of the VBA used in the Form's Load
event (which is correct):

[Text26] = "You Chose Dates Between " & Forms!frmParamForm!BeginDate & " and

" & Forms!frmParamForm!EndDate
with the syntax of the query parameter.

If you still get prompts, take note of the wording of the prompt. It
may be coming from the Report's Sorting and Grouping dialog. If you
once had a control in the report that was used to Sort or Group by and
then deleted it, if you still have it in the Sorting and Grouping
dialog it will prompt for a value. If this is in fact what happened,
delete the control from the Sorting and Grouping dialog.

 

[Microsoft]

Fred,

It works. Here's a surprise. I took out the criteria in the query (which
removed the where statement too) and it started to work just fine. I have
tested it several times and it gives good data and responds fine with the
paramform form you described.

Many thanks for all your help.

Best regards,
Scott B

 

[Microsoft]

Sorry, but I was wrong. Bad data. Put your Where clause back in and prompt
reappears. Don't have any filter in form.
Fustrated.

Scott

 

[fredg]

Sorry, but I was wrong. Bad data. Put your Where clause back in and prompt
reappears. Don't have any filter in form.
Fustrated.

Scott

Scott,
I'm frustrated too.
If you wish, Email it to me and I'll take a look.
Access 2002 or earlier.
Make a duplicate of your database.
Remove all data except that data necessary for this report.
Please alter the remaining data if necessary to protect the privacy of
any person. I just need enough records to run this report.
Make sure the relationships are properly established.
Include the tables, frmParamForm, the query and the report, plus any
code used in the form and report.

To send it to <[email protected]>

The part within the <> is my false address.
Replace example with att and invalid with net

You MUST also write in the Subject line, "Database Where clause".
I'll be looking for that phrase. This way I won't automatically delete
it without reading.
I hope address stuff is clear to you.
Sorry I can't be more explicit but I'd just get too much spam if I
wrote my email address in the clear.


I'll reply back here in the newsgroup when I've looked at it.

 

[Scott B]

Fred,

After much work and more attention on my part your solution is working. I
have incorporated some ideas from Tina in formscoding and all is great. I
cannot thank you enough for your patience.

Best regards,
Scott B