Increment a field in a form by 1

[Grieve1]

Hi Me again

I have a fom "Payment Certificate" with a field called [Paymentnumber]now I want to increment this field by 1 when 2 conditions occur that is when field [companyname]=[companyname] and field [contractname] =[contractname]I can get it to work for company name but not for both. I am using a "qryMaxVal", then using code to call it in a dlookup. e.g.
Private Sub Contract_Name_AfterUpdate()
If Me.NewRecord Then
Me.[Payment Number] = DLookup("[MaxValue]", "qryMaxVal", "[CompanyName]=""" & [CompanyName] & """") + 1
End If

End Sub

 

[Remou]

Why not use Dmax?

Dmax("PaymentNumber","Relevant Table or Query","[CompanyName]=""" & [CompanyName] & """ AND [ContractName=""" & [ContractName] & """")

It seems that you have several design flaws. You may wish to read [URL unfurl="true"]http://www.R937.com/Relational.html[/URL]