Saving a Record 1

[integritycare]

Hi Guys,

Need some ideas for this please
In the work database an invoice is raised by a form. The invoice number is recorded;

Private Sub Form_Current()
Me.InvoiceNumber = Nz(DMax("InvoiceNumber", "Accounts08"), 0) + 1
End Sub

The issue is when one person is invoicing then all is well. But we need to have two people invoicing at the same time.
I have noticed that the invoice number is only saved to the table when the invoice is completed and the user 'saves' the invoice.

The command on the Save Button is this;

Private Sub Record_Click()
Access.Application.DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Sub

How can I get the invoice number to be saved when the invoice form is opened. This way 2 users can input accounts to two separate invoices.

Many thanks,

Integrity

 

[missinglinq]

This is normally done by placing the number generating code in the Form_BeforeUpdate event. This means that it is generated at the last second before the Record is saved. Using this method, for over a decade, I've never seen an auto-incremented number like this duplicated.

But you also have a major flaw in your code

Me.InvoiceNumber = Nz(DMax("InvoiceNumber", "Accounts08"), 0) + 1

This code, alone, will change your InvoiceNumber every time the Record is accessed/saved! You need to qualify it so that it only applies on a New Record!

If Me.NewRecord Then
  Me.InvoiceNumber = Nz(DMax("InvoiceNumber", "Accounts08"), 0) + 1
End If

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[integritycare]

Hi Missinglinq,
Many thanks for your reply. I have done as you suggested. So I opened a new invoice, did not enter any data and it showed the next invoice number in this case 57500 in the table. I noticed that the invoice number did not show on the invoice form when it opened.

I then proceeded to enter some dummy data and then the invoice number showed on the form, but it showed the next consecutive number. So that invoice registered as 57501 both on the form and in the table.

Any thought on how to fix this issue?

The invoice form code is as follows;

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec

'Turn the MouseWheel Off
Dim blRet As Boolean
blRet = MouseWheelOFF
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  Me.InvoiceNumber = Nz(DMax("InvoiceNumber", "Accounts08"), 0) + 1

End If
End Sub

Many thanks
Integrity

 

[missinglinq]

My guess would be that you did not remove the code from the Form_Current event. Can't think of any other reason, assuming that you entered no data. Barring that, I'd recheck all that you've reported, here. It really makes little sense; the code in the Form_BeforeUpdate won't execute, and the Record won't be saved to the Table, until some other data has been entered into the Record. And once the Form_BeforeUpdate event has fired, and the Record saved, it is no longer a New Record, and the code will not execute again. This is really bolierplate code, having been used, successfully, thousands of times.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[integritycare]

Hi Missinglinq,
Again thanks for your reply.
Yes your code does work, but my question is this; Say one person opens a new invoice and then another person opens a new invoice both within seconds before any one enters data would they both have the same invoice number or would they be consecutive numbers. ( I have not tried this live as yet.)

Is my next step to go live on each desktop and see what happens?

Regards,
Integrity

 

[missinglinq]

As I said in my original post, "Using this method, for over a decade, I've never seen an auto-incremented number like this duplicated!"

You're talking about two users completing/saving their Records at the exact, same nano-second, and while it's theoretically possible, I've never seen it happen!

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[integritycare]

Hi Missinglinq,

Thanks for your reply. I shall update all forms and give it ago..

Integrity

 

[lionelhill]

If you're really worried about it, set the field as a unique key and then it will give an error if the two invoice-entering people manage to synchronise themselves with that nanosecond accuracy.

If you're really over-cautious (as I am, but that's a personal failing) then you can add a useful error-handler that either retries for a new number, or tells the invoice-typer what happened.

I've seen people just use AutoNumbers for Invoice Numbers, which has the advantage that you don't need any code and you're guaranteed to get unique numbers. So far I've never seen Access issue AutoNumbers out of order (although there's no guarantee it will always work that way), but under some circumstances you can miss a number out.