-
Jun 26th, 2017, 11:09 AM
#1
Thread Starter
Lively Member
VBA for Acces getting End of Statement error in Immedate Window
Hello:
The following code keeps giving me an end of statement error. It will not perform the DLookup. It will only open the two forms can you please assist. what do I need for the ending statements? Also when I click run in the when I'm not in Immediate window it keeps asking me to create and macro.
Private Sub MustLook()
Dim SubX As String
SubX = Nz(DLookup("[NCPDP_ID]", "Main_Credential_Entry_Table", "[NCPDP_ID] =" & [Forms]![Enter New Credentials]![NCPDP_ID])& ""),"") then
IIf IsNull(SubX) Then
DoCmd.OpenForm ("Enter New Credentials")
Else
DoCmd.OpenForm ("Update Existing Credentials")
End If
End Sub
-
Jun 26th, 2017, 11:17 AM
#2
Re: VBA for Acces getting End of Statement error in Immedate Window
IIF is not correct, should be IF
-
Jun 26th, 2017, 11:26 AM
#3
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
the VarX statement has end of statement error and the IIF is null statement has end of statements error on it
-
Jun 26th, 2017, 11:29 AM
#4
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
I mean SubX with the DLookup has End of statement error and IIF is Null SubX
-
Jun 26th, 2017, 11:53 AM
#5
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
-
Jun 26th, 2017, 11:56 AM
#6
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Now Immediate window said block if without end if when I took away the extra I
-
Jun 26th, 2017, 12:05 PM
#7
Re: VBA for Acces getting End of Statement error in Immedate Window
Eric
Does your code look like this now?
Code:
Private Sub MustLook()
'
Dim SubX As String
'
SubX = Nz(DLookup("[NCPDP_ID]", "Main_Credential_Entry_Table", "[NCPDP_ID] =" & [Forms]![Enter New Credentials]![NCPDP_ID])& ""),"") then
'
If IsNull(SubX) Then
DoCmd.OpenForm ("Enter New Credentials")
Else
DoCmd.OpenForm ("Update Existing Credentials")
End If
End Sub
If so, should be ok, unless the error is occuring in another sub.
BTW, I used the CODE wrapper .. when posting, highlight the code and press the # symbol.
Spoo
-
Jun 26th, 2017, 12:06 PM
#8
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Does the DLookup look ok that's the one throwing the end of statement error in immediate window that's not working
-
Jun 26th, 2017, 12:06 PM
#9
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
then I have another issue how do I get code to stay in immediate window so I can get a button to work
-
Jun 26th, 2017, 12:41 PM
#10
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Originally Posted by Spooman
Eric
Does your code look like this now?
Code:
Private Sub MustLook()
'
Dim SubX As String
'
SubX = Nz(DLookup("[NCPDP_ID]", "Main_Credential_Entry_Table", "[NCPDP_ID] =" & [Forms]![Enter New Credentials]![NCPDP_ID])& ""),"") then
'
If IsNull(SubX) Then
DoCmd.OpenForm ("Enter New Credentials")
Else
DoCmd.OpenForm ("Update Existing Credentials")
End If
End Sub
If so, should be ok, unless the error is occuring in another sub.
BTW, I used the CODE wrapper .. when posting, highlight the code and press the # symbol.
Spoo
Yes my code looks like this plus I veried it and too away private and still got errors
-
Jun 26th, 2017, 12:42 PM
#11
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
It's the SubX DLookup statement giving me all the problems, it keep saying end of statement error
-
Jun 26th, 2017, 12:43 PM
#12
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
I put in codes 0xD and 0xA and it didn't work
-
Jun 26th, 2017, 12:49 PM
#13
Re: VBA for Acces getting End of Statement error in Immedate Window
Thread moved to Office Development, which is the best place for VBA questions.
My usual boring signature: Nothing
-
Jun 26th, 2017, 12:56 PM
#14
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
the Private, Dim and SubX all have end of statement errors on them, I tried to put 0xD and 0xA to clear them but it didn't work, I don't know what Immediate window wants here
-
Jun 26th, 2017, 12:57 PM
#15
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
I also don't know how to get my code to stay in immediate window so I can get a button to work, can you assist
-
Jun 26th, 2017, 01:03 PM
#16
Re: VBA for Acces getting End of Statement error in Immedate Window
Remove the "then" from the "SubX =" line
-
Jun 26th, 2017, 01:08 PM
#17
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
-
Jun 26th, 2017, 01:11 PM
#18
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
I removed then and it still says end of statement error
-
Jun 26th, 2017, 01:15 PM
#19
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
what does immediate window want in the private, dim and my SubX statement???
-
Jun 26th, 2017, 01:20 PM
#20
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
and the if statement is saying block if without end if error but I have end if after else
-
Jun 26th, 2017, 01:40 PM
#21
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
what is the code wrapper btw??
-
Jun 26th, 2017, 01:50 PM
#22
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
if 0xD and 0xA are not working for end of statement are there other codes that will work in VBA Access???
-
Jun 26th, 2017, 02:47 PM
#23
Re: VBA for Acces getting End of Statement error in Immedate Window
Originally Posted by erickatd
what is the code wrapper btw??
Look at the menu bar in the Quick Reply "box" (at the top, far right .. it appears as #)
If you click it, it adds [CODE] [\CODE] "tags" .. aka "the code wrapper"
You should then paste your code between the "tags"
You apparently have a function named Nz()
Could you post that code too, as you seem to infer that the problem is occurring there.
Spoo
-
Jun 26th, 2017, 02:51 PM
#24
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
-
Jun 26th, 2017, 02:55 PM
#25
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
that Nz is what coders told me to put to allow for the null value in the rows when the dlookup perform the lookup in the rows
-
Jun 26th, 2017, 02:59 PM
#26
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Should I take Nz out before the DLookup
-
Jun 26th, 2017, 03:05 PM
#27
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Yes thank you thank you that end of statement error went away
-
Jun 26th, 2017, 03:09 PM
#28
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Now the Dim statement and private statement still have end of statement errors that need to be fixed
-
Jun 26th, 2017, 03:10 PM
#29
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
The DLookup keeps asking for this now )
-
Jun 26th, 2017, 03:19 PM
#30
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
SubX iDLookup is asking for way too many ) I don't know what to do here
-
Jun 26th, 2017, 03:36 PM
#31
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Is the DLookup getting stuck so it's asking for ) all the time?????
-
Jun 26th, 2017, 03:41 PM
#32
Re: VBA for Acces getting End of Statement error in Immedate Window
The line in question is invalid
You have a Then on the end of that statement. Either that should not be there or else there should be an IF at the beginning and a matching end if and some point farther down.
-
Jun 26th, 2017, 03:44 PM
#33
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
I removed the then from the DLookup, is that what your talking about
-
Jun 26th, 2017, 03:45 PM
#34
Re: VBA for Acces getting End of Statement error in Immedate Window
Another issue is the use of a string and the isnull() method.
A string can never be null so the test on the string for is null will never execute if there is a null, instead it will crash on the line before when the result is null.
You could try it like this though I have no idea what NZ() is referring to
Code:
Private Sub MustLook()
If IsNull(Nz(DLookup("[NCPDP_ID]", "Main_Credential_Entry_Table", "[NCPDP_ID] =" & [Forms]![Enter New Credentials]![NCPDP_ID]) & ""), "") Then
DoCmd.OpenForm ("Enter New Credentials")
Else
DoCmd.OpenForm ("Update Existing Credentials")
End If
End Sub
-
Jun 26th, 2017, 03:55 PM
#35
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
I was told to use nz for the null value is that not correct
-
Jun 26th, 2017, 04:08 PM
#36
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Okay so now this says compile error expected then or goto
-
Jun 26th, 2017, 04:12 PM
#37
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
How can I get the Then to connect to my Open Form command without the rror
-
Jun 26th, 2017, 04:16 PM
#38
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
Do I say the Then goto DoCmd.Openform(Enter New Credentials)???
-
Jun 26th, 2017, 04:30 PM
#39
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
when I get this working how can I get code to stay in Immediate window so I can have a button working because when I run this code not in immediate window I keeps prompting me to create a macro so the web said run this code in immediate window but I need this function in a button or textbox
-
Jun 26th, 2017, 04:35 PM
#40
Thread Starter
Lively Member
Re: VBA for Acces getting End of Statement error in Immedate Window
My open form stop working this way they were working with my other code
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|