access requery/refresh/dirty

How is the number given right now? As the default value of the field? If so, it is not up to date and may generate duplicates, and it is not saved in the database until the user either changes record or closes the form.

I would calculate it in the after_update event of the date field, then issue a "docmd.runcommand accmdsaverecord" to ensure it is saved in the database immediately. Be sure to add some error checking for duplicates anyways, because it could happen that another user adds a record between the time you assigne the value to the invoice ID and the save record.

Hope it helps,
Mike

Interesting as I have calculated it in the Unique ID field.

I have been struggling for some error code ----could you think of any?

Thanks,.

This would be assuming that the UniqueInvoiceID is indexed to allow no duplicates and that there are no other required fields, it's not the most elegant way, and we should probably add some maximum number of tries to ensure it never goes into and endless loop, but it should work :

Private sub DateOfInvoice_AfterUpdate
On Error GoTo Err_DateOfInvoice_AfterUpdate

	If nz(UniqueInvoiceID,"") = "" Then
		
GenerateNewID:
		Me.UniqueInvoiceID = DMAX("UniqueInvoiceID","TableName")
		Docmd.RunCommand acCmdSaveRecord

	End If

Exit_DateOfInvoice_AfterUpdate:
	Exit sub

Err_DateOfInvoice_AfterUpdate:
	If err.Number = 3022 Then  'Error returned when the value already exists
		Resume GenerateNewID
	else
		msgbox err.number & " - " & err.description
		Resume Exit_DateOfInvoice_AfterUpdate
	end if

End sub

Select allOpen in new window

Thanks, looks promising. Will test and return.

Oops, just noticed I didn't add a +1 to the DMax in that code example, be sure to add it or it's a definite endless loop hehe :

DMAX("UniqueInvoiceID","TableName")+1

fyed,

Ive got that thanks. It works.

I was just looking for a way to update the new DMAX filed without logging out.