DMax Arugments

[Guest]

I'm making a small data base to help me with my professional photo business.
On a busy day I can go to 3 schools that and photograph 500 students or more
per school. ( I'm stationed in Europe.) My [MasterData] table has 7 fields.
The fields that I'm concerned with are [OrderNumbr] and [SchoolName]. I need
the [OrderNumbr] field to reset to 1 when I type in a new entry into
[SchoolName]. The current code that doesn't work is this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!OrderNumbr = DMax("OrderNumbr", "MasterData", "[SchoolName]=" &
[SchoolName]) + 1
End Sub

Thanks in advance for everyone that contributes.

Mike

 

[Guest]

Try this

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!OrderNumbr = NZ(DMax("OrderNumbr", "MasterData", "[SchoolName]=" &
[SchoolName]),0) + 1
End Sub

Now if the School name s string, try this
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!OrderNumbr = NZ(DMax("OrderNumbr", "MasterData", "[SchoolName]='" &
[SchoolName] & "'"),0) + 1
End Sub

 

[Steve]

I use the DMAX/DMIN function frequently in code when working with multiple
tables. I always find myself spending more time getting formatting right
than I should since I use it often. For example, here is a snip of an
easier, working code block:

UPDATE Table1 INNER JOIN Table2 ON [Table1].[Part]=[Table2].[Item] SET
Table1.DATE = ([Table2].[Timer]), Table1.Num = ([Table2].[Number])
WHERE ((([Table2].[Timer])=DMin("[Timer]","[Table2]","[Item] = '" &
[Table1].[Part] & "'")));

This basically updates an order number field and date field in Table1 where
it finds the earliest dated order for the same part in Table 2.

I typically screw myself up with format, quote marks, and arguments for
everything following the WHERE. Is there a good book, web page, help doc,
etc that does an excellent job of explaining and laying out the form for
Dmax/Dmin when using with multiple tables like this? Obviously, I cut and
paste my existing code and tweak it to meet the current needs, but I'd
really like to have a good cheat-sheet on using these functions in this
manner. All the sources, books, etc I have don't cover using it with joined
tables.

Thanks for any help!

Thank you,
Your code work perfectly!

Try this

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!OrderNumbr = NZ(DMax("OrderNumbr", "MasterData", "[SchoolName]=" &
[SchoolName]),0) + 1
End Sub

Now if the School name s string, try this
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!OrderNumbr = NZ(DMax("OrderNumbr", "MasterData", "[SchoolName]='" &
[SchoolName] & "'"),0) + 1
End Sub

--
I hope that helped
Good luck

I'm making a small data base to help me with my professional photo business.
On a busy day I can go to 3 schools that and photograph 500 students or more
per school. ( I'm stationed in Europe.) My [MasterData] table has 7 fields.
The fields that I'm concerned with are [OrderNumbr] and [SchoolName]. I need
the [OrderNumbr] field to reset to 1 when I type in a new entry into
[SchoolName]. The current code that doesn't work is this.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!OrderNumbr = DMax("OrderNumbr", "MasterData", "[SchoolName]=" &
[SchoolName]) + 1
End Sub

Thanks in advance for everyone that contributes.

Mike