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

Kamis, 28 Maret 2013

RE: [MS_AccessPros] Re: If with DLookup question

Jim-

If Account is text, then you must do:

' If on a new record or Account has changed,

If Me.NewRecord Or Me.txtSuborg <> Me.txtSuborg .OldValue Then
' Check to see if account entered already exists

If Not IsNull(DLookup("[Account]", "AccountCodes", "[code] = '"
Me.txtSuborg & "'")) Then
Cancel = True
MsgBox "This Suborg code has already been used. " _
& "Correct your entry or press Esc to cancel."
End If

End If

I disagree with Clive that you need the Else.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Clive
Sent: Thursday, March 28, 2013 5:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: If with DLookup question

Hi Jim,

The logic of the statement is not correct. you are
missing an Else statment.

If DLookup("[Account]", "AccountCodes", "[code] = _
[Forms]![frmAddNewAccounts]![txtSuborg]") Then
Cancel = True
Else ' You need this Else
MsgBox "This Suborg code has already been used. " _
& "Correct your entry or press Esc to cancel."
End If

Make sure that you always compile your code after
changes, (though it would'nt have found this problem).

Regards, Clive.

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner
<luvmymelody@...> wrote:
>
> 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@...>
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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]
>

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