Command Button to Open Report Based on Current Record

[JessicaNicole]

I'm sure this is super simple and I'm just over-thinking.

I have a tabbed form with subforms on each tab. They all update how they are supposed to. I want to put a command button on one of the subforms that will open according to the record that the subform is currently on. I tried just doing the normal wizard, didn't work. So I looked online for some code and this is what I found:

Private Sub cmdOpenGrowerInforpt_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rptGrowerInformation", acViewPreview, , "[GrowerID]='&"
[GrowerID]
End Sub



I bolded that last part because it's whats causing the code not to run. I don't know what I should be putting there to make it work. Hopefully I'm making sense. Any help would be great. Thanks!

[John_G]

Hi -

I think it's just a syntax error. Try this:

DoCmd.OpenReport "rptGrowerInformation", acViewPreview, , "[GrowerID]='" me![GrowerID] & "'" if [GrowerID] is NOT numeric,

and

DoCmd.OpenReport "rptGrowerInformation", acViewPreview, , "[GrowerID] = " & Me![GrowerID] if [GrowerID] IS numeric.

I use me![GrowerID] to ensure MS Access knows it's a form control, though it would probably run without it.

[GrowerID] must be a field in the record source table/query for the report.

John

[JessicaNicole]

I put that code in and nothing happens when I click on the button. I don't have any compile errors with that code either.

[John_G]

Are you sure that the docmd.openreport is being executed? You can check by putting a msgbox "Report opening" command just before it.

If the docmd.openreport is running, could it be opening the report, but putting it behind other windows? I've seen that happen.

If the msgbox does not display - is the code in the OnClick event for the right button?

John

[Missinglinq]

The example for the syntax where GrowerID is NOT numeric, i.e. is defined as Text, was missing an Ampersand:

DoCmd.OpenReport "rptGrowerInformation", acViewPreview, , "[GrowerID]= '" & Me![GrowerID] & "'"

Linq ;0)>

[John_G]

Thanks, Linq - that's what happens when one doesn't proof read.

J.

[JessicaNicole]

The message box isn't showing up either. I checked and it is in the right on click. I only have this button in my db so far. The GrowerID is a numeric number.

[ketbdnetbp]

JessicaNicole - You could try...

Private Sub cmdOpenGrowerInforpt_Click()
DoCmd.RunCommand acCmdSaveRecord
Dim stDocName As String
stDocName = "rptGrowerInformation"
Dim stLinkCriteria As String
stLinkCriteria = "[GrowerID]=" & "Me![GrowerID]"
DoCmd.OpenReport stDocName, acViewPreview, stLinkCriteria
End Sub

In addition, I don’t know if it really matters but, you may want to check the reports Filter On property, it may have to be YES.

Hope this helps,

Jim

[John_G]

Do the properties for the command button have [Event Procedure] for the On Click event? If not, that is the problem - Access does not know there is a procedure associated with the Click event.

Where is the procedure? It has to be in the code module for the form.

You are not even seeing the MsgBox, which indicates that for some reason the procedure is not being executed at all.

John

[joshynaresh]

I have one form "Journal Credit" have command bottom "Post" and i have one report "Journal Receipt". both table have ID field with same value.

I want when I click on post bottom of form "Journal Credit" record should be saved and report "journal Receipt" should be open as print preview as dialog with same record.
is it possible with VBA code.
Plz help be with VBA Code.