Auto Invoice No

  • Thread starter Thread starter monkey1
  • Start date Start date
M

monkey1

I have following tables

tblcoursepayment with autonumber invoice number field.
tblitemSalesPayment with autonumber invoice number field.

how can I generate a central sequence invoice number for both table?

or any other suggestion to avoid duplicate invoice number?
 
monkey1 said:
I have following tables

tblcoursepayment with autonumber invoice number field.
tblitemSalesPayment with autonumber invoice number field.

how can I generate a central sequence invoice number for both table?

or any other suggestion to avoid duplicate invoice number?
Click to expand...

Are these tables related or are they unrelated and you just don't want
duplicates across two un-related tables? If they are related then of the fields
actually participating in the relationship only one table can use an AutoNumber.
The other side should just be a LongInteger and its value will be determined by
the table using an AutoNumber. This is most easily handled in a form with a
subform. The table in the main form has the AutoNumber and that value is
automatically pushed into any records created in the subform.
 
thanks Rick,

2 tbls not related, but want to share 1 autonumber, dont want duplicate
invoice number across two un-related table.

Thank in advance your help.

Rick said:
I have following tables
Click to expand...
[quoted text clipped - 4 lines]
or any other suggestion to avoid duplicate invoice number?
Click to expand...

Are these tables related or are they unrelated and you just don't want
duplicates across two un-related tables? If they are related then of the fields
actually participating in the relationship only one table can use an AutoNumber.
The other side should just be a LongInteger and its value will be determined by
the table using an AutoNumber. This is most easily handled in a form with a
subform. The table in the main form has the AutoNumber and that value is
automatically pushed into any records created in the subform.
Click to expand...
 
monkey1 said:
thanks Rick,

2 tbls not related, but want to share 1 autonumber, dont want
duplicate invoice number across two un-related table.
Click to expand...

Then you cannot use AutoNumber for either of them. You can use a single row,
single field table that holds a seed value. Then when creating records you...

Get value from seed table and put an exclusive lock on it.
Use the seed value to assign your invoice number.
Add 1 to the value in the seed table.
Release the exclusive lock on the seed table.

You would do all of this in the BeforeUpdate event of the forms used to create
new records.
 
thank you, got it.

I m newbie in vba, would you give me some example vba code?
 
Back
Top