open report button syntax error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a form button to open a report to the record that's open in the form.
I've tried the following code but get a syntax error. I'm sure it lies within
the "Forms! & Me!form & [txtProfileID]" string.

Anyone see how I can resolve this?

THANKS!!!

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, , "[tblProfiles.txtProfileID]
= Forms! & Me!form & [txtProfileID]"

Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
 
John
dim strRptName as string
strRptName = "Forms!urFormName!" & [txtProfileID]
debug strRptName to see if that's what you wanted. (it's not the same as
Forms! & Me!form & [txtProfileID])


Jeff
 
Thanks, Jeff!

This button is used in a subform. I need to use the subform for several
other forms. Therefore, if I code the button for the form then I would need
to create multiple subforms with similarly coded buttons for their respective
forms.

Is there another way around this?

THANKS!!!

--
www.Marzetti.com


Jeff said:
John
dim strRptName as string
strRptName = "Forms!urFormName!" & [txtProfileID]
debug strRptName to see if that's what you wanted. (it's not the same as
Forms! & Me!form & [txtProfileID])


Jeff

I need a form button to open a report to the record that's open in the form.
I've tried the following code but get a syntax error. I'm sure it lies within
the "Forms! & Me!form & [txtProfileID]" string.

Anyone see how I can resolve this?

THANKS!!!

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, , "[tblProfiles.txtProfileID]
= Forms! & Me!form & [txtProfileID]"

Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
Click to expand...
Click to expand...
 
JohnLute said:
I need a form button to open a report to the record that's open in
the form. I've tried the following code but get a syntax error. I'm
sure it lies within the "Forms! & Me!form & [txtProfileID]" string.

Anyone see how I can resolve this?

THANKS!!!

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, ,
"[tblProfiles.txtProfileID] = Forms! & Me!form & [txtProfileID]"

Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
Click to expand...


This would be simplest:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & Me!!txtProfileID & "'"

*IF* the field txtProfileID won't contain the single-quote (')
character. If it might -- and I know from working with your data in the
past that it definitely *could* contain the double-quote character --
then this revision of your code would probably be best:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = Forms![" & _
Me.Name & _
"]!txtProfileID"
 
Dirk Goldgar said:
JohnLute said:
I need a form button to open a report to the record that's open in
the form. I've tried the following code but get a syntax error. I'm
sure it lies within the "Forms! & Me!form & [txtProfileID]" string.

Anyone see how I can resolve this?

THANKS!!!

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, ,
"[tblProfiles.txtProfileID] = Forms! & Me!form & [txtProfileID]"

Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
Click to expand...


This would be simplest:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & Me!!txtProfileID & "'"

*IF* the field txtProfileID won't contain the single-quote (')
character. If it might -- and I know from working with your data in
the past that it definitely *could* contain the double-quote
character -- then this revision of your code would probably be best:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = Forms![" & _
Me.Name & _
"]!txtProfileID"
Click to expand...

John, I just saw your reply to Jeff that this is on a subform, not a
main form. In that case, you won't find the subform in the Forms
collection, so my second suggestion won't work. Here's a version of my
first suggestion that should work regardless of whether the profile ID
contains a single-quote or double-quote:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & _
Replace(Me!!txtProfileID, "'", "''") & _
"'"
 
Dirk!!!

As always - a super big thanks! This works perfectly! When I grow up I want
to be just like you!

:)

--
www.Marzetti.com


Dirk Goldgar said:
Dirk Goldgar said:
JohnLute said:
I need a form button to open a report to the record that's open in
the form. I've tried the following code but get a syntax error. I'm
sure it lies within the "Forms! & Me!form & [txtProfileID]" string.

Anyone see how I can resolve this?

THANKS!!!

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, ,
"[tblProfiles.txtProfileID] = Forms! & Me!form & [txtProfileID]"

Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
Click to expand...


This would be simplest:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & Me!!txtProfileID & "'"

