Adding a new record to unique ID from UserForm

Question

I am trying to add a new record (unique ID) from a UserForm in Access. I have SQL code to Select the Last ID in the table, but even this did not work. It seemed to just grab the first ID. Is there a way, so that when I click the Combobox on the Userform a new unique Id is generated based on the last table value (without adding that value to the table).

SELECT Last(IDNumber) AS Expr1
FROM tbID;

Thanks much.

If this is a multiuser system, that is not a good idea, and even at that, max would be better. — Fionnuala, May 20 '16 at 08:33
Is it better to append the (other) values to the table and then let the table auto-generate the new record number? — Chris2015, May 20 '16 at 08:36
Very much so. It is quite complicated to get a new number in a multiuser system. — Fionnuala, May 20 '16 at 08:51
Note that auto-generated numbers may not be sequential, merely unique, and even that is arguable. — Fionnuala, May 20 '16 at 08:53
Great. Thanks for the feedback. One other question though..if you are creating new unique IDs at "random" are there any obvious ways to know which record is which...how to query them/keep track of your records? — Chris2015, May 20 '16 at 08:53
Hmm, if you mean how does a user find a record, an auto-number is not the way to go, identity numbers are for the db, not the user. — Fionnuala, May 20 '16 at 08:56
Here are some notes http://stackoverflow.com/questions/12517498/insert-query-with-sequential-primary-key/12528222#12528222 — Fionnuala, May 20 '16 at 09:01

score 0 · Accepted Answer · answered May 20 '16 at 09:33

0

Last and First really just picks some value.

This will more likely succeed (ignoring for a moment the good advice in the comments):

SELECT Max(IDNumber) AS Expr1
FROM tbID;

answered May 20 '16 at 09:33

Gustav

48,886
6
31
51

That depends. If the ID is for the vehicles of your company, these are probably not entered massively by the second. – Gustav May 20 '16 at 09:48
Famous last words. Design for the future. I have been doing this stuff for quite a while. – Fionnuala May 20 '16 at 09:53
So have I, and learned not to complicate matters beyond reason. Of course, for - say - an audit trail such a simple method won't be reliable. – Gustav May 20 '16 at 10:10

Adding a new record to unique ID from UserForm

1 Answers1