Updating a primary key based on form entry

[Confused in access]

Hi,

I am confused on how to go about doing this. This is my scenario.
The form presents a combo box of different options say apple, cauliflower, rose etc.and other details to be filled in like purchase amount.



Now based on what the user selects in the form access should go and assign a product id to it. The product id is the primary key in this regard. Say the user selects apple. All fruits have the product id starting with 111. In that case access should go the table and assign that particular entry a product id 111xxx where xxx is a randomly generated number. Similarly all vegetable will have a prefix of 222. Please let me know how to go about doing this.

[Micron]

You already have a primary field called product id? If so, is it an autonumber field, or how is the entry created? Need more info on the table structure.

[Confused in access]

Well currently its not autonumber. So i have to fill in the numbers manually. However i have to ensure that each number for each transaction is unique. But i should also ensure that the first three digits will categorize which product associated with that transaction. For instance on the form, in the combo box if the user clicks vegetable and also enters other details then automatically the unique id assigned to that transaction would be 222xxx. Here 222 is the prefix for all vegetables. The remaining 3 digits I henceforth want them to be generated by the access by ensuring that it is not already been created. Since the database in already in existence for a few months now, it is too late to change the primary key format to auto number. Please help me on this

[Micron]

Ok, but I still haven't figured out if you want something to do this going forward or to update all your existing records, or both. The title of your post suggests the latter, the information you post suggests the former. You are also asking for a random number, which is something special, when maybe what you really want is a unique number to be appended to your prefix. One way to do this is with a random number, another would be the currrent time (expressed as a serial number) or yet another way would be the number of seconds elpased since a given date.

You can also add a primary field as autonumber to your table, which would immediately add all numbers required. Then you could update the product id field as 222 with the autonumber added at the end. However, your process thereafter would have to create the record first, then update the product id field after it is saved.

[Confused in access]

Hi Micron,

You have phrased it perfectly. Thank you so much. I want to do this going forward. I do not want to change all the records that have already been created. What i want is a unique number to be appended to the appropriate prefix. However since the tables already have existing numbers generated along with appropriate prefixes, i want to ensure that the new numbers generated through this method do not repeat the existing numbers. Ultimately this is a 9 digit number. The first 3 numbers are the prefix decided based on what the user has selected in the combo box. The next 6 are the generated unique numbers. Please let me know if you have any doubt. I cant tell you how important getting this done is for me. Thank you for helping me out here

[Micron]

OK, so if you need six unique digits, consider concatenating hour/minute/second to your prefix and trying to write this to the table on whatever form action was triggering it before. Trap for the Access "duplicate error" message and if it occurs, repeat the process until it passes. The only way this will fail is if at the exact same second of the same minute of the same hour of a day someone triggers the code for the exact same prefix. If you think this is likely, maybe you should entertain a larger suffix so that you can use the day part of the date (8 characters plus your prefix). Since the day part is repeated from month to month most of the time, this still does not guarantee the result would be unique the first time. However using either way the code would continue to pick values until it passes the test. Create a query that brings back some rows from one table field. The field or the data is not important, but a date field should be easy to put criteria on to limit the records. Just return a FEW records. Paste this into the next query grid column in design view: myVal: "222" & Format(Now(),"ddhhnnss") and run it. You will see how my suggestion will work. It matters not that it's repeated for as many rows as you return - we only need be concerned about the first record returned.

Can you post the code that creates your table entry the way it does now?

[Confused in access]

Is this to be done using vbcode??

[Micron]

This could end up double posted - I don't see my original reply.

Yes, that was my intent.