Page 1 of 4 1234 LastLast
Results 1 to 40 of 124

Thread: VBA for Acces getting End of Statement error in Immedate Window

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: VBA for Acces getting End of Statement error in Immedate Window

    IIF is not correct, should be IF

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    ok thanks

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  7. #7
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    Quote Originally Posted by Spooman View Post
    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

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    I put in codes 0xD and 0xA and it didn't work

  13. #13
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    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

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  16. #16
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: VBA for Acces getting End of Statement error in Immedate Window

    Remove the "then" from the "SubX =" line

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    ok will do just that

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    I removed then and it still says end of statement error

  19. #19

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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???

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  21. #21

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    what is the code wrapper btw??

  22. #22

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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???

  23. #23
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    Re: VBA for Acces getting End of Statement error in Immedate Window

    Quote Originally Posted by erickatd View Post
    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

  24. #24

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    ok thank you very much

  25. #25

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  26. #26

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    Should I take Nz out before the DLookup

  27. #27

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    Yes thank you thank you that end of statement error went away

  28. #28

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  29. #29

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    The DLookup keeps asking for this now )

  30. #30

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  31. #31

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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?????

  32. #32
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  33. #33

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  34. #34
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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

  35. #35

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  36. #36

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    Okay so now this says compile error expected then or goto

  37. #37

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  38. #38

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    Re: VBA for Acces getting End of Statement error in Immedate Window

    Do I say the Then goto DoCmd.Openform(Enter New Credentials)???

  39. #39

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

  40. #40

    Thread Starter
    Lively Member
    Join Date
    Jun 2017
    Posts
    96

    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

Page 1 of 4 1234 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width