auto-number field as basis of second auto-number field

  • Thread starter Thread starter ron
  • Start date Start date
R

ron

I have a form and a subform that have as their record
sources queries based on TblInvoice and TblInvoiceDetail
respectively. These two tables are linked by an auto-
number field [InvoiceNumber] that is the primary key in
TblInvoice and a foreign key in TblInvoiceDetail.
TblInvoiceDetail has its own auto-number primary key field
[DetailNumber]. I am wondering if there's a simple (or
not) way to make the auto-number field [DetailNumber]
generate a value based on the number generated in field
[InvoiceNumber]. Thus:

If
[InvoiceNumber] = 16
then the linked records would be
[DetailNumber] = 16-01
[DetailNumber] = 16-02
[DetailNumber] = 16-03

Doable?

Thanks,
ron
 
Ron

Not in Access, as far as I know. An Autonumber is a system-generated,
(generally) unique value used to identify a row. They aren't fit for human
consumption, which I'm inferring from your post that you are trying to do.

If you want to know the tblInvoice row to which a tblInvoiceDetail row
"belongs", you've already achieved that by using the tblInvoice!InvoiceID
value as a foreign key in the Detail table. If you want to generate a
human-readable Invoice Number, you'll need to create your own routine (not
too hard). Check Google.com for "custom autonumber" for ideas.

Good luck

Jeff Boyce
<Access MVP>
 
I am wondering if there's a simple (or
not) way to make the auto-number field [DetailNumber]
generate a value based on the number generated in field
[InvoiceNumber]. Thus:
Click to expand...

Easy, but you need two fields to make up the Detail primary key: the FK
InvoiceNumber field and a SerialNumber that counts each detail.

You can allocate the SerialNumber in the BeforeUpdate event of the form
(presumably the subform) with just two lines of code, something like

' don't reallocate it if it's already filled in!
If Not IsNull(Me!txtSerialNumber) Then Exit Sub

' look up the current highest serial for the current invoice and
' add one.
' If it's the first one, then DMax will return NULL so you must
' wrap the function in a NZ function to convert it.
'
' The Me.Parent.Parent is a guess as to how to see the main form,
' assuming that Me is the subform, me.parent is the subform control
' and me.parent.parent is the main form. But I could be wrong!
'
Me!txtSerialNumber = 1 + NZ( _
DMax("SerialNumber", "OrderDetails", _
"InvoiceNumber = " & Me.Parent.Parent!txtInvoiceNumber), _
0)



Hope that helps


Tim F
 
Back
Top