Sequential Invoice Number Multiple Tables

[Neruda]

Hi,
I have 3 tables from which 3 different forms create 3 different types of invoices. There are only 2 common fields to all tables(customer, employees).
I would like to add a sequential Invoice number so that no matter what form is used, the invoice number always goes up by 1. The forms create a new record using an insert query.
Would I have to create a fourth table that contains a PK field so that I can use the Dmax+1?

Thanks

[NeoPa]

Sounds like an overly-complicated setup but I don't know the situation it has to match.

Are you saying you're storing the invoices in these three separate tables? If so you'll find life difficult. There are nearly always ways around bad design but the worse the design the more you'll need to use them and the more fragile will be your project.

If you really, really need to create a sequential number between three different tables then it can be done. I could help you with it if I had some reliable information as to exactly what it is you're talking about. You will need to access all three tables and you'll need to take advantage of the format of any invoice numbers stored within. As I have no such knowledge from your question that's as far as I can go at this point.

Please remember in future questions that everyone saves time and energy if you include all relevant information in your original question.

[Neruda]

Hi,
I am storing Invoices in 3 different tables, yes.
I would go with your intuition that it might be a case of bad design. My end goal is to have this sequential invoice number across 3 tables, I thought about having just one but could not find a way. The tables have mostly unrelated fields.
I have attached a screenshot. They all create a sale receipt using different forms.
I suppose when it comes to retrieve record from a specific date I would have to use a union query from 3 tables?
I might abandon the idea if it gets too messy and have their own Invoice number, maybe with a prefix to distinguish them.

Thanks

[NeoPa]

Forget intuition for this (Not generally. It's very important.) as this comes from experience.

Looking at your table designs informs me that you should avoid multiple problems by merging your tables together into a single one. An extra type field would determine which type of invoice it is and any fields that are unused for that type can simply be left as Nulls. The work to create a PK value, even assuming you couldn't use AutoNumber, would be much simpler, as would much more of the work surrounding any work with your invoices. This looks like a no-brainer.

[Neruda]

Hi,
when you say an extra type field do you mean just a field (in each table)with say 1 as default value for one table, 2 for the second and 3 for the third so as to distinguish them?

Will I be able to use an union query from 3 tables to pull records from a particular day (using same number of fields)?

I read about dmax+1, should I bother in this case?

Thanks

[NeoPa]

If you were to use three separate tables then the type field wouldn't be necessary, but if you used a single table, with the type field just as you describe it, then everything would work more smoothly. You wouldn't even need to worry about UNION queries (which by the way in Jet or ACE SQL are necessarily non-updatable).

To see or process the invoices from any of the particular states, for which previously you would have a separate table, you simply filter using that [Type] field using whichever value matches that type.

[NeoPa]

Neruda:
I read about dmax+1, should I bother in this case?

Let's get to where we know what you're actually working with before we answer that one. Hopefully it won't be necessary but it will depend on understanding how you're storing your data. Information we don't yet have.

[Neruda]

to have just one larger table is something I had not considered! because of all the null entry. I thought leaving many nulls would not be a good idea. But I suppose it would simplify things a lot. It doesn't really matter does it?
I think i'll do just that, each record will have a type field in one larger table.

[NeoPa]

That's part of the question Neruda. How about your ID field. Is it a simple number or is it a formatted string that people sometimes like in order to show some information?

If it's a simple number then an AutoNumber field will work perfectly well and Access will handle setting that for you automatically. If it's the latter then you may need to use the DMax() approach, but we'll need to know how it's formatted if you'd like us to help with that.

[Neruda]

Hi,
this id field is a number with a two letter prefix. Ideally I would like to have the ability to change the prefix. I could store the prefix in a table I already have, i suppose.

About the forms:
form 1 uses an insert query for enterering data
forms 2 & 3 use a form and records can be aborted

there will never be more than person using the app.

The Invoice ID would look like WE0000001 or KW0000001 and should not miss numbers. I would like the prefix to be changable from a standard form, without opening the app.

Thanks

[NeoPa]

You still give very little clue as to what you're really talking about. If you want multiple possible prefixes then that should surely be included in your question. It's no fun running around trying to guess what it is you want after asking you to explain it properly.

Also, talking of forms makes absolutely no sense unless you explain why. Forms don't contain data and your question seems to imply you get the information from a form. That simply doesn't make any sense.

What is it you're trying to achieve? Are there different two-letter codes as prefixes? If there are then does the numeric part depend on which is used or is it sequential regardless which code is used? How should the actual two-letter code be determined?

[Neruda]

I would like to have a sequential Invoice number field in a table (in the title).
The Invoice Number should look like this:
a 2 letter prefix + 7 sequential digits (eg WE0000001)
The user will determine which prefix he wants to use. Can be any 2 letters. The user will edit the prefix using a form. The default value is DF0000001. The numeric part increases regardless of the letters chosen .

[NeoPa]

OK. It seems like we're close enough to a clear question to put out some help here that will actually help. Before we start I'll explain the references so you can match them to your own situation.
My suggestions will assume you have an unbound Control on the current Form (The one the code will be running from.) called [cboPrefix], which will hold the two-letter prefix. Also that the same Form has a TextBox Control called [txtInvoiceNo] that will be used to represent the field [InvoiceNo] and that this will be set whenever [cboPrefix] is updated. It will also assume that the table we're dealing with is called [tblInvoice].

There are two ways to handle this :

1. This one works with an Access table but, due to an AutoNumber in ODBC-linked tables being created only once the record has been created, not with other types. This solution relies on a separate field (We'll call it [AutoNo]) in [tblInvoice] being set as an AutoNumber. Another caveat is that it is possible in this scenario to create gaps in the sequence by starting to create a record but never saving it. It is always possible to create gaps in any type of system by deleting records from earlier in the sequence after subsequent ones have already been created, but this can be avoided if you disallow such deletions. So, this option should only be taken if you don't care unduly about gaps.
   
   On to the solution itself. We will need an extra TextBox bound to [AutoNo] which we'll call [txtAutoNo]. The code would be something like :
   

   Private Sub cboPrefix_AfterUpdate()
       With Me
           .txtInvoiceNo.DefaultValue = "='" & .cboPrefix _
                                      & Format(.AutoNo, "0000000") & "'"
       End With
   End Sub

   This won't, in itself, cause the new number to be used up, but if any changes are made to the record then the new number is used up and unavailable for future use even if the record isn't saved.
2. This is a safer approach, although obviously a little more complex. Even with this one though, gaps can be created in the sequence number if you delete records after subsequent records have been created. If that's important then don't allow any such deletions. You can have some form of Deleted indicator that means it will be ignored by the rest of the system even though it remains in the table.
   
   This approach relies on scanning the table for existing values and using the one with the highest numerical value to build your next one from.
   

   Private Sub cboPrefix_AfterUpdate()
       Dim lngSeq As Long
       Dim strDefaultVal As String
    
       With Me
           lngSeq = Nz(DMax(Expr:="Val(Mid([InvoiceNo],3,99))" _
                          , Domain:="[tblInvoice]"), 0) + 1
           strDefaultVal = Replace("='%P%N'", "%N", Format(lngSeq, "0000000"))
           strDefaultVal = Replace(strDefaultVal, "%P", .cboPrefix)
           .txtInvoiceNo.DefaultValue = strDefaultVal
       End With
   End Sub

Neither solution handles numbers extending beyond the seven digits. In both cases the resultant string would include the full extended number.