DCount

[Ray]

I have a table with membership numbers. The field is called Mbr No. I have
a form with the field Mbr No in it. When I am entering new records I want
the code to check if the number already exists and if so print an error
message. Here is the code I have been trying to work out.

Private Sub Mbr_No_BeforeUpdate(Cancel As Integer)
If DCount("[mbr no]", "members", "[mbr no]>0") Then
MsgBox "This Member Number " & Me.[Mbr No].Value _
& " has already been used!", vbExclamation, "Duplicate Member #"
Cancel = True
End If

End Sub

Unfortunately (for me) this returns the "already used" message whether it is
true or not.

My instinct tells me it has to have something to do with identifying the
last Mbr No as being in the form but I have tried variants like Me.[Mbr No]
(see below) with no luck.

Private Sub Mbr_No_BeforeUpdate(Cancel As Integer)
If DCount("[mbr no]", "members", "Me.[mbr no]>0") Then
MsgBox "This Member Number " & Me.[Mbr No].Value _
& " has already been used!", vbExclamation, "Duplicate Member #"
Cancel = True
End If

End Sub

Please help.

Thank you.

Ray

 

[Mr B]

Ray,

Try the following:

Private Sub Mbr_No_BeforeUpdate(Cancel As Integer)
Dim lngMbrNum As Long
lngMbrNum = Me.NameOfYourTextBox
If DCount("[Mbr No]", "members", "[Mbr No] = " & lngMbrNum & "") > 0 Then
MsgBox "This Member Number " & Me.[Mbr No].Value _
& " has already been used!", vbExclamation, "Duplicate Member #"
Cancel = True
End If
End Sub

You could use the following code in the AfterUpdate event of you control and
actully assign the next available number to the field:

Private Sub Mbr_No_AfterUpdate()
Dim lngMbrNum As Long
Dim strMsg As String
Dim vbResponse

lngMbrNum = Me.NameOfYourTextBox
If DCount("[Mbr No]", "members", "[Mbr No] = " & lngMbrNum & "") > 0 Then
lngMbrNum = DMax("[Mbr No]", "members") + 1
strMsg = "The value you entered has already been " _
& "used!" & vbNewLine & vbNewLine & "The " _
& "next avaliable." & "number " _
& "is: " & lngMbrNum & "." & vbNewLine & vbNewLine & " " _
& "Would you like to use """ & lngMbrNum & """ as the next " _
& """Member Number""?"
vbResponse = MsgBox(strMsg, vbQuestion + vbDefaultButton1 + _
vbYesNo, "Number Not Avaiable")
If vbResponse = vbYes Then
With Me.NameOfYourTextBox
.Value = DMax("[Mbr No]", "members") + 1
.SetFocus
End With
Else
With Me.txtMemNo
.Value = 0
.SetFocus
End With
End If
End If

End Sub