Assign One of Set of 10 Values to Specific Records as Created

[Limeran]

MSAccess 2003 Assign One of Set of 10 Values to Specific Records as Created

I need to automatically assign values to records as they are created. I use the Autonumber feature in this table. For example:

Number Group New Value
1 Cars Becky
2 Cars John
3 Homes April
4 Homes Jane
5 Cars Sam
6 Cars Becky
7 Cars John
8 Cars April
9 Homes Jane
10 Homes Sam

I am new at using VBA and am not sure where to start with this. The records will be selected from the Records table using a query to exclude the records that do not need the New Values assigned to the Groups. The New Values will always use the same order.

Thank you,
Lisa

 

[ted.martin]

Where is this new number coming from. Maybe you could use an update query after the inital add event.

 

[Limeran]

Where is this new number coming from. Maybe you could use an update query after the inital add event.

The numbers are generated by the Autonumber field option. The New Value column is what I need access to assign using Becky, John, April, Jane and Sam. Once it assigns Becky, I need the next record to be assigned to John and so on until it has to go back to Becky.

Thank you,
Lisa

 

[Alisa]

Maybe if you explain a little more about what you are trying to do, you will get more suggestions. For instance, it is difficult for me to grasp why you would need to keep assigning the same names over and over again.

 

[Limeran]

I have ten users who all precert medical procedures. The precerts are entered into my database from a variety of sources that are not the users. The precerts need to be distributed to the users equally. The precerts are entered into the database all day long and need to be assigned as they are received. We want Acccess to do this automatically.

Alisa,
Thanks for the suggestion.
Lisa

 

[Alisa]

That sounds like a poor design - one of the first rules in a relational database is that you shouldn't store data redundantly (like peoples names). You can search for normalization on this forum to read a lot more about it. Do you have any leeway to set up the tables properly?

 

[Limeran]

I can change the tables anyway that is needed. I just used names because I thought would be easier to explain what I needed.

Thanks for your help,
Lisa

 

[Alisa]

I can change the tables anyway that is needed. I just used names because I thought would be easier to explain what I needed.

Thanks for your help,
Lisa

Oh, so are you actually inserting key values, not names?

 

[Limeran]

That is correct.

 

[Alisa]

ok, that makes more sense now. So what is the mechanism for adding the new records - is this done through a form or . . .

 

[Limeran]

New records are mainly added using a form. In the future some of it will be added directly to the tables.

 

[Alisa]

New records are mainly added using a form. In the future some of it will be added directly to the tables.

Well, you are going to have to isolate the event that adds a new record - ted martins suggestion above might work.

In any case, once you have found the correct event, you need to add the code to insert the value. That will be a currentdb.execute (UPDATE . . .) statement to update the value of that particular field in the table. Or, if the value shows up on the form, you could also set the value of the control on the form.

As far as figuring out WHICH value to use, you could add a field (if there isn't one already) to the names table called NameOrder. You can't use the autonumber field for this because an autonumber isn't guarenteed to be in order or not to skip. Then you could get the NameOrder of the last name that was used, then lookup the key value of nameorder +1 in the names table.