ACCESS FEED: Re: [MS_AccessPros] If with DLookup question

Kamis, 28 Maret 2013

Re: [MS_AccessPros] If with DLookup question

I tried this below statement and I get the msgbox to come up and not a type mismatch, but it is not working because no matter what number I put in the text box whether it is in the accountcodes table or not I get the msgbox.

IsNull (DLookup("[code]", "AccountCodes", "[code] ='" & [Forms]![frmAddNewAccounts]![txtSuborg] & "'"))

Jim Wagner
________________________________

________________________________
From: luvmymelody <luvmymelody@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, March 28, 2013 7:52 AM
Subject: [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

[Non-text portions of this message have been removed]