runtime 2001 error with DCount

[Lissa]

I have a table that stores the status of various pieces of hardware. I need to prevent the user from entering duplicate serial numbers so I decided to use DCount...

1. I have created a query called qryExistingSN that contains requires criteria from a subform.
PartInvId
[Forms]![sbfPartsInventory_PS]![PartInvId]
SerialNumber
[Forms]![sbfPartsInventory_PS]![SerialNumber]

2. My subform called sbfPartInventory_PS resides on a 'PartStatus' page on a main form called 'frmPartInventory'. The subform container is called sbfPartInventory.

3. On the subform, in the After Update event of the SerialNumber field I have the following code

If DCount("[SerialNumber]", _
"qryExistingSN", _
"[PartInvId] = '" & Forms![frmPartInventory].[sbfPartsInventory].Form.[PartInvId] & "' And " & _
"[SerialNumber] = '" & Forms![frmPartInventory].[sbfPartsInventory].Form.[SerialNumber] & "'") > 0 Then
MsgBox "this serial number already exists. Please try again.", vbCritical, "Warning!"
Me.Undo
End If

I keep getting a runtime error '2001' You have canceled the previous operation. I think it may be a syntax error in that I'm not referencing PartInvId or SerialNumber correctly... ??????

I have tried
Forms!frmPartInventory!sbfPartsInventory_PS!PartInvId
and that will not work either...

Any ideas on how I can solve this?
Thanks!

 

[HiTechCoach]

Are you getting this message with the is a duplicate? Ig yes, then it is probably due to canceling the event with the Me.Undo. You will need to add additional error handling. Without seeing all if your code, it is hard to say the best way to handle this.

It would help is you would post all the VBA code you are using.

 

[Lissa]

I would upload my database but I can't get it small enough to meet upload requirements...
I get the error as soon as the Serial Number field is updated.

 

[gemma-the-husky]

i would put extra spaces before and after the " = " signs in the where expression of the dcount - see if that helps

 

[Lissa]

I tried adding extra spaces and that didn't work. Thanks for the suggestion.
I'm going to try rewriting the dcount to not use the query and see if that helps...

 

[gemma-the-husky]

are your partid, and serial numbers text, or numbers

you have them both coded as text, so the dcount will fail if either is a number

 

[boblarson]

If the code is on the subform then you do not need the full, qualified form reference. Just use:

3. On the subform, in the After Update event of the SerialNumber field I have the following code

If DCount("[SerialNumber]", _
"qryExistingSN", _
"[PartInvId] = '" & [COLOR="Red"]Me.[/COLOR]PartInvId & "' And " & _
"[SerialNumber] = '" & [COLOR="Red"]Me.[/COLOR]SerialNumber & "'") > 0 Then
MsgBox "this serial number already exists. Please try again.", vbCritical, "Warning!"
Me.Undo
End If

And do as mentioned about the quotes if the items are numbers.

 

[Lissa]

Dave I think've pointed the problem...
PartInvId is a number and SerialNumber is text (since I have TBD until an actual serial number is entered).

I have a typemismatch error now in the changes I've made...
I think I need quotes ' ' around strFilterB but I'm not sure about how to get them in there "'" "'" wouldn't work...



Private Sub SerialNumber_AfterUpdate()
Dim strFilterA As Integer
Dim strFilterB As String

strFilterA = "PartInvId = " & Me.PartInvId
strFilterB = "SerialNumber = " & Me.SerialNumber

If DCount("*", "tblPart_Status", strFilterA And strFilterB) > 0 Then
MsgBox ("Serial Number already exist")
Cancel = True
End If

 

[boblarson]

Private Sub SerialNumber_AfterUpdate()
Dim strFilterA As Integer
Dim strFilterB As String

strFilterA = "PartInvId = " & Me.PartInvId
strFilterB = "SerialNumber = [COLOR="Red"]'[/COLOR]" & Me.SerialNumber [COLOR="red"]& "'"[/COLOR]

If DCount("*", "tblPart_Status", strFilterA[COLOR="red"] & " And " &[/COLOR] strFilterB) > 0 Then
MsgBox ("Serial Number already exist")
Cancel = True
End If

 

[Lissa]

Happy Happy Happy Joy Joy Joy!!

Using DCount("*", "tblPart_Status", strFilterA & " And " & strFilterB)
from Bob's last post worked.

It's always the quotes and the references that trip me up!

Thanks to everyone that posted suggestions! I love this forum!!!

 

[boblarson]

Happy Monday!