Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Append AutoNumber (XPDev)

    LESSON 1: Never create an Append Query until you
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Append AutoNumber (XPDev)

    Probably the most effective method of generating numbers is to use a single record table that contains the next number you want to use. You need to use either DAO or ADO to manage the process, but essentially what you do is trigger a routine that adds one to the stored number in that table, and then assigns that number to the new record. This should be based on the Before Insert event. The only issue you need to be really concerned about in merging your two tables is that you don't have any duplicate numbers. I'm presuming of course that you want your invoice number to be unique.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Append AutoNumber (XPDev)

    It seems to be that whatever method you use, you are going to have a problem, seeing as how your "old" numbers are higher than your new numbers. Can you renumber your old numbers so that they fall below all the new numbers?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Append AutoNumber (XPDev)

    Wendell:

    I'm not certain how it goes about doing it, but frmOrderNew (DataEntry) has the invoice number (AutoNumber) locked. As soon as you begin to fill out the first entry of the form it assigns the invoice number. There is no Before Insert event for this form. I had thought perhaps I could create another text box (perhaps txtInvoiceNo2) with Max=([InvoiceNo])+1as the Control Source and then append the old records to the new table, but seeing as the old numbers exceed the existing ones (6-digit vs. 5-digit) it doesn't seem that that would work either. All invoice numbers are unique.

    In order to continue the current (5-digit) series I would somehow have to pickup on the Max number of that series, rather than the Max number of the old series (6-digit). Does that make any sense?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Append AutoNumber (XPDev)

    Mark:

    I'm afraid re-numbering the invoices would create an accounting nightmare!

    I'm just thinking out loud here, but (considering the 6-digit issue) might I use a second txtbx to display the old invoice numbers, or would they still end up having new invoice numbers assigned from the Autonumber txtbx? This is confusing!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Append AutoNumber (XPDev)

    The Before Insert event is not the event to put what Wendell has suggested, it should go into the BeforeUpdate event and you should check if it's a new record.
    A97 help says: >>The BeforeInsert event occurs when the user types the first character in a new record, but before the record is actually created.<<

    Wendell's idea is the way to go, but first you must change the Invoice number from a Autonumber to a Long Integer.

    The problem with using Max(InvoiceNo)+1 is when you have multi users entering at the same time, you can easily get duplicate invoice numbers.
    As regards your overlapping invoice number problem, you could put 2 ranges of numbers into the Control table (this is the single record table that Wendell talks about) as well as the NextAvailableInvoiceNo.

    If you need any more help, just post back.

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Append AutoNumber (XPDev)

    Pat:

    Long time no hear from! Good to "see" you again, and thanks for the reply! Sorry I missed you, but I shut off my email, phone, and hearing - I was in the "zone" trying to work this out. I just spent the most miserable Sunday afternoon of my life, but I did come up with a solution (of sorts) for part of the problem. Not the least bit elegant, but it does work.

    I went back to the original db and created a query that deducted an amount from the InvoiceNo (6-digit) so that the highest result was equal to one number less than the InvoiceNo in the current db (5-digit). I then did a Make Table Query that included both the new and the converted InvoiceNo's, and imported it into the current db. I then did an Append Query to update the tblMaster in the current db. All the old numbers fell right in behind the existing ones - thereby assigning any new order the correct series of Autonumbers (yuck!). I retained the old InvoiceNo and placed a txtbx on the frmOrderHistory to display next to the appended numbers. All current orders only show the current Invoice series (txtInvoiceNo), but there is a second box (txtInvoiceNoOld) that shows the old number. Like I said - it ain't elegant!

    The current frmOrderHistory now has two invoice number displays, but only the current year orders have the single entry, everything before the first of the year has two, the new (converted) number and the old one. Depending on how old the order is, you can search for it in one txtbx or the other. Not exactly pretty, but it does work, and it's certainly better than what I had previously. However, I'm still stuck with the AutoNumber issue and would deffinitely like to get away from that - after I sober up!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    If you'd like to walk me through the method you and Wendell have been discussing, I love to hear from you guys - I never want to see another AutoNumber as long as I live (at least not one that is used for display purposes)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Append AutoNumber (XPDev)

    >>If you'd like to walk me through the method you and Wendell have been discussing, I love to hear from you guys<<
    All you need is to replace the Autonumber with a Long Integer number in that table (cannot remember the table name), and put the following code in the BeforeUpdate event of the form.
    The table Control needs the field NextAvailableInvoiceNo defined as a Long Integer number.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim dbs as DAO.Database, rs as DAO.Recordset
    Dim lngInvoiceNo as Long
    Set dbs = CurrentDB
    Set rs = dbs.OpenRecordset("Control")
    rs.Edit
    rs!NextAvailableInvoiceNo = rs!NextAvailableInvoiceNo + 1
    lngInvoiceNo = rs!NextAvailableInvoiceNo
    rs.Update
    txtInvoiceNo = lngInvoiceNo
    Set rs = Nothing
    Set dbs = Nothing
    End Sub

    You will need to change the field txtInvoiceNo to the name of your Invoice number field on your form. I would also lock the invoice number field on your form.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Append AutoNumber (XPDev)

    Is there a chance that the new Invoice numbers will ever "catch-up" with the old numbers? If we can be sure the new invoice numbers will never get beyond 100000, then you could assign the new invoice number using this formula (I've just assumed field and table names):
    if me.NewRecord then
    InvoiceNo = Dmax("InvoiceNo","tblInvoices","InvoiceNo<100000) + 1
    end if

    When I use this technique to create new Invoice numbers, I add this code as the last lines in my form's BeforeUpdate event.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Append AutoNumber (XPDev)

    Pat:

    Let's see if I follow this.
    tblMaster | InvoiceNo: Change from AutoNumber to Long Integer
    Create new table: tblControl | NextAvailableInvoiceNo (Long Integer)(I entered the next highest available InvoiceNo)
    frmOrderNew: insert code in BeforeUpdate event.

    If that's correct (and I have my doubts), then I'm getting an error (see attachment). Keep in mind that you're dealing with someone who doesn't even know what DAO stands for <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Append AutoNumber (XPDev)

    A problem with this solution Mark is the multi user aspect. I know it's a remote possibility but it's there none the less to create duplicate invoice numbers.

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

    Re: Append AutoNumber (XPDev)

    DAO stands for Data Access Objects.
    You will have to set a reference to Microsoft Office DAO 3.6 Object Library (I think that's what it's name is).

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Append AutoNumber (XPDev)

    Pat:

    When the Visual Basic screen pops up with the error, I go to Tools | References but references is grayed-out.
    (Don't ya just love working with a novice?)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Append AutoNumber (XPDev)

    You cannot go into references while code is executing. What you need to do is go into a code module while it's not executing and it will not be greyed out.

    BTW we were all novices once, it's just a matter of time and effort before that changes.

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Append AutoNumber (XPDev)

    Thanks for that tidbit!

    Almost, but variable not defined (attached)
    (Also, please note that I had to change the txtbx from "InvoiceNo" (the existing name) to "ProNo")
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 2 12 LastLast

Posting Permissions

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