SQL query in Access-97 VBA

  • Thread starter Thread starter Geronimo
  • Start date Start date
G

Geronimo

Hi

Problem:
I have a invoicetable and a form for this. InvoiceNr is autonumber and the
primary key. There are several peoples using it. The problem is when making
new invoices. If two, or more, make a new invoice at the same time, it uses
the same number and this don't work. Is there any way to make a query in the
vba so that the form can autosign the next available number?

//Thx
MK
 
It doesn't sound like AutoNumber.

This is normally the behaviour of the method using the DMax on the Invoice
Table.

Is this a database you wrote? If it is not, you will need to post the
method you used to create the sequence of custom [InvoiceNr]s.

The safer way for a multi-user database is to use a one-Record Table
"tblNextInvoiceNo" to store the next available InvoiceNo. In the
BeforeUpdate Event of the Invoice Form, lock this Table tblNextInvoiceNo
(using a Recordset), retrieve the number for the new Invoice, use code to
update the Record in this Table tblNextInvoiceNo and then unlock this Table.
This way, each number is allocated only ONCE just before the Record is
appended into the Invoice Table.
 
Hi



You where right, there were no auto number.

No. It's not my db. I'm just trying to help a college.

The next invoicenr have, until now, been a manual process.



Is it possible/safe to use the next invoiceID in the t_invoice table?



If Me![invoiceID] = Nz(0) Then
'If not a manually invoicenr are enterd
Me![invoiceID] = DMax("[invoiceID]", "[t_invoice]") + 1 'Get the
last invoiceID and add 1
Me.Caption = invoiceID & " - " & Klientnr.Column(1) 'Just
info to user
Me.Recalc
' don't know if this is needed
End If



This code could possible be used when someone click the make invoice button.



Thx

MK

In the befor update event in the invoice form
Van T. Dinh said:
It doesn't sound like AutoNumber.

This is normally the behaviour of the method using the DMax on the Invoice
Table.

Is this a database you wrote? If it is not, you will need to post the
method you used to create the sequence of custom [InvoiceNr]s.

The safer way for a multi-user database is to use a one-Record Table
"tblNextInvoiceNo" to store the next available InvoiceNo. In the
BeforeUpdate Event of the Invoice Form, lock this Table tblNextInvoiceNo
(using a Recordset), retrieve the number for the new Invoice, use code to
update the Record in this Table tblNextInvoiceNo and then unlock this Table.
This way, each number is allocated only ONCE just before the Record is
appended into the Invoice Table.

--
HTH
Van T. Dinh
MVP (Access)



Geronimo said:
Hi

Problem:
I have a invoicetable and a form for this. InvoiceNr is autonumber and the
primary key. There are several peoples using it. The problem is when making
new invoices. If two, or more, make a new invoice at the same time, it uses
the same number and this don't work. Is there any way to make a query in the
vba so that the form can autosign the next available number?

//Thx
MK
Click to expand...
Click to expand...
 
If it is a single-use database, yes. If it is a multi-user database, no as
per your original post.

See my earlier reply about using the one-Record Table.
 
Back
Top