Auto Number in Access Form

[jrd3476]

Hi!

I need to auto generate a number based on items chosen from my 2 combo boxes plus the sequence count of each item. So for example
Box 1: choose your color R, B, G, Y.
Box 2: Choose day of week: M, T, W, T

So far I've created a new field that concatenates the 2 boxes so If I choose Red and monday the output is: RM, but I need to add a counter. So if it's the first Red and Monday it should say RM1, then if I choose Red and Thursday it should say RT1, if I then choose Red and Monday it should say RM2...Green and Thursday is should say GT1 and so on.
Thanks,
Julia

 

[Remou]

It should be possible to use DCount:

Counter=DCount("*","TableHere","Left(ID,2)='" & IDCode & "'")+1

 

[fneily]

I've created a new field.." Are you storing this concatenation in a table? This is a calculated field and shouldn't be stored in a table. You should have three fields - Color, DayOfWeek, SequenceNo
You'd then concatenate them for a form or report.

 

[jrd3476]

Fneily,
I do have three fields in the table. Where do I store the concatenated field if not in the table?
Thanks!

 

[randy700]

You should not store the concatenated field anywhere. This amounts to duplicated data (all parts of the concatenation are all ready stored as distinct fields).

The concatenation should be used for display purposes only.


Randy

 

[jrd3476]

Hi Randy,

I need the new concatenated code to be stored as we will use it for reference and need to be able to lookup later.
Thanks,
Julia

 

[randy700]

Julia,

The data is all ready stored in your table. Take a look here.
Fundamentals of Relational Database Design


Randy

 

[jrd3476]

Thanks Randy,
I read through the link you sent along, and I think I've not described my problem correctly.

I need to create a unique code for a monthly promotions for 5 products. Each product has a unique delivery. I need the user to create their own code based on selecting the product from a drop down list, then the delivery channel from a drop down list, add count to occurance, then based on those inputs, show the user the code. The code will be added to all collateral for that promotion. If someone needs to then refer back to the code they should be able to look it up on the table to see what made up the code. If I don't store the concatenated field, how will they find out the details?
Drop Down box 1 Pants Drop down box 2irect Mail Occurance
Shirts e-mail
Shoes phone
Hats Web
Sweaters
Promo code: PD1
Thanks,
Julia

 

[aperez54]

I think the '3rd' field which is the numeric code should be the autonumber field on your table so it's auto generated.

Then you can show on the form or report the concatenation (?) of all 3 fields.