Ugh! I should know this...

  • Thread starter Thread starter Bonnie A
  • Start date Start date
B

Bonnie A

Hi everyone! Happy Thanksgiving! I'm using A02 on XP and I should know this
by now but it's always just one little thing I miss. Darn it.

I have a record on my subform with a field [PlanNum] and a report with a
query that contains the field [PlanNum]. On my subform, I have a button with
the following OnClick:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]=" & Me.PlanNum
DoCmd.RunCommand acCmdZoom100

What am I missing? I've played with the quotes and can't get it to work.
When I click the button I get a parameter with my actual field value in it
and asking me to fill it in. If I fill in the blank spot with GP0041, it
works. I want it to open without having to satisfy the parameter, I could
put that in the query criteria. If the form is open to GP0041, I want to
view the report showing just that record.

Thank you in advance for your time and assistance. I would be lost without
the newsgroups.
 
Hi everyone! Happy Thanksgiving! I'm using A02 on XP and I should know
this by now but it's always just one little thing I miss. Darn it.

I have a record on my subform with a field [PlanNum] and a report with a
query that contains the field [PlanNum]. On my subform, I have a button
with the following OnClick:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]=" & Me.PlanNum
DoCmd.RunCommand acCmdZoom100

What am I missing? I've played with the quotes and can't get it to
work. When I click the button I get a parameter with my actual field
value in it and asking me to fill it in. If I fill in the blank spot
with GP0041, it works. I want it to open without having to satisfy the
parameter, I could put that in the query criteria. If the form is open
to GP0041, I want to view the report showing just that record.

Thank you in advance for your time and assistance. I would be lost
without the newsgroups.
Click to expand...

I believe you need one more comma before your criteria. You are using
the FILTER argument and you need to be using the WHERE argument.
 
On Wed, 26 Nov 2008 05:56:05 -0800, Bonnie A

Since PlanNum is alphanumeric, it must be wrapped in singlequotes:
DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]='" &
Me.PlanNum & "'"

Put a breakpoint on that line, and ensure Me.PlanNum has the value you
expect.

-Tom.
Microsoft Access MVP
 
Bonnie A said:
Hi everyone! Happy Thanksgiving! I'm using A02 on XP and I should know
this
by now but it's always just one little thing I miss. Darn it.

I have a record on my subform with a field [PlanNum] and a report with a
query that contains the field [PlanNum]. On my subform, I have a button
with
the following OnClick:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]=" & Me.PlanNum
DoCmd.RunCommand acCmdZoom100

What am I missing?
Click to expand...

Because PlanNum is a text field, you need to embed quotes around the value.
Assuming that it will never contain the single-quote character, try this:

DoCmd.OpenReport "rJanusLtr", acViewPreview, , _
"[PlanNum]='" & Me.PlanNum & "'"
 
Unless Access 2003 (which I have) is different from Access 2002 (which the
OP has), the Filter argument would follow the first comma, and the Where
argument the second comma, unless I am completely misreading something.

To the OP, is PlanNum a number field? If it is text you need:
"[PlanNum] = """ & Me.PlanNum & """"

Expanded for clarity:
"[PlanNum] = " " " & Me.PlanNum & " " " "

or:
"[PlanNum] = ' " & Me.PlanNum & " ' "

For what exactly is the parameter prompt asking?

If no luck, another thing to try is to eliminate the Where condition, just
to be sure the report opens properly. If you still receive the parameter
prompt with no Where condition, go back to the report's Record Source query
(if it is using a query), or check the Sorting and Grouping.

Rick Brandt said:
Hi everyone! Happy Thanksgiving! I'm using A02 on XP and I should know
this by now but it's always just one little thing I miss. Darn it.

I have a record on my subform with a field [PlanNum] and a report with a
query that contains the field [PlanNum]. On my subform, I have a button
with the following OnClick:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]=" & Me.PlanNum
DoCmd.RunCommand acCmdZoom100

What am I missing? I've played with the quotes and can't get it to
work. When I click the button I get a parameter with my actual field
value in it and asking me to fill it in. If I fill in the blank spot
with GP0041, it works. I want it to open without having to satisfy the
parameter, I could put that in the query criteria. If the form is open
to GP0041, I want to view the report showing just that record.

Thank you in advance for your time and assistance. I would be lost
without the newsgroups.
Click to expand...

I believe you need one more comma before your criteria. You are using
the FILTER argument and you need to be using the WHERE argument.
Click to expand...
 
Thank you, thank you, thank you!!!

That one ALWAYS gets me.

Your time was truly appreciated!
--
Bonnie W. Anderson
Cincinnati, OH


Tom van Stiphout said:
On Wed, 26 Nov 2008 05:56:05 -0800, Bonnie A

Since PlanNum is alphanumeric, it must be wrapped in singlequotes:
DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]='" &
Me.PlanNum & "'"

Put a breakpoint on that line, and ensure Me.PlanNum has the value you
expect.

-Tom.
Microsoft Access MVP

DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]=" & Me.PlanNum
Click to expand...
Click to expand...
 
Hi Dirk,

I truly appreciate the time you put in to help others.

Thank you, it's perfect!
--
Bonnie W. Anderson
Cincinnati, OH


Dirk Goldgar said:
Bonnie A said:
Hi everyone! Happy Thanksgiving! I'm using A02 on XP and I should know
this
by now but it's always just one little thing I miss. Darn it.

I have a record on my subform with a field [PlanNum] and a report with a
query that contains the field [PlanNum]. On my subform, I have a button
with
the following OnClick:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rJanusLtr", acViewPreview, , "[PlanNum]=" & Me.PlanNum
DoCmd.RunCommand acCmdZoom100

What am I missing?
Click to expand...

Because PlanNum is a text field, you need to embed quotes around the value.
Assuming that it will never contain the single-quote character, try this:

DoCmd.OpenReport "rJanusLtr", acViewPreview, , _
"[PlanNum]='" & Me.PlanNum & "'"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Unless Access 2003 (which I have) is different from Access 2002 (which
the OP has), the Filter argument would follow the first comma, and the
Where argument the second comma, unless I am completely misreading
something.
Click to expand...

My mistake. I am on a system where I can't bring up Access right now so
was going from memory. Isn't the view option after the first comma?
 
Oops. I meant to say that the Filter argument is the first comma after the
View argument, which indeed follows the first comma.

The only reason I had the information somewhere near the front of my brain
was that I had responded and read other responses to another OpenReport
question earlier today. Ordinarily I start typing and wait to see what pops
up in the Intellisense (?) menu.
 
Back
Top