Increment based on linked value

[dniezby]

I have a field that I want to increment. That's not the issue. A simple Dmax and it does the job. The issue comes up when I move to a new main form. It keeps going.



Here is an example.

The table I'm using had a field "event_id". Many records in this table will have that same event id. The field I want to increment will be "roundComplete"

I can get it to put an incrementing field but if the event_id changes, I need that "roundComplete" to start at 1 again.

Thoughts on how to do this?
I could just enter them manually but I would prefer to have it so its automatic.

[rzw0wr]

What code are you using now?

It sounds like you might need a table.

Dale

[dniezby]

Well, right now this is what I did.

' check to see if current event_id is the ' same as the record I'm working with

varA = DLookup("event_id","events","event_id=" & event_id)

' enter a increment value 
If event_id = varA then
 roundComplete = Dmax("roundComplete","plr_transactions")+1

This is working except I can't figure out how to have it restart with different event_ids

[Bob Fitz]

Perhaps something like:
Me.roundComplete = Nz(DMax("roundComplete", "yourTableName", "event_id=" & Me.event_id), 0) + 1

[rzw0wr]

if Dlookup("[event_id]","events","event_id=" & event_id") then
   Dmax("roundComplete",plr_transactions")+1)
else
    roundComplete=1
end if

Air code.
Should work.


Dale

[dniezby]

Perhaps something like:
Me.roundComplete = Nz(DMax("roundComplete", "yourTableName", "event_id=" & Me.event_id), 0) + 1

Didn't even think to use the Nz function. Don't know if I understand why it works, but it does.


Dale, I tried your code too and it did not work. Was giving me syntax errors. Thanks anyway though.

[rzw0wr]

Glad you got it working.
My air coding does need some more work.
Like my air guitar.

Dale

[Bob Fitz]

Didn't even think to use the Nz function. Don't know if I understand why it works, but it does.

dniezby
Glad it worked for you. Perhaps some explanation would help your understanding.
The DMax() function returns the maximum value in the field called "roundComplete" where the field called "event_id" in the table is = to the value of the control of the same name on the form. I didn't test to see what DMax() returns if no matching value is found. It may be that it would return 0 anyway but the Nz() function was included just in case. All we need then is to add one to the value with +1 at the end.

Hope this helps.