Set a var = to the result from a sql statement in Access

Question

I am trying to set the value of the member number = to the current max number + 1. What ist eh best way to do that? I am using Access 2007.

Answer 1

It depends on the number of users you have. If it is unlikely that more than one person will be updating the table, DMax should suit:

 DMax("MemberID","tblMembers")

If you have a number of users who may be updating the table at the same time, it may be best to keep a separate table that holds the last ID. The table can be opened and locked in order to get the next id.

Answer 2

You might want to take a look at the excellent ELookup function at this website

http://allenbrowne.com/ser-42.html

You get the simplicity of dlookup with improved performance and added options, very handy

EDIT:

Also check out

http://www.mvps.org/access/modules/mdl0012.htm