Dcount problem with 2 field criteria 1

[Joallyn]

Hello all,

I've gone through every "Dcount" post and am still banging my head against this one...so here goes.

I have an intersection table

tblActionMaterial
ActionID (pk)
MaterialID (pk)

In an Action subform I allow the user to enter materials used to complete that action. If a particular material has already been used for that action, I want a message box to pop up, notify the user, and ask them to pick something else. That way I don't end up with a pk violation.

So in Before Update I look at whether both the MaterialID and the ActionID already appear in the table:

Private Sub Material_BeforeUpdate(Cancel As Integer)
If DCount("[MaterialID] +
[ActionID]", "tblActionMaterial", "MaterialID = " & Me!
MaterialID And "ActionID = " & Me!ActionID) > 0 Then

MsgBox "This material is already chosen; pick another"
DoCmd.CancelEvent
End If

End Sub

The problem is that I keep getting a type error mismatch. The IDs in question are both numbers; can anyone see where I've gone astray in the syntax?

thanks!!

Joallyn

 

[Steve101]

A couple of points here which hopefully will help:

(a) It would probably be more meaningful to use the DLookup function here, though the DCount is also legitimate.

(b) Try changing your syntax to the following:

If DCount("[MaterialID] + [ActionID]",
"tblActionMaterial",
"MaterialID = " & Me!MaterialID & _
" AND ActionID = " & Me!ActionID) > 0 Then
etc.

I've reformatted your code slightly, but the only problem that I see with it is that the AND operator needs to be made part of the Criteria expression, which will otherwise be evaluated incorrectly, and produce the error you are having.

(c) Why not let the Access engine handle this error situation for you; If an attempt is made to enter a duplicate primary key, you will be prevented from saving the record anyway. Use the OnError event associated with the form to trap the error, and handle it yourself. For example, adapt the following code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
MsgBox "Duplicate key. Please change, or press escape twice to cancel changes"
Case Else
MsgBox DataErr & " " & Error(DataErr)
End Select
Response = 0
End Sub

Hope this helps,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Joallyn]

Hi Steve,

that helped, very much so.

I actually *had* an error trap in there, but I wanted to notify the user more quickly that the entry was a duplicate (with the error message, the user could go on to enter a material quantity, and only then would the error message pop up.)

Making the syntax change fixed my Dcount problem. However, I now get puzzling and exasperating "Validation rule" errors (and I don't have a validation rule on that field!), so I've gone back to the error trap.

thanks again for your help! I'll look into Dlookup--I'm an absolute VB novice, so I have lots to learn.

Joallyn