using DMAX with a where clause?

[Christine Vollberg via AccessMonster.com]

I have a problem, I am trying to create a new record with some of the
information from the previous record except for one item. The Charge
Sequence number needs to increment, but it first needs to see the highest
number for that case and defendant that already exists. I used a macro to
open a form and then set the values for all necessary information, I have
tried a few different ways to set the charge seq number but it won't work.
Here is the code I was trying to use on the cmd button:
Private Sub cmdAddNewCharge_Click()
On Error GoTo Err_cmdAddNewCharge_Click

Dim defchaseq As String
Dim Def_Charge_Seq As Integer

DoCmd.RunMacro "AddNewCharge"
defchasseq = "SELECT * FROM tblDefChargesSentence where DefendantId = Me!
DefendantId and CaseNo = Me!CaseNo"
Def_Charge_Seq = DMax("[ChargeSeqNo]", "tblDefChargesSentence")
Me!ChargeSeqNo = (Def_Charge_Seq) + 1


Exit_cmdAddNewCharge_Click:
Exit Sub

Err_cmdAddNewCharge_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdAddNewCharge_Click

End Sub

Any help will be greatly appreciated thank you

 

[DubboPete]

Hi Christine...

instead of

Def_Charge_Seq = DMax("[ChargeSeqNo]", "tblDefChargesSentence")
Me!ChargeSeqNo = (Def_Charge_Seq) + 1

have you tried the simpler method of

Me!ChargeSeqNo = DMax("[ChargeSeqNo]", "tblDefChargesSentence") + 1

The List's Resident Goat-Herder
DubboPete

 

[Christine Vollberg via AccessMonster.com]

yes, but then it just pulls the highest from the table, not the highest for
that particular defendantId and caseno. I need to put the criteria in the
dmax statement instead of before it, I think.