DCount on Multiple Fields

[LarryDavidow]

I have a table that has two fields combined to form a Key field. I want to use the Dcount function to customize a msgbox when the user enters a value that already exists for that record in the subform. However, the Dcount function needs to hold true for both fields, not one OR the other...

Here is what I have so far...

Private Sub Combo10_AfterUpdate()
Dim intCt As Integer
intCt = DCount("[ModCode]", "CastingDetail", "[ModCode] = '" & Me.Modcode & "'&quot
If intCt <> 0 Then
MsgBox &quot;You have already selected &quot; & Me.Combo10.Column(1) & &quot; for this casting&quot;
Me.Combo10 = &quot;&quot;
Exit Sub
End If
End Sub

I need something like this, however, it's not working...

Private Sub Combo10_AfterUpdate()
Dim intCt As Integer
intCt = DCount(&quot;[ModCode]&quot;, &quot;CastingDetail&quot;, &quot;[ModCode] = '&quot; & Me.Modcode & &quot;'&quot And DCount(&quot;[CastingDetailCode]&quot;, &quot;CastingDetail&quot;, &quot;[CastingDetailCode] = &quot; & Me.CastingDetailCode & &quot;&quot
If intCt <> 0 Then
MsgBox &quot;You have already selected &quot; & Me.Combo10.Column(1) & &quot; for this casting&quot;
Me.Combo10 = &quot;&quot;
Exit Sub

End If
End Sub

I hope this is enough info...

Larry

 

[BillPower]

Hi LarryDavidow,

I haven't tested this, but I think it might do the job:

Dim intCt As Integer
intCt = DCount(&quot;[ModCode]&quot;, &quot;CastingDetail&quot;, &quot;[ModCode] = '&quot; & Me.Modcode & &quot;'&quot
intCt = DCount(&quot;[CastingDetailCode]&quot;, &quot;CastingDetail&quot;, &quot;[CastingDetailCode] = &quot; & Me.CastingDetailCode & &quot;&quot + intCt
If intCt <> 0 Then
MsgBox &quot;You have already selected &quot; & Me.Combo10.Column(1) & &quot; for this casting&quot;
Me.Combo10 = &quot;&quot;
Exit Sub
End If

Bill

 

[LarryDavidow]

Thanks for the help, Bill but I just can't get it to work. I have tried so many different variables. The Dcount function is still specific to the whole table, and not just the current records in the sub form. Somehow, I need to make the
intCt = DCount &quot;[ModCode]&quot;, &quot;CastingDetail&quot;, &quot;[ModCode] = '&quot; & Me.Modcode & &quot;'&quot
statement specific to the current records in the sub form.

This one is baking my noodle, yet I know it has to be very simple. How can I run a Dcount function on specific records in the subform?????

 

[BillPower]

If Modcode is a Field/Control in the Sub Form and Combo10 is in the Parent Form, try this syntax:

intCt = DCount &quot;[ModCode]&quot;, &quot;CastingDetail&quot;, &quot;[ModCode] = '&quot; & Me!SubformName.Form!Modcode & &quot;'&quot

Refer to CastingDetailCode in the same way if it is on the Sub Form and being called from the Main/Parent Form.

You will need to change SubformName to your actual Sub Form Name. Also try with and without the apostrophes before getting back if this once again doesn't work.

These posers are sometimes made more difficult not being able to view the forms and controls in question.

Bill