Append AutoNumber (XPDev)

Topic

New Reply

LESSON 1: Never create an Append Query until you

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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!

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)

Reply | Quote

>>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.

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

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?)

Reply | Quote

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.

Reply | Quote

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”)

Reply | Quote

Have you got a field called txtProNo, this should be the Invoice number on the form.

Reply | Quote

Yes there is (novice that I am, I wouldn’t lead you down that garden path!)
“ProNo” is in the tblmaster as well as frmOrderNew (where we’re working on the code)

I won’t bore you with the details, but there is a reason I added the additional field.

Reply | Quote

Is your problem solved?

Reply | Quote

No.

It’s not looking for txtProNo in tblControl is it? Because if it is, it won’t find it – it’s in tblMaster.
Did you notice what I assumed in post 269970 ? Was that all correct?

Reply | Quote

The control txtProNo is on the form, or should be, and it’s source should probably be InvoiceNo.

Yep, your assumptions are right, except for txtProNo.

Reply | Quote

Where’d ya go!
Cyber-goblins get ya?!

Not to fret, I’m working in a copy and the original is still plugging along, so just let me know when (if?) you want to take this back up.

Many thanks for your help thusfar! Need to run to the bank, but shall return.

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

There is no multi-user issue. Assuming InvoiceNo is the Primary Key field (or at least a Unique key), then if 2 users should happen to try to write the same new InvoiceNo at the same time, one of them will get a duplicate key error message. When that person tries again, the system will select a new #.

Reply | Quote

Mark:

I owe you an apology. (I might have just sent you an e-mail, but seeing as how it was done in public, the apology should also be)

You were kind enough to reply in post 269916 when I was replying to Pat, and Pat responded to you and sent me a reply before I could get back to you. One thing lead to another and I never got back to you. That’s bad form and, well, just plain rude. I never take for granted the help and advise I receive in this forum, and least of all from someone who has been as helpful as you. It was only after reviewing the whole string that I realized my blunder. I hope you accept my humble apology.

You will see in the string that I did in fact have to resort to changing the InvoiceNo (as you already knew), but managed to come up with a way to retain the old ones for “search” purposes. The AutoNumber, on the other hand, is still an issue that Pat is working with me on (I think). Nevertheless, I do want you to know that there was no intent in my bumbling etiquette; just me being incoherent again.

You and Pat seem to be at odds as to how to go about doing this. . .so. . .uh. . .well. . .geesh!

Reply | Quote

>>The control txtProNo is on the form, or should be, and it’s source should probably be InvoiceNo.
I really meant ProNo not txtProNo, sorry about that.

Reply | Quote

OH! Well why didn’t you just say so.
Removed “txt” from the “ProNo” code and all is well. What a great solution!

Thanks so much for hanging in there with me Pat! Your patience is most admirable. Not only have you provided me with a great solution, but you also lead me through some area’s I’ve never been . Great experiance!

Reply | Quote

No problem. Sometime in the frenzy of messages, it is hard to remember who said what to whom, and to whom you did or did not reply to!

Reply | Quote

You are right if Invoice number is a unique key, however if it’s not then the problem still exists.

Reply | Quote