Auto increasing a number, depending on maximum number when condition is met

[dannewestis]

Hi,

I'm trying to get the maximum number in a table field to increase it by one depending on the member that is selected in a drop down in a field.

I have three tables: members, programs and times. Each member can have N programs and each program can be broadcast N times.

Each member has a three digit code, like XXX. Each program has the three digit code of the member + three numbers that are supposed to auto increment. That is, the first program of member X with the member code XXX is called XXX001.

What I'm trying to do is that when a new program is filled in and I select the member, then the program code should update automatically, adding one to the latest program by that member.

That is, if the last program by member X that was inserted in the database is XXX010, then if a new program is inserted it should automatically be XXX011, even though programs by other members have been added in between.

This is the code I use now, for the AfterUpdate when selecting the member in a dropdown in the form. But although I've played around a bit, I just get error messages...

Private Sub medlemsruta_AfterUpdate()
Dim medlemskod
medlemskod = Me![medlemsruta].Column(2)

    Dim strMax  As String
    
    strMax = DMax("programs_kod", "table_programs", "Left$(programs_kod, 3) = medlemskod")
    Me!program_kod = Left$(strMax, 3) & Format$(Val(Right$(strMax, 3)) + 1, "000")
End Sub

Medlemsruta is a dropdown where one selects the member from the members table, where the three digit code is in the third column (Column(2)).

I'm trying to use DMax to get the maximum number for the particular member and after that adding 1 to that for the new program code.

Grateful for any advice! Thanks!

 

[neileg]

You would find the whole process much easier if you stored the member ID and the incrementing number in two separate fields and concatenated them for display purposes.

 

[dannewestis]

Thanks neileg and Pat!

I split them up in two fields and have it working quite fine now.

Only issue is when someone is changing the member (and thus getting a new code) and then changing it back to the old member. Then the original number is gone and the new number is one higher than the then highest.

But I'll try to add some kind of flag when the record is closed, so that after that it's not possible to change the member again, as the program already has got its code.

Thanks again!