ACCESS FEED: [MS_AccessPros] If with DLookup question

Kamis, 28 Maret 2013

[MS_AccessPros] If with DLookup question

Hello all,

I have a form that the users enter data and I would like to have only one of the fields check to see if an account code exists so that there is no duplicates. The account codes are defined by management and cannot be an autonumber. I have the following code in the beforeupdate event. I have tried several instances so I left the commented lines in so I can see what I have tried. I get a Type mismatch error. I checked all fields in the tables and they are all formatted the same. So I cannot understand why there is a type mismatch.

Thank You for any help.

Jim Wagner

Private Sub txtSuborg_BeforeUpdate(Cancel As Integer)
'Dim strCriteria As String
'
'strCriteria = Me.txtSuborg
'strCriteria = "[txtSuborg] = """ & Me.txtSuborg & """"
'Debug.Print strCriteria
'If Me.NewRecord Or Me.txtSuborg <> Me.Suborg .OldValue Then
'If DLookup("[Account]", "AccountCodes", "[code] = Forms![frmAddNewAccounts].txtSuborg) Then
'If DLookup("Account", "[AccountCodes]", "[code] = " & Forms![frmAddNewAccounts].txtAccount) Then
If DLookup("[Account]", "AccountCodes", "[code] = [Forms]![frmAddNewAccounts]![txtSuborg]") Then
Cancel = True
MsgBox "This Suborg code has already been used. " _
& "Correct your entry or press Esc to cancel."
End If
'End If

End Sub