I'm working on an Access 2003 database where users can submit requests for personnel. For a number of complicated reasons, the best primary key to use is going to be an autonumber (or, really, just a number). Basically, what I want to do is this -
when a user types in all the info for their request, it is sitting in unbound textboxes. I then use VBA code to create the SQL query and insert the record. What I need is a way to also insert a value for the "RequestID" field that is one more than the previous max number (for instance, if there are 10 requests in there, I need to insert "11," next time it will be "12," etc.). Any ideas on the best way to find this max number using SQL?
I tried just creating a query in design view that pulls the maximum correctly, but whenever I try to use code to pull that number ("SELECT qry_RequestID.R equestID FROM RequestID"), it doesn't seem to be getting me the correct recordset. I'm open to any ways to do it, that's just the first one that came to mind.
when a user types in all the info for their request, it is sitting in unbound textboxes. I then use VBA code to create the SQL query and insert the record. What I need is a way to also insert a value for the "RequestID" field that is one more than the previous max number (for instance, if there are 10 requests in there, I need to insert "11," next time it will be "12," etc.). Any ideas on the best way to find this max number using SQL?
I tried just creating a query in design view that pulls the maximum correctly, but whenever I try to use code to pull that number ("SELECT qry_RequestID.R equestID FROM RequestID"), it doesn't seem to be getting me the correct recordset. I'm open to any ways to do it, that's just the first one that came to mind.
Comment