Convert macro to vba (2007) (1 Viewer)

[MrRundog]

Anyone know where the Convert macro to vba is on access2007?

else
I need this criteria set into the macro somehow

strCriteria = "[claimID]=Forms!frmClaimsParts!claimID"

& this is the macro

Report, rptParts, PDF Format (*.pdf), , Yes, , 0, Print

 

[dsfcom]

In your macro conditions column put something like:

[Forms]![frmThisForm]![claimID] = [Forms]![frmClaimsParts]![claimID]

Your code portion looks a little ambiguous. You're assigning a value to strCriteria that's assigning a value to [claimID] and that won't work. Assign the value of your form's [claimID] field to strCriteria and then open the report using strCriteria as the criteria or open it using the open forms [claimID] field as the criteria:

DoCmd.OpenReport "rptThisReport", acViewPreview, , "[claimID] = forms!frmThisForm![claimID]", acDialog

or

DoCmd.OpenReport "rptThisReport", acViewPreview, , "[claimID] = strCriteria", acDialog

One of these methods should work. Better the first if the form remains open when the report is opened.

You can convert your macros to modules by opening your macro and clicking SAVE AS and selecting MODULE.

 

[MrRundog]

Hi thanks for the reply.

Firstly - I tried adding to the Conditions but that doesn't work - I get a printout of all records.

The code below is attached to an open report button - This works great and opens the report with the correct ID.

Private Sub Command391_Click()
On Error GoTo Err_Command391_Click
    Dim strCriteria As String
    Dim stDocName As String
  strCriteria = "[claimID]=Forms!frmClaimsParts!claimID"

    stDocName = "rptParts"
    DoCmd.OpenReport stDocName, acPreview, , strCriteria

Exit_Command391_Click:
    Exit Sub

Err_Command391_Click:
    MsgBox Err.Description
    Resume Exit_Command391_Click
    
End Sub

Ok - Now what I need to actually do is to send report to file in pdf format - no problem in access2007 you would think - Except the button to Report to File is not selective - I am getting all records.

So - What i would like is to use the embedded macro code as VBA so I can correctly send just the current record to file.

The embedded macro reads...

Action (Output To)
Arguments
Report, rptParts, PDF Format (*.pdf), , Yes, , 0, Print

Also - for some reason the embedded macro won't give the option to save as a module.

Cheers

 

[dsfcom]

Not sure what you mean by "embedded macro", but if you need to run a macro via code use a line like this:

DoCmd.RunMacro "mcrThisMacro.mnThisName"

To output the report via code only, add something like this to your report's OnLoad or OnOpen event:

DoCmd.OutputTo acOutputReport, "rptName", acFormatPDF, , True

That should output the report to pdf when it's opened and prompt for a file/location to save.

 

[MrRundog]

In access 2007 there is a button to enable you to save to file as a file type EG PDF. The code behind the button is an embedded macro as shown above.

DoCmd.OutputTo acOutputReport, "rptName", acFormatPDF, , True

I can do the above no problem, but I need criteria - else the report printed contains all records. My criteria is

strCriteria = "[claimID]=Forms!frmClaimsParts!claimID"

claimID being the actual primary key for the current record I am viewing on the form.

Where in your code would I stipulate that criteria - Trial and error did nothing for me i'm affraid.

reagards

 

[dsfcom]

Try what is mentioned in post #4. Add the OnOpen/OnLoad event to the report and it will output itself to pdf when it's opened.

 

[MrRundog]

Hi - While all the suggestions work - they all end up printing all records not the speicific record. So i'm still stuck on this one

 

[JBurlison]

that wont work you need to base the report off a query on the table, not the table itself, you can filter form but not tables. create a query and set the criteria as Forms!frmClaimsParts!claimID

 

[MrRundog]

Thanks all - I have it - Remembering i'm using Access 2007 - I have the query set to open just the report that relates to the passed ID as above - Also I have the report filtered with the same criteria - and set the filter to run OnLoad=Yes - This takes care of the report to file(pdf) to just the record we are working on.

Cheers

 

[mpcs]

Here is the embedded macro you need to email a single record in (PDF).

Hope this helps.