VBA code to pull data from a different table

[katzi4eva]

Hi
this may seem obvious and glaring me in the face but i can't see it.

I have a form I use for data entry, it needs to generate an id called RO Number and i need it be generated by access starting at number RO129036 and then keep adding sequentially, so RO129037 etc etc.

as i already have data in my DB that i need i cannot just reset any fields

someone suggested having a table with just one field - the numeric part of the RO number so first one would be 129036 -
so i need the form to pull this field, add 1, and then add "RO" at the beginning of it? i have really been struggling with this database, would someone be able to help point me in the right direction?

Thanks

 

[plog]

I agree with some of that advice. You do not need a new table for this, you simply use the correct data type in your existing table, retrieve the highest value and add one to it.

If all the "RO" numbers start with "RO", then you don't store the "RO" portion. In your main table you should have a numeric field which stores the numeric part of the RO number and that's it. Then, what you do with your form is you have code that generates your new RO number. That code would use a DMAX(http://www.techonthenet.com/access/functions/domain/dmax.php) to get the largest number, add 1 to it, then use that number.

To have "RO" appear in front of the number, you simply concatenate it whenever you display the number. Internally, you keep it as a number.

 

[katzi4eva]

Thank you for your reply, i'll try that in the morning!
The main use of the data will be an export that i send to the customer so when i do the export i would have to add the "RO" to the field again?

 

[plog]

Yes, you would create a query and do it there:

RONumberFormatted: "RO" & [RONumber]