Dmax Or DCount - Multiple Criteria (Form Controls)

[Dmak]

I'm adding data to several tables with a single form using INSERT SQL and the Dmax method to sort relations.

On one of the tables there is the potential for duplicates which I want to avoid.

Please can someone provide an example of DMax or DCount or other method that I can use to decide whether to run the INSERT SQL Code.

So far I have;

If IsNull(DMax("PrimaryID", "

", "Criteria = " _
& Field1 = [Control1] & " AND " & "Field2 =" & "[Control2]")) = True Then
'Nothing
Else
DoCmd.RunSQL

This produces an incorrect result, always reports a Null. I'm using MsgBoxes at the moment to relay the result.

Any help, much appreciated. I have searched high and low for examples pulling values from controls but I only get examples using variables in the code.

 

[John Big Booty]

Try;

If IsNull(DMax("PrimaryID", "[Table]", "Field1 = " & [Control1] & "AND Field2 = " & [Control2])) = True Then
'Nothing
Else
DoCmd.RunSQL

This assumes that both criteria are numeric and not string values.

 

[Dmak]

Try;

If IsNull(DMax("PrimaryID", "[Table]", "Field1 = " & [Control1] & "AND Field2 = " & [Control2])) = True Then
'Nothing
Else
DoCmd.RunSQL

This assumes that both criteria are numeric and not string values.

I'll give this a try, thanks

 

[John Big Booty]

Just as a late observation Table, Field1 and Control1 are not really good objects or control names as they convey no hint as to the type of data they are storing or dealing with.

If they are not the actual names of objects or controls in your DB, then you will need to change them to match the reality of your DB.

 

[Dmak]

Just as a late observation Table, Field1 and Control1 are not really good objects or control names as they convey no hint as to the type of data they are storing or dealing with.

If they are not the actual names of objects or controls in your DB, then you will need to change them to match the reality of your DB.

Thanks John, I changed all the names to generic descriptive ones to give a bit more meaning to others.

Just testing your amendments now.

 

[Dmak]

Works a treat, can't thank you enough, cheers

 

[John Big Booty]

Glad you got it sorted