lookup next ref on data entry

[Jackie]

Hi all

I'm having brain freeze over a database I am workin on and can't seem to find a way to make it work

What I'm looking to do is:

I have an account activity table that has an activity id, activity type (eg
invoice, credit note, payment, deposit etc etc), Ref and a number of other fields. The activity id is an auto number and what i want is to have the Ref to also be an auto number but be determined by the activity type so for example
i enter a new activity which for example is a invoice it looks up the last ref for an invoice type (type 2) and uses the next number - if i enter a credit note it looks up last number credit note as property type and uses the next number for credit note

I thought of using something like

on update

Me.Ref = Format(DMax("ActivityID", "Activity", Type= "2") + 1)

not sure if I'm on the right track would this even work?
Cheers

Jackie

[pbaldy]

For starters, this:

Type= "2"

likely needs to be

"Type= 2"

Also, I would think you'd want the max of Ref, not ActivityID.

[Jackie]

Thanks paul - I'm goin to work on it tonight and see how it goes

Cheers
Jackie