Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counter (Office 98)

    The following is the code I am trying to use to set up a counter that counts vendor numbers. Vendor_No's are 10 digit numbers like "0000456734", "0008796660" etc. I am trying to use a variable to look at the vendor_no. However I am having trouble holding the variable. If the vendor_ no remains the same the pg_grp should read 1, 1, 1. When the vendor_no changes the pg_grp should be 2,2,2 until the vendor_no changes again. The routine should continue through 12. After the pg_grp reads larger than 12 it should put the pg_grp number back to 1 and increase a pg_brk by 1.
    Ultimately I would like the system to perform a page break after printing 12 vendors invoices.
    Any ideas or suggestions would be appreciated.


    Public Function pg_bk()

    Dim dbs As Database
    Dim rst As Recordset
    Dim cven_no As Variant
    Dim nbrk_cnt As Integer
    Dim npg_grp As Integer

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("rents")

    npg_grp = 1
    nbrk_cnt = 1

    Do While Not rst.EOF

    With rst
    .Edit

    If !VENDOR_NO = cven_no Then
    !PG_BRK = nbrk_cnt
    !PG_GRP = npg_grp
    Else
    nbrk_cnt = nbrk_cnt + 1
    End If

    If nbrk_cnt > 12 Then
    nbrk_cnt = 1
    npg_grp = npg_grp + 1
    End If
    !PG_BRK = nbrk_cnt
    !PG_GRP = npg_grp
    cven_no = !VENDOR_NO
    .Update
    .MoveNext
    End With
    Loop
    End Function

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

    Re: Counter (Office 98)

    If your purpose is to insert a page break into a report after each 12 vendors, if would follow a different approach:
    <UL><LI>Group the report on the Vendor_No field, and set the Group Footer property in the Sorting and Grouping window to Yes.
    <LI>Put a text box in the group footer, set its Visible property to No, its Control Source to =1 and its Running Sum property to Over Groups. Let's call it txtCount.
    <LI>Put a page break control in the group footer and set its Visible property to No. Let's call it pgeBreak.
    <LI>Put code in the On Format event of the group footer:

    Private Sub GroupFooter_Format(Cancel As Integer, FormatCount As Integer)
    pgeBreak.Visible = (txtCount Mod 12 = 0)
    End Sub

    This code makes the page break visible only if the vendor count is divisible by 12. By putting it in the group footer, it becomes effective after the records for the vendor have been displayed/printed.[/list]

  3. #3
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter (Office 98)

    Your suggestion worked beautifully! Thank you!

  4. #4
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter (Office 98/Access97)

    Along the same lines. If I would like to total all the like vendor numbers but only show the complete total on the last invoice how would I word it?

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

    Re: Counter (Office 98/Access97)

    The easiest way is to show the total in the group footer section. If you put a text box there (visible this time) with control source =Sum([FieldToSum]) (where FieldToSum must be replaced by the field you want to sum), Access will automatically calculate the sum within each group. It will be displayed immediately below the last invoice of the vendor.

    If you really want the total to be displayed in the last invoice itself, it would take some VBA; I don't know offhand how difficult it would be. Post back if you need this.

  6. #6
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counter (Office 98/Access97)

    Thank you for your assistance. Again it worked beautifully!

Posting Permissions

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