ID field by function and edit mode

[DevAccess]

Hello

I have one field called digitalID which is i am generating using Max function in VBA when New record is being created and i set that field to plain text to implement is that now I am saving the record so it got saved with 64 as it was the latest record or insertion point where I can create new record.

But when i am opening any existing records using combobx it shows me digitalID as blank becuase I am generating ID when new record gets created in this case how I can use field control source to point to opened existing record.

 

[Ranman256]

You don't get the new ID until you save the record.

 

[sneuberg]

It sounds like you have the control source of the combo box set to something like:

=Max(.....

but you really want it to be bound to digitalID in the form's record source. If this is the case you need to move the Max or DMax function somewhere else. I'd try the On Current event first, test for a new record and if it's a new record then set the combo box to value from a DMax function. Something like:

Private Sub Form_Current()

If Me.NewRecord Then
    Me.ComboBoxName = DMax("[digitalID]", "[The Table Name]")
End If

End Sub

 

[Galaxiom]

I'd try the On Current event first, test for a new record and if it's a new record then set the combo box to value from a DMax function. Something like:

Private Sub Form_Current()

If Me.NewRecord Then
    Me.ComboBoxName = DMax("[digitalID]", "[The Table Name]")
End If

End Sub

That would have the same effect as using the Form_BeforeInsert Event without the NewReocrd condition.

 

[sneuberg]

That would have the same effect as using the Form_BeforeInsert Event without the NewReocrd condition.

Good tip. Thanks

 

[Galaxiom]

BTW. It is essential to understand the importance of the moment of saving. If the record is not saved immediately after insertion, another user will get the same ID allocated to new records.

 

[sneuberg]

BTW. It is essential to understand the importance of the moment of saving. If the record is not saved immediately after insertion, another user will get the same ID allocated to new records.

I believe that unless transactions are used that is possible in a multiuser environment even if the record is saved immediately. Is this not true?

I would suggest putting a unique index on the field and if that rare duplicate occurs put a "try again" in the error handling for it.

 

[Galaxiom]

Yes, using DMax there is nothing to stop another user getting the same number if they query before that save but it isn't usually a problem.

The problem is that saving immediately causes the number to be used in vain if the record is then abandoned and the record deleted. If another record has been entered the numbers have moved on. The alternative is to allocate the number immediately before the record is saved using the Form_BeforeUpdate event.

 

[DevAccess]

It sounds like you have the control source of the combo box set to something like:

=Max(.....

but you really want it to be bound to digitalID in the form's record source. If this is the case you need to move the Max or DMax function somewhere else. I'd try the On Current event first, test for a new record and if it's a new record then set the combo box to value from a DMax function. Something like:

Private Sub Form_Current()

If Me.NewRecord Then
    Me.ComboBoxName = DMax("[digitalID]", "[The Table Name]")
End If

End Sub

what if it is new record and from combobox I am selecting existing value and I want to populate same field with existing record ID

 

[DevAccess]

It sounds like you have the control source of the combo box set to something like:

=Max(.....

but you really want it to be bound to digitalID in the form's record source. If this is the case you need to move the Max or DMax function somewhere else. I'd try the On Current event first, test for a new record and if it's a new record then set the combo box to value from a DMax function. Something like:

Private Sub Form_Current()

If Me.NewRecord Then
    Me.ComboBoxName = DMax("[digitalID]", "[The Table Name]")
End If

End Sub

I want field for two purpose

1 ) when it is new record it should be filled up with VBA and once it saved and when I opening same record it should show me existing ID which was created when I had created the record initially using function.

 

[sneuberg]

I don't understand why you would want to

populate same field with existing record ID

Do you want to add records with duplicate digitalIDs? Or do you want to use the combo box to navigate to an existing record?

 

[sneuberg]

I want field for two purpose

1 ) when it is new record it should be filled up with VBA and once it saved and when I opening same record it should show me existing ID which was created when I had created the record initially using function.

If you changed the control source of the combo box to the field it should be working that way.

 

[DevAccess]

I don't understand why you would want to



Do you want to add records with duplicate digitalIDs? Or do you want to use the combo box to navigate to an existing record?

Same form is being used to create new record and to display existing record.

When I create new record it should take I'd which I created and when I open existing record it show me data from record source.

 

[sneuberg]

Can you upload the database?