*IF* the field txtProfileID won't contain the single-quote (')
character. If it might -- and I know from working with your data in
the past that it definitely *could* contain the double-quote
character -- then this revision of your code would probably be best:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = Forms![" & _
Me.Name & _
"]!txtProfileID"
Click to expand...

John, I just saw your reply to Jeff that this is on a subform, not a
main form. In that case, you won't find the subform in the Forms
collection, so my second suggestion won't work. Here's a version of my
first suggestion that should work regardless of whether the profile ID
contains a single-quote or double-quote:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & _
Replace(Me!!txtProfileID, "'", "''") & _
"'"

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

(please reply to the newsgroup)
Click to expand...
 
I hope this reaches you, Dirk.

I've been using the code below now for awhile and it's been working fine.
Now that I'm entering in multiple revision records I've got more data to work
with which has returned a small glitch. When I use the button for records
with multiple revisions the report generates all of the revision records
rather than only the one that is open.

Is this a button code problem or a report problem? I've reviewed the report
structure and can't see where I would change anything to correct this.

As always, thanks for your generous assistance.

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & _
Replace(Me!txtProfileID, "'", "''") & _
"'"
Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
--
www.Marzetti.com


Dirk Goldgar said:
Dirk Goldgar said:
JohnLute said:
I need a form button to open a report to the record that's open in
the form. I've tried the following code but get a syntax error. I'm
sure it lies within the "Forms! & Me!form & [txtProfileID]" string.

Anyone see how I can resolve this?

THANKS!!!

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, ,
"[tblProfiles.txtProfileID] = Forms! & Me!form & [txtProfileID]"

Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
Click to expand...


This would be simplest:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & Me!!txtProfileID & "'"

*IF* the field txtProfileID won't contain the single-quote (')
character. If it might -- and I know from working with your data in
the past that it definitely *could* contain the double-quote
character -- then this revision of your code would probably be best:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = Forms![" & _
Me.Name & _
"]!txtProfileID"
Click to expand...

John, I just saw your reply to Jeff that this is on a subform, not a
main form. In that case, you won't find the subform in the Forms
collection, so my second suggestion won't work. Here's a version of my
first suggestion that should work regardless of whether the profile ID
contains a single-quote or double-quote:

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & _
Replace(Me!!txtProfileID, "'", "''") & _
"'"

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

(please reply to the newsgroup)
Click to expand...
 
JohnLute said:
I hope this reaches you, Dirk.

I've been using the code below now for awhile and it's been working
fine. Now that I'm entering in multiple revision records I've got
more data to work with which has returned a small glitch. When I use
the button for records with multiple revisions the report generates
all of the revision records rather than only the one that is open.

Is this a button code problem or a report problem? I've reviewed the
report structure and can't see where I would change anything to
correct this.

As always, thanks for your generous assistance.

Private Sub cmdPreviewRevision_Click()
On Error GoTo Err_cmdPreviewRevision_Click
DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[tblProfiles.txtProfileID] = '" & _
Replace(Me!txtProfileID, "'", "''") & _
"'"
Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub
Click to expand...

Hi, John -

What is the recordsource for the report? If txtProfileID doesn't
identify a unique record in that recordsource, then you're going to have
to revise the WhereCondition argument of the call to OpenForm so that
you do identify the one record that you want to report. I don't know if
there is a RevisionID field that is unique, or whether you may need a
combination of txtProfileID and some other field (like, maybe,
RevisionNumber), or even a longer list of fields.
 
Hey, Dirk!

The Ubiquitous Guru is ubiquitous as ever! Hope your knee(s) are cooperating.

Here are the recourd sources:

Report record source:
SELECT tblProfiles.*
FROM tblProfiles INNER JOIN tblProfilesRevisions ON tblProfiles.txtProfileID
= tblProfilesRevisions.txtProfileID;

Subreport record source:
tblProfilesRevisions

As it turns out I do have a unique field to identify each revision:
tblProfilesRevisions.numProfilesRevisionsID

This is an autonumber field.

I'm not sure what you mean by the "WhereCondition argument of the call to
OpenForm." Do you mean in the button's event procedure?

Thanks!
 
JohnLute said:
Hey, Dirk!

The Ubiquitous Guru is ubiquitous as ever! Hope your knee(s) are
cooperating.

Here are the recourd sources:

Report record source:
SELECT tblProfiles.*
FROM tblProfiles INNER JOIN tblProfilesRevisions ON
tblProfiles.txtProfileID = tblProfilesRevisions.txtProfileID;

Subreport record source:
tblProfilesRevisions

As it turns out I do have a unique field to identify each revision:
tblProfilesRevisions.numProfilesRevisionsID

This is an autonumber field.

I'm not sure what you mean by the "WhereCondition argument of the
call to OpenForm." Do you mean in the button's event procedure?
Click to expand...

Yes, that's what I mean, except that I should have said "OpenReport",
not "OpenForm". Sorry about that slip of the brain. With the
information you've provided, I can revise your event procedure as
follows:

'----- start of revised code -----
Private Sub cmdPreviewRevision_Click()

On Error GoTo Err_cmdPreviewRevision_Click

' Save current record, if it hasn't been saved yet.
If Me.Dirty Then Me.Dirty = False

' Open report in preview mode, filtered for the current revision.

DoCmd.OpenReport "rptPKRevisions", acPreview, , _
"[numProfilesRevisionsID] = " & Me!numProfilesRevisionsID

Exit_cmdPreviewRevision_Click:
Exit Sub

Err_cmdPreviewRevision_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRevision_Click

End Sub

'----- end of revised code -----

There are a couple of possible complications that may exist and affect
whether or not the above code will work. First, the above code assumes
that cmdPreviewRevision is on a form that either has a control named
"numProfilesRevisionsID", or has such a field in the recordset to which
it's bound. If that's not the case -- for example, if the field has
some other name, or if numProfilesRevisionsID is on a subform and the
button is on a main form, or vice versa -- the code will fail.

Second, you refer to a subreport based on tblProfilesRevisions, but the
main report is based on a query that joins tblProfiles to that same
table. That's an odd thing to do -- normally you'd either base the main
report on such a query and not use a subreport, or you'd keep the
subreport but base the main report on tblProfiles alone. Maybe I'm not
imagining correctly the way you have set this up. However, if the main
report is *not* based on a query that includes tblProfilesRevisions,
then filtering it by numProfilesRevisionsID is not going to work.
There's no direct way to filter the subform, without more code than
ought to be necessary. So I *think* you ought to be using just a main
report, with no subreport, but grouped by txtProfileID. This is all
speculation on my part, though.
 
Dirk,

First of all I want to say that I find you darn (or insert your choice of
expletive) SMART! You certainly cover all bases. Also, I happen upon your
replies here frequently and you truly display a depth of knowledge. Secondly,
I hope Bill Gates compensates you well for all your efforts. He ought to be
inviting you to his big mansion for an evening of fencing by the pool or at
least some movies in front of his giant wall of televisions.
There are a couple of possible complications that may exist and affect
whether or not the above code will work. First, the above code assumes
that cmdPreviewRevision is on a form that either has a control named
"numProfilesRevisionsID", or has such a field in the recordset to which
it's bound. If that's not the case -- for example, if the field has
some other name, or if numProfilesRevisionsID is on a subform and the
button is on a main form, or vice versa -- the code will fail.
Click to expand...

It's failing. I get an "Enter Parameter Value for numProfilesRevisionsID"
dialog box. cmdPreviewRevision is actually on a subform.
"numProfilesRevisionsID" is part of the Record Source of that subform. Is the
button being on the subform the cause for this?
Second, you refer to a subreport based on tblProfilesRevisions, but the
main report is based on a query that joins tblProfiles to that same
table. That's an odd thing to do -- normally you'd either base the main
report on such a query and not use a subreport, or you'd keep the
subreport but base the main report on tblProfiles alone. Maybe I'm not
imagining correctly the way you have set this up. However, if the main
report is *not* based on a query that includes tblProfilesRevisions,
then filtering it by numProfilesRevisionsID is not going to work.
There's no direct way to filter the subform, without more code than
ought to be necessary. So I *think* you ought to be using just a main
report, with no subreport, but grouped by txtProfileID. This is all
speculation on my part, though.
Click to expand...

Good speculation. I'm puzzled now as to why I designed it this way, too. It
may have been due to the Access-induced stupors I often find myself in. I'm
going to look hard at this for a redesign.
 
Dirk:

OK - I corrected the report design and now the button is working, too.
Obviously, the report design was clashing with the button. So all now appears
well in the Universe for the moment.

Thanks again for your help!!!
 
Secondly, I hope Bill Gates compensates you well
for all your efforts.
Click to expand...

I'm sure I'm worth every cent he pays me. said:
He ought to be inviting you to his big mansion
for an evening of fencing by the pool or at least some movies in
front of his giant wall of televisions.
Click to expand...

I'll tell him, next time we're hanging out together.
It's failing. I get an "Enter Parameter Value for
numProfilesRevisionsID" dialog box. cmdPreviewRevision is actually on
a subform. "numProfilesRevisionsID" is part of the Record Source of
that subform. Is the button being on the subform the cause for this?
Click to expand...

No. It's probably because you don't include the fields from
tblProfilesRevisions in the report's recordsource query.
 
JohnLute said:
Dirk:

OK - I corrected the report design and now the button is working, too.
Obviously, the report design was clashing with the button. So all now
appears well in the Universe for the moment.

Thanks again for your help!!!
Click to expand...

Great! You're welcome.
 
Back
Top