Dcount Question

[Guest]

I am trying to use this code to count records and if the record is
found,"Your Data was entered". If no record is found I am trying to give
"Your Part Number was not found" and not enter the data. Here is my current
code, what corrections do I need to make? thanks in advance.
Private Sub PartNum_AfterUpdate()
Dim PartNum As String
Me.PartNum = PartNumber
x = DCount("[FootSwitch1]", "[Part Number Master]")
If x = 0 Then
MsgBox ("Your Data Was Entered")
Else: MsgBox ("Your Part Number Was Not Found")
End If

End Sub

 

[John W. Vinson]

I am trying to use this code to count records and if the record is
found,"Your Data was entered". If no record is found I am trying to give
"Your Part Number was not found" and not enter the data. Here is my current
code, what corrections do I need to make? thanks in advance.
Private Sub PartNum_AfterUpdate()
Dim PartNum As String
Me.PartNum = PartNumber
x = DCount("[FootSwitch1]", "[Part Number Master]")
If x = 0 Then
MsgBox ("Your Data Was Entered")
Else: MsgBox ("Your Part Number Was Not Found")
End If

Well, the short answer is that your DCount isn't counting what you think it's
counting. It's counting the number of records in the table named [Part Number
Master] which have a non-NULL value in the field FootSwitch1. There might be
dozens or hundreds, and in any case the DCount will not make any reference to
what the user has entered. You'll need to use the (optional) third argument to
search the appropriate field (which I do not know!) in [Part Number Master].

BUT... rather than making users type a part number, and slapping their hands
if they type wrong, why not use a Combo Box based on the [Part Number Master]
table? Then the user can *select* (typing into the combo box with autocomplete
makes this really fast) a valid value.

John W. Vinson [MVP]