Clueless query

[QuantifyRisk]

Just trying to noob my way through creating a DB. Here's what I wanna do in Computer-English...

IF you selected Party from the drop down list
THEN run a query that looks up party records and takes the max
Then add 1 to the max and put that number in PartyCode

I started writing it in VBA but couldn't figure out how to do it without opening a query.

[pbaldy]

Try DMax() using the drop down in the criteria:

DLookup Usage Samples

[QuantifyRisk]

Look like this:
Me.PartyCode=DMax("PartyCode" , "Party" , "BestParties")+1
assuming BestPArties was the type of party I wanted to add 1 to?

[pbaldy]

Did you look at the link?

DMax("PartyCode", "Party", "PartyType = '" & Forms!FormName!ComboName & "'")

replacing the items in red with their actual names

[QuantifyRisk]

I did but it's hard for me to understand this sometimes because I'm not studying liberally. I'm looking up topics to find solutions to my problems. All I need to do is make this one DB and that's it. The ! Is new to me. I will try it again Friday and see how it goes.

[June7]

The bang (!) should work but if you want intellisense popup help, use dot (.).

[QuantifyRisk]

This, !, is a factorial in the math field. So I'm not familiar with ! Vs. .

[June7]

Just type them in code as suggested and see what happens.

[QuantifyRisk]

Is it necessary to use the 3rd criteria for DMax if I use an If/then statement for the choice of the drop down?

[pbaldy]

I don't understand, but without a criteria you'll get the max value regardless of the type of party.

[June7]

Use an If/Then where and how will that filter records?

Without the criteria, DMax will return the maximum value in the table, not the maximum value of the selected party type records.

[QuantifyRisk]

What I have:

If (Me.AgencyCode = "Flood Only") Then
Me.AgencyCode = DMax("AgencyCode", "Agency Information", "AgencyType=FloodOnly"' &Forms!AgencyInformation!AgencyType & '") + 1
End If

[June7]

The placement of apostrophes is wrong.

Why do you have the literal text "FloodOnly" in the criteria?

Me.AgencyCode = Nz(DMax("AgencyCode", "Agency Information", "AgencyType='FloodOnly" & Forms!AgencyInformation!AgencyType & "'"),0) + 1

[pbaldy]

Try

Me.AgencyCode = DMax("AgencyCode", "Agency Information", "AgencyType='" & Forms!AgencyInformation!AgencyType & "'") + 1

[QuantifyRisk]

The placement of apostrophes is wrong.

Why do you have the literal text "FloodOnly" in the criteria?

Me.AgencyCode = Nz(DMax("AgencyCode", "Agency Information", "AgencyType='FloodOnly" & Forms!AgencyInformation!AgencyType & "'"),0) + 1

What is Nz for?