Cannot believe this is so difficult (Autonumber)

[WildlyHarry]

I am trying to create a database that uses autonumber in conjunction with two
combo boxes on a form to create an Unique ID for new records entered into a
table. My record source code for the unique id would look something like
this:

=[forms]![form1]![combo1] & "-" & (auto number field) & "-" &
[forms]![form1]![combo2]

I want this code to be stored in my table as a unique ID. But there is no
way to save this info to a field in my main table. Any ideas? Thanks in
advance for the help.

 

[Douglas J. Steele]

Store it as three separate fields in the table. A primary key can contain up
to 10 separate fields.

 

[WildlyHarry]

Interesting. But how would I search for a record based on the case id?

Store it as three separate fields in the table. A primary key can contain up
to 10 separate fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to create a database that uses autonumber in conjunction with
two
combo boxes on a form to create an Unique ID for new records entered into
a
table. My record source code for the unique id would look something like
this:

=[forms]![form1]![combo1] & "-" & (auto number field) & "-" &
[forms]![form1]![combo2]

I want this code to be stored in my table as a unique ID. But there is no
way to save this info to a field in my main table. Any ideas? Thanks in
advance for the help.

 

[Douglas J. Steele]

You'd either set criteria for all three fields, or you'd add a computed
field to your query that concatenates the 3 fields, and search on the
computed field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Interesting. But how would I search for a record based on the case id?

Store it as three separate fields in the table. A primary key can contain
up
to 10 separate fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to create a database that uses autonumber in conjunction
with
two
combo boxes on a form to create an Unique ID for new records entered
into
a
table. My record source code for the unique id would look something
like
this:

=[forms]![form1]![combo1] & "-" & (auto number field) & "-" &
[forms]![form1]![combo2]

I want this code to be stored in my table as a unique ID. But there is
no
way to save this info to a field in my main table. Any ideas? Thanks
in
advance for the help.

 

[UpRider]

I think you can save the combination as a primary key. Add a text field to
your table and call it MyPK. Put a command button on your form, call it
cmdOK and add this event procedure: (air code)
sub cmdOK_Click()
if not me.newrecord then exit sub
if isnull([combo1]) then exit sub 'add code to notify user
if isnull([combo2]) then exit sub 'add code to notify user
[MyPK] = [forms]![form1]![combo1] & "-" & (auto number field) & "-" & _
[forms]![form1]![combo2]
RunCommand acCmdSaveRecord
end sub
This code will prevent the PK from being changed after the record is
created.

HTH, UpRider

 

[Douglas J. Steele]

While you can do that, it's wrong! Fields should be atomic: they should not
contain 3 separate pieces of information.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I think you can save the combination as a primary key. Add a text field to
your table and call it MyPK. Put a command button on your form, call it
cmdOK and add this event procedure: (air code)
sub cmdOK_Click()
if not me.newrecord then exit sub
if isnull([combo1]) then exit sub 'add code to notify user
if isnull([combo2]) then exit sub 'add code to notify user
[MyPK] = [forms]![form1]![combo1] & "-" & (auto number field) & "-" & _
[forms]![form1]![combo2]
RunCommand acCmdSaveRecord
end sub
This code will prevent the PK from being changed after the record is
created.

HTH, UpRider

I am trying to create a database that uses autonumber in conjunction with
two
combo boxes on a form to create an Unique ID for new records entered into
a
table. My record source code for the unique id would look something like
this:

=[forms]![form1]![combo1] & "-" & (auto number field) & "-" &
[forms]![form1]![combo2]

I want this code to be stored in my table as a unique ID. But there is
no
way to save this info to a field in my main table. Any ideas? Thanks in
advance for the help.