Unique Record ID

[Guest]

I am creating a field on my form that will automatically create a unique ID
number for a record. My Control Source says...

=[Subsidiary] & [Location] & [SpecID] & Format([AuditDate],"mmddyyyy")

now here's my problem... once i changed the control source it no longer will
be saved in the table....

Anyone have a fix or advice?

 

[Guest]

If there issome event that happens like a new record created, you could put:

Private Sub SomeEvent()

[UniqueID] = [Subsidiary] & [Location] & [SpecID] &
Format([AuditDate],"mmddyyyy") .

End Sub

Then you keep the control source as [UniqueID]

 

[Guest]

I entered the code you recommended "Private Sub SomeEvent()" and changed
SomeEvent() to NewRecord().... then I put the control source back to
UniqueID. This didnt work for me. Perhaps I more ignorant in access than I
like to admit.

The fields [Subsidiary] & [Location] & [SpecID] & [AuditDate] are manually
filled out on a form by the user. "UniqueID" wont be filled out until the
above are filled out. Then Unique ID is stored

If there issome event that happens like a new record created, you could put:

Private Sub SomeEvent()

[UniqueID] = [Subsidiary] & [Location] & [SpecID] &
Format([AuditDate],"mmddyyyy") .

End Sub

Then you keep the control source as [UniqueID]

I am creating a field on my form that will automatically create a unique ID
number for a record. My Control Source says...

=[Subsidiary] & [Location] & [SpecID] & Format([AuditDate],"mmddyyyy")

now here's my problem... once i changed the control source it no longer will
be saved in the table....

Anyone have a fix or advice?

 

[Guest]

I entered the code you recommended "Private Sub SomeEvent()" and changed
SomeEvent() to NewRecord().... then I put the control source back to
UniqueID. This didnt work for me. Perhaps I more ignorant in access than I
like to admit.

The fields [Subsidiary] & [Location] & [SpecID] & [AuditDate] are manually
filled out on a form by the user. "UniqueID" wont be filled out until the
above are filled out. Then Unique ID is stored

If there issome event that happens like a new record created, you could put:

Private Sub SomeEvent()

[UniqueID] = [Subsidiary] & [Location] & [SpecID] &
Format([AuditDate],"mmddyyyy") .

End Sub

Then you keep the control source as [UniqueID]

I am creating a field on my form that will automatically create a unique ID
number for a record. My Control Source says...

=[Subsidiary] & [Location] & [SpecID] & Format([AuditDate],"mmddyyyy")

now here's my problem... once i changed the control source it no longer will
be saved in the table....

Anyone have a fix or advice?

 

[Bob Quintal]

I entered the code you recommended "Private Sub SomeEvent()"
and changed SomeEvent() to NewRecord().... then I put the
control source back to UniqueID. This didnt work for me.
Perhaps I more ignorant in access than I like to admit.

The fields [Subsidiary] & [Location] & [SpecID] & [AuditDate]
are manually filled out on a form by the user. "UniqueID" wont
be filled out until the above are filled out. Then Unique ID
is stored

You would need to use the AfterUpdate Event of the last of those
values to be entered, or better yet, all four, but you'd need to
check that each of them had some value entered.

If isnull(me.Subsidiary) OR isnull(me.Location) _
OR isnull(me.SpecID) OR isnull(me.AuditDate) then
'Do nothing
Else
[UniqueID] = [Subsidiary] & [Location] & _
[SpecID] & Format([AuditDate],"mmddyyyy") .
End If

If there issome event that happens like a new record created,
you could put:

Private Sub SomeEvent()

[UniqueID] = [Subsidiary] & [Location] & [SpecID] &
Format([AuditDate],"mmddyyyy") .

End Sub

Then you keep the control source as [UniqueID]

I am creating a field on my form that will automatically
create a unique ID number for a record. My Control Source
says...

=[Subsidiary] & [Location] & [SpecID] &
Format([AuditDate],"mmddyyyy")

now here's my problem... once i changed the control source
it no longer will be saved in the table....

Anyone have a fix or advice?

 

[Guest]

Rename the text box with the formula as "txtUniqueID". Then, in the form's
BeforeUpdate event, do this:

Private Sub Form_BeforeUpdate()

If Me.NewRecord Then Me.UniqueID.Value = Me.txtUniqueID.Value

End Sub

You can also do this:

Private Sub Form_BeforeInsert()

Me.UniqueID.Value = Me.txtUniqueID.Value

End Sub

Both of these procedures put a value directly into the record, bypassing a
bound control on the form. I prefer BeforeInsert, but BeforeUpdate works too
if you get it right. It's important that you rename the text box so
VBA/Access does not get confused about whether you are trying to talk to a
control or a recordset field. The default is to talk to a control, and you
don't want to do that here.

You could also just put the forumla you want to use on the right side of the
equation in each example.