Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Posts
    11

    Unanswered: Macro to print report from latest record

    On a data entry-type form, (no saved records viewable), I've got a button control and am trying to make a macro that will:

    1. Display some sort of "Thank You" message (did this already)
    2. Go to a new record (did this already)
    3. Print a report for the record just entered. I'm having trouble with the "Where Condition" in the macro. I can base this on the customerID field (which is autonumber) or the date field (Now())

    Everything I've tried won't find any record, much less the latest one.

  2. #2
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Now will not do the trick as it counts the date and time to the second. Why not have it print the record THEN go to a new record ? I think that would be easier.
    Failing that the latest record could be found using Top()

    My 2 cents worth.
    Darasen

  3. #3
    Join Date
    Feb 2005
    Posts
    11
    I'm going to print a report, so I assumed the record had to be saved first.

    Can you elaborate a little? The "Where Condition" has to be an SQL WHERE clause or it can be an Expression.

    Something along the lines of

    (WHERE) customerID = MAX([tblCustomer.customerID])

    but I'm sure my syntax isn't right

  4. #4
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    Put this code behind a command button's On Click Event on your form, name the button cmdPrint.

    Next change the code to suit you db, change the Rpt_Customer to the name of your report. You do not have to change CustomerID as it appears we have both used that.

    This code will:

    Check to see if the form is blank and if it is then display a msg box
    If changes have been made, saves the form
    Print out a report that relates by CustomerID to the Current Record in the form.


    Code:
    On Error GoTo Err_cmdPrintReport_Click
    
    
    'This sets your variables
    Dim StrCriterion As String
    Dim strMsg As String
    Dim strTitle As String
    Dim intStyle As Integer
    
    
    'This checks to see if the record (form) is blank and if so will not print or preview but displays a message
    
    If IsNull(Me![CustomerID]) Then
    
            strMsg = "You cannot Print a Blank Form !!."
                strTitle = "Print Error"
                    intStyle = vbOKOnly
                        MsgBox strMsg, intStyle, strTitle
    
        Exit Sub
    End If
    
    ' If the record is not blank then it saves it, also when you have just created a new record and try and print it
    ' it will not have been "saved" so this will do it.
    
    If Me.Dirty Then Me.Dirty = False
    
    End If
    
    ' This will establish the link, and print or preview the report.
    
    StrCriterion = "[CustomerID]=" & Me![CustomerID]
    
    DoCmd.OpenReport "Rpt_Customer", acNormal, , StrCriterion
    
    
    
    Exit_cmdPrintReport_Click:
        Exit Sub
    
    Err_cmdPrintReport_Click:
        MsgBox Err.Description
        Resume Exit_cmdPrintReport_Click
    Regards,



    John A

  5. #5
    Join Date
    Feb 2005
    Posts
    11
    When I try this code, I get an "Invalid Outside procedure" error.

    Here is the help content for the error:
    Code:
    Visual Basic for Applications (VBA) encountered a problem while attempting to access a property or method. The problem may be one of the following:
    
    A reference is missing. 
    For help restoring missing references, see the Microsoft Knowledge Base article 283806.
    
    An Expression is misspelled. 
    Check all expressions used in event properties for correct spelling.
    
    A user-defined function is declared as a sub or as a private function in a module.
    Expressions can resolve a user-defined function only if the function is declared as one of the following:
    A public function in a module
    A public or private function in a code module of the current form or report
    Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8 update is not installed.
    A more recent verion of Jet 4.0 must be installed for Access to function properly when security is set to Medium or High. To obtain the latest version of Microsoft Jet, go to Windows Update.
    The only thing I really changed was the name of my report since I was already using 'CustomerID'.

  6. #6
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    Have a look at the attached sample, let me know if that works for you.
    Attached Files Attached Files
    Regards,



    John A

  7. #7
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    If the above doesn't help, the easiest way I could get around this is by creating a global string such as:

    Global GLOcustomerID As String

    Then on the form when you press the button to open the report include the following within the code

    MyCustomerID = Me.CustomerID

    Then in the criteria for your report/query say where customerID = GLOcustomerID

    This works for my reporting without any problem

  8. #8
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    There has got to be something wrong with your set up.

    Have you go CustomerID (Numeric) in both your form and report?

    Is the form and report based on the same query?

    Are you telling me that you opened my sample db and it did not work?

    I have had the full version of that sample db downloaded about 200 times here and on other forums and never had a problem.
    Regards,



    John A

  9. #9
    Join Date
    Feb 2005
    Posts
    11
    I've said nothing about your sample dB. In fact it works great. I just had problems using your button code snippet with my particular setup.

    No need to get defensive.


    Quote Originally Posted by ansentry
    There has got to be something wrong with your set up.

    Have you go CustomerID (Numeric) in both your form and report?

    Is the form and report based on the same query?

    Are you telling me that you opened my sample db and it did not work?

    I have had the full version of that sample db downloaded about 200 times here and on other forums and never had a problem.

  10. #10
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    There are 2 options you can use. The first is to use the Docmd.OpenReport command on the print button. You can filter the reports recordset by using the WhereCondition property.

    Alternatively you could pass to the report a SQL statement that the report will use. Again you use the Docmd.OpenReport command and pass the SQL statement as the OpenArgs property. In the reports OpenEvent you would check to see if the OpenArgs property has any data in it and use it as the reports recordsource. you would use code such as this:

    If Len(Me.OpenArgs) Then Me.Recordsource = Me.OpenArgs

    Note; You can only change the recordsource in a report in the reports open event

    After the report has printed and the user has closed it, control is passed back to the button that called the report. You could then print your message and move to a new record.
    I hope this helps
    Justin

  11. #11
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Cool

    Sorry,

    I pasted my post in the wrong thread. Wasn't sure how to delete it.
    Last edited by christyxo; 02-22-05 at 13:52.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •