Using VBA to program code in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am creating a program in access, but i am using VBA to insert the code.
I have created a program that would not allow a user to enter duplicate
numbers, if the number is duplicate then a msgbox will be displayed, but the
code is not working correctly. I want the code to recognize the difference
between a duplicate account number and a new account number. I have copied a
sample of the code that I have created could someone let me know what am iI
doing wrong or what am I missing in the coding.

Private Sub Account_Number_BeforeUpdate(Cancel As Integer)
Dim strAccount_Number As String, strMsg As String, strDuplicate_Number As
String
Dim strNewAcctNumber As String

If (Account_Number) Or Account_Number = Duplicate_Number Then
strDuplicate_Number = MsgBox("Sorry Duplicate Number, Please Try
Again", vbInformation)
Cancel = True
Else
' If Account Number is not a duplicate number continue process.
If Not Duplicate_Number Then
strNewAcctNumber = MsgBox("Continue", vbInformation)
End If
End If
End Sub

Thank you very much for your help
 
Private Sub Account_Number_BeforeUpdate(Cancel As Integer)
Some questions and comments:

Dim strAccount_Number As String, strMsg As String, strDuplicate_Number As
String
Dim strNewAcctNumber As String
**What are you trying to do here?
If (Account_Number) unless this is a boolean data type, it will alway
evaluate to False.
** Where does Duplicate_Number come from? It is not Dimmed in this sub
If (Account_Number) Or Account_Number = Duplicate_Number Then
** You are assigning a number returned from the MsgBox function to a string,
Why?

strDuplicate_Number = MsgBox("Sorry Duplicate Number, Please Try
Again", vbInformation)
Cancel = True
Else
' If Account Number is not a duplicate number continue process.
If Not Duplicate_Number Then
** Another assignment to a string from MsgBox
strNewAcctNumber = MsgBox("Continue", vbInformation)
End If
** Why the extra End If? Is there code we don't see?
End If
End Sub

Here is a suggestion. I am assuming Account_Number is a text box on your
form and I am giving the field in the table the name Account_Num. Change the
names as you need to:

If Not IsNull(DLookup("[Account_Num]", "MyAccount Table", "[Account_Num]
= '" & Me.Account_Number & "'") Then
MsgBox("Sorry Duplicate Number, Please Try Again", vbInformation)
Cancel = True
End If

The DLookup will return the account number if it exists; otherwise, it will
return Null.
If you don't get a Null, then the account number being entered is a duplicate.
 
Back
Top