Automatically increment session number

[notfound]

Hello all,

I'm developing a simple db in Access 2002. It's main function is to track when I see clients, so I have a table of sessions which relates to a table of clients.

As I enter a new session on a form, I would like to track how many sessions a client has had.

Ideally, this would be calculated automatically: I select the client from a drop-down list, Access should work out which session number they're up to and enter it into a field called Session#

I guess this needs something to look up the client, find their highest session number in the sessions table, and add 1.

I assume DMax comes into this somehow, but please illuminate me!

Thanks,
notfound

 

[Scooterbug]

You are correct that Dmax is the way to go. You would do this:

NewSessionNumber = nz(dmax("[SessionNumber","SessionTable","[ClientID=" & forms!YourForm!ComboBox),0) + 1

The nz() is there for when or if you add a new client and there is no session record. Of course you will need to replace field names and table with what they actually are in your database.

Conversly, if all you are looking for is the number of sessions each client has been to, you could put a text box on the form and set the control source to a simple Dcount.

SessionTotal = dcount("*","SessionTable","[ClientID]=" & forms!YourForm!ComboBox)

 

[notfound]

Belated thanks for this reply Scooterbug!

cheers,
notfound