Subform Counter 1

[Woody666]

I have a main form which has Job details on and a subform which lists Media details. What I want to achieve is if I entered a job number of 2000 that the media number on the sub form is assigned 1 if no other media is assigned to that job or is incremented by 1 if other media exist. This rule should apply to all job numbers. What Im trying to get is the following:

Enter Job Number 2000 Media Number = 1
Enter Job Number 2001 Media Number = 1
Enter Job Number 2000 Media Number = 2 etc

I may be approaching this the wrong way but look forward to anyones comments.

Thanks
Woody Woody

 

[KarMac21]

I believe this should work for ya Woody

Private Sub JobNumber_AfterUpdate()
Dim intMediaCnt as integer

intMediaCnt = IIf(IsNull(DMax("Media Count","table where media count is stored","Job # = " & JobNumber)),1,DMax("Media Count","table where media count is stored","Job# = " & JobNumber))

Currentdb.execute "Update {your table media table name} Set [Media Count] = " & intMediaCnt

End Sub

If you have any questions let me know - Shane

 

[Woody666]

Thanks Shane,

Its certainly does help. While I can recognise all the individual expressions I would have never put them all together like that to come up with a solution. I suppose its practice and forums etc like this where you learn the things most books cant teach. Thanks
Woody

 

[KarMac21]

Awesome, I'm glad it worked. This forum is an excellent place to learn new stuff.

Good Luck in the future - Shane