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

Kamis, 28 Maret 2013

RE: [MS_AccessPros] Re: If with DLookup question

Thanks for fixing my bad typing, Darrell!

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 Embrey, Darrell
Sent: Thursday, March 28, 2013 7:02 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: If with DLookup question

Hi Jim,
Your syntax error is a missing ampersand (&). Change :
If Not IsNull(DLookup("[Account]", "AccountCodes", "[code] = '" Me.txtSuborg & "'")) Then
to:
If Not IsNull(DLookup("[Account]", "AccountCodes", "[code] = '" & Me.txtSuborg & "'")) Then

Darrell

From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Jim Wagner
Sent: Thursday, March 28, 2013 1:57 PM
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Re: If with DLookup question

John,

I am struggling with the code still. the line in red is red in the vba editor. I do not see anything wrong with the syntax. If it is not red when you see it, it is the If not isnull line

Jim Wagner

Private Sub txtSuborg_BeforeUpdate(Cancel As Integer)

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

End Sub

Jim Wagner
________________________________

________________________________
From: John Viescas <JohnV@msn.com <mailto:JohnV%40msn.com> <mailto:JohnV%40msn.com>>
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Thursday, March 28, 2013 9:21 AM
Subject: 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%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.com>] On Behalf Of Clive
Sent: Thursday, March 28, 2013 5:07 PM
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.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> <mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> , Jim Wagner
<luvmymelody@... <mailto:luvmymelody@...%3cmailto:luvmymelody@...> <mailto: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@... <mailto:luvmymelody@...%3cmailto:luvmymelody@...> <mailto:luvmymelody@...>>
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com> <mailto:MS_Access_Professionals%40yahoogroups.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]

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

The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.

Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.

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

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