reset Dmax by case

[kost36]

Hi al,
I use this and I need to reset the number for every case. May be it's not the best code to do this but at least it works at a half
Could you please help?
type_media is short text, test is number
Thank's in advance


Private Sub type_media_AfterUpdate()
Select Case type_media
Case Is = "Movies photo"
If Not IsNull(Me!test) Then
Me.test.Locked = True
Me.file_name.Locked = True
Else
Me.test.Locked = False
Me.file_name.Locked = False
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films]), 0) + 1
Me.file_name = ID_films & "_" & Me.test & ".jpg"
End If
Case Is = "Shooting photo"
If Not IsNull(Me!test) Then
Me.test.Locked = True
Me.file_name.Locked = True
Else
Me.test.Locked = False
Me.file_name.Locked = False
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films]), 0) + 1
Me.file_name = ID_films & "_g" & Me.test & ".jpg"
End If
Case Is = "Poster photo"
If Not IsNull(Me!test) Then
Me.test.Locked = True
Me.file_name.Locked = True
Else
Me.test.Locked = False
Me.file_name.Locked = False
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films]), 0) + 1
Me.file_name = ID_films & "_a" & Me.test & ".jpg"
End If
End Select
End Sub

 

[Grumm]

If you reset Me.test in each case, I don't see why you need to check it in each case.
You know that once you enter in a case, the other ones are skipped. So if you set Me.test= null at the end of the Select. The number will always be null the next time the function is called.
Or am I missing something ?

 

[kost36]

Dear Grumm,
the function as it is called every "type_media after update" and the result is

type_media test fil_dir
Film photo 1 1_1.jpg
Film photo 2 1_2.jpg
Film photo 3 1_3.jpg
Shooting photo 4 1_g4.jpg
shooting photo 5 1_g5.jpg
Poster photo 6 1_a6.jpg
poster photo 7 1_a7.jpg
Poster photo 8 1_a8.jpg


what i need is to reset Dmax every time the type_media changes so the above must appears as
type_media test fil_dir
Film photo 1 1_1.jpg
Film photo 2 1_2.jpg
Film photo 3 1_3.jpg
Shooting photo 1 1_g1.jpg
shooting photo 2 1_g2.jpg
Poster photo 1 1_a1.jpg
poster photo 2 1_a2.jpg
Poster photo 3 1_a3.jpg

that's the reason type_media is in case and Dmax must reset

as for the checking of type_media into all cases you have right. It's not necessery. So I changed it putting the "check" out of cases

Private Sub type_media_AfterUpdate()
If Not IsNull(Me!test) Then
Me.test.Locked = True
Me.file_name.Locked = True
Else
Me.test.Locked = False
Me.file_name.Locked = False
End If

Select Case type_media
Case Is = "Movies photo"
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films]), 0) + 1
Me.file_name = ID_films & "_" & Me.test & ".jpg"
Case Is = "Shooting photo"
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films]), 0) + 1
Me.file_name = ID_films & "_g" & Me.test & ".jpg"
Case Is = "Poster photo"
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films]), 0) + 1
Me.file_name = ID_films & "_a" & Me.test & ".jpg"
End Select
End Sub

What I am thinking is that something missing from
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films]), 0) + 1
in which may need to add the type_media criteria.
That's the point I am a little confused

thank's

 

[kost36]

that makes the trick
thank's all for your time

Private Sub type_media_AfterUpdate()
If Not IsNull(Me!test) Then
Me.test.Locked = True
Me.file_name.Locked = True
Else
Me.test.Locked = False
Me.file_name.Locked = False
End If

Select Case type_media
Case Is = "Movies photo"
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films] & " and [type_media] = " & Chr(34) & Me.type_media & Chr(34))) + 1
Me.file_name = ID_films & "_" & Me.test & ".jpg"

Case Is = "Shooting photo"
'ID_films = Me.ID_films
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films] & " and [type_media] = " & Chr(34) & Me.type_media & Chr(34))) + 1
Me.file_name = ID_films & "_g" & Me.test & ".jpg"

Case Is = "Poster photo"
'ID_films = Me.ID_films
Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films] & " and [type_media] = " & Chr(34) & Me.type_media & Chr(34))) + 1
Me.file_name = ID_films & "_a" & Me.test & ".jpg"

End Select
End Sub

 

[MarkK]

Avoid repetition, avoid repetition, avoid repetition...

Private Sub type_media_AfterUpdate()
    Dim tmp As String
    
    Me.test.Locked = Not IsNull(Me!test)
    Me.file_name.Locked = Me.test.Locked

    Select Case type_media
        Case "Movies photo"
            tmp = "_"
        Case "Shooting photo"
            tmp = "_g"
        Case "Poster photo"
            tmp = "_a"
    End Select

    Me.test = Nz(DMax("[Test]", "ST_media_ph", "ID_films = " & [ID_films] & " and [type_media] = " & Chr(34) & Me.type_media & Chr(34))) + 1
    Me.file_name = ID_films & tmp & Me.test & ".jpg"
End Sub

The only thing that changes case to case, that I can see, is the single character. If that's true, everything else is the Select Case is repetition.

 

[kost36]

Hi Mark,
much pretty code, without repetitions but in Me.file_name = ID_films & tmp & Me.test & ".jpg" tmp is not added

many thank's

 

[kost36]

it was my fault....
it works perfect
thanks