Assign system ID if criteria is met

[SharonInGa]

How can I get a system generated ID for the BOL field - ONLY when certain
values are typed into the cboDestination field?


If Me.cboDestinations = "8781" Or Me.cboDestinations = "8936" then
Me.BOL_ = "SYSTEM GENERATED ID"
Else
Me.BOL_ = ""

 

[tina]

well, "system generated" generally means an Autonumber. in a table, the
Autonumber data type is principally used to generate a primary key value for
each record in the table. the value is assigned to the field by the system
whenever a new record is created in the table.

is that what you're after? or do you mean a "programmatically assigned" ID?
a programmatically assigned value is generated whenever, wherever, and
however the developer decides, by the running of code that the developer
writes for that purpose. to generate a programmatically assigned value, the
developer has to decide what to base the value on, perhaps how to increment
it, and how to handle multi-user conflict issues.

can you give us more information on what you're trying to do, and where, and
when? and why?

hth

 

[SharonInGa]

We have two customer destinations to track some inventory. One customer
gives us a product # to use on our form. The other customer allows us to
create our own product number. I have code that sets the product field value
to null when the 1st customer is selected --- so that we can add the number
the customer gives us. I need the system to add a number to the same prodcut
field when we select the 2nd customer. So, the null and "program generated
number" are competing for the same field called "BOL" . I need to know how
to code a program gerated number and stick it into the code below.

If Me.cboDestinations = "8781" Or Me.cboDestinations = "8936" then

 

[tina]

okay, then you need a programmatically generated value. you can use a simple
imcrementing code, such as

Nz(DMax("ProductField", "MyTable", "Destination = 8781 OR Destination =
8936"), 0) + 1

which searches all the records in the table with either of those two
destinations, finds the highest value in the product field, and then adds 1
to it.

if you have multiple users entering data in this table at the same time,
then you're going to have duplicate numbers generated at some point - almost
guaranteed. assuming that there should never be a duplicate value in this
field, the first thing to do is set the field's Indexed Property to No
Duplicates, at the table level. in the data entry form, set the code that
generates the ID in the form's BeforeUpdate event. you may still get a
"duplicate value" error at the form level, so you also need to trap that
error, and re-run the ID-generating code until the system succeeds in saving
the record with a unique ID.

btw, you should remove the

Else
Me.BOL_ = ""

from your code. the system may not consider multiple records with a
zero-length string value as a violation of the unique index, but it's still
generally not a good idea to store a zero-length string in a text field. a
zero-length string value is NOT the same as a Null value, but you can't tell
the difference with the naked eye, and you have to write queries and
expressions very precisely to make sure they handle the two different values
exactly the way you expect them to.

hth