open report button syntax error

J

JohnLute

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
 
J

Jeff

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
 
J

JohnLute

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...
 
D

Dirk Goldgar

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"
 
D

Dirk Goldgar

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, "'", "''") & _
"'"
 
J

JohnLute

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...
 
J

JohnLute

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...
 
D

Dirk Goldgar

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.
 
J

JohnLute

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!
 
Top