Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using VBScript in a report (Access 2000/SR-1)

    I've created an access report, and want to be able to use the value in the previous record of the report for a calculation.

    I'm sure I can use VBScript to store the value in a variable, but not sure how...

    Any help would be much appreciated

    Thanks in advance for any help

    - Maurice

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    Put the code to save the variable you need in the OnFormat event of the Detail section.
    You will need to declare a variable at the top of the section immediately af the option statements.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    In addition to Patt's reply: Access, like the other Office applications, uses Visual Basic for Applications (VBA) as programming language, not VBScript.

  4. #4
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    I'm a newbie to access & VBA, how do I put the code in the OnFormat event.

    The code I need will be something like

    PrevRecValue = mileage

    and the declaration

    PrevRecValue as long

    Thanks again

    - Maurice

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    The exact code will depend on your situation, but it'll be more or less as follows. I have assumed that Mileage is a field in the record source of the report and that it is of type Numeric (Long Integer).
    <UL><LI>Open the report in design view.
    <LI>Click on the gray bar at the top of the details selection.
    <LI>Activate the Properties window (View | Properties if it isn't visible.)
    <LI>Activate the Events tab.
    <LI>Click in the On Format event.
    <LI>Select Event Procedure from the dropdown list.
    <LI>Click the builder button (the three dots ... on the right hand side)
    <LI>You'll see the first and last line of the event procedure:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    End Sub

    <LI>Click in the empty line ABOVE Private Sub ... BELOW any Option ... statements.
    <LI>Type the following lines:

    Private PrevRecMileage As Long
    Private CurRecMileage As Long

    This declares two variables: one to hold the current value, and one to hold the previous value. If Mileage has a different data type, modify "As Long" accordingly.
    <LI>Click in the empty line between Private Sub ... and End Sub.
    <LI>Type the following lines:

    PrevRecMileage = CurRecMileage
    CurRecMileage = Me.Mileage[/list]I don't know how you want to use the variable, so post back if you need more help, with details about the way you want to use it,

  6. #6
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    Thanks HansV,

    It's a simple database to record fuel consumption, the reason for the VBA is so I can calculate miles travelled.

    Database contains:- Date, mileage, fuel, cost

    I wanted the report to be something like:-

    Date, Mileage, Miles Traveled, Fuel, Cost

    So just a little help with the last part to print the miles traveled (CurRecMileage - PrevRecMileage)

    Thanks again

    - Maurice

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    Put an unbound text box in the Detail section of the report (unbound means leave the Control Source property empty). Name this text box txtMilesTraveled.
    Add the following line to the Detail_Format event procedure, after the lines you already have (but above End Sub):

    Me,txtMilesTraveled = CurRecMileage - PrevRecMileage

    Notes:

    1. You will notice that MilesTraveled is incorrect for the first record. You may get around this by adding one line of code to the OnFormat event of the Report Header section (create it the same way as you created the event procedure for the Detail section):

    CurRecMileage = Me.Mileage

    This sets the initial value to that of the first record, so that the first distance will be 0.

    2. It would be possible to create a query that returns the value of the Mileage field in the previous record, but the solution you have now should work OK.

  8. #8
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Thanks again...

    You said it would be possible to create a query that returns the value, even though my report is working I'd be interested in how you would achieve this

    Thanks for all your help

    - Maurice

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    You would need to create a function in a general module to do so, and use this function in the query. The function is fairly advanced, so perhaps you should wait until you have a bit more experience before trying it. For future reference, you can find a description of the technique in Knowledge Base article ACC2000: Fill Record with Data from Previous Record Automatically. This article contains a link to download a sample queries database that demonstrates this technique and many others - recommended!

  10. #10
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using VBScript in a report (Access 2000/SR-1)

    Thanks again for all your help...

    I think I'll take your advice and wait until I'm a bit more experienced.

    Thanks

    - Maurice

Posting Permissions

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