Results 1 to 7 of 7

Thread: Name Query Not Working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2018
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Name Query Not Working

    Hi. I am trying to use a VBA code to pull initials from a table named "PlannersList". It will then take those initials and insert them into query "schedule" in the "initials" parameter. This process will loop until all initials have been utilized. Everything works except for the initials are not returning results, but if I take away the table request and just type initials, it works. Below is what I have:

    PlannersList Table Fields & Data Type:
    Planner - Text
    PlannerInitials - Text
    DateEdited - Date/Time

    Schedule Query Fields:
    Initials (Parameter = Like findcriteriaOHREF)
    NumberofJobs

    VBA:

    Code:
    Option Compare Database
    
    Public Sub ModifyData()
    
    Dim myRST as Recordset
    Dim myDB as Database
    Dim myGlobalVariableOH_REF as String
    
    Set myDB = CurrentDB
    Set myRST = myDB.OpenRecordSet("PlannersList", dbOpemDynaset)
    Do Until myRST.EOF
    
    myGlobalVariableOH_REF = myRST!PlannerInitials
    DoEvents
    Docmd.OpenReport "Schedule"
    
    myRST.MoveNext
    Loop
    myRST.Closen
    End Sun
    
    Public Function findcriteriaOHREF()
    
    findcriteriaOHREF = myGlobalVariableOH_REF
    
    End Function
    This returns the query with a
    Code:
    #Type!
    Error as the result but no Error code
    I have added * after the variable. I've added "findcriteriaOHREF" but nothing works. The initials are only letters. The type is text but I can't figure out what's going on

    Please help!!
    Last edited by Pestomania; Jun 13th, 2019 at 08:11 AM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Name Query Not Working

    Your code has typos all over the place. Is this really what your code looks like?
    Code:
    Set myRST = myDB.OpenRecordSet("PlannersList", dbOpemDynaset)
    should be "dbOpenDynaset"

    Code:
    myRST.Closen
    End Sun
    should be:
    Code:
    myRST.Close
    End Sub
    If not, and the typos are just in this question, and not really in your project, you should use Copy & Paste to post your actual code here, instead of trying to re-type it. It is hard to identify issues if the code you posted is incorrect and does not reflect what you really have.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    399
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Name Query Not Working

    Apart from the typos - how is your query supposed to get your variable? One is inside a routine the function doesn't know where to find that.

    Try using TempVars:

    Code:
    Do Until myRST.EOF
    TempVars!OHRef = myRST!PlannerInitials
    Docmd.OpenReport "Schedule"
    myRST.MoveNext
    Loop
    Within the criteria for your query you would put. [TempVars]![OHRef]


  4. #4
    Board Regular
    Join Date
    May 2018
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Name Query Not Working

    Hi. Unfortunately, I cannot copy and paste, that would have been much easier. Those are typos only in the question. Autocorrect tried to correct everything, I missed a few.

  5. #5
    Board Regular
    Join Date
    May 2018
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Name Query Not Working

    Quote Originally Posted by stumac View Post
    Apart from the typos - how is your query supposed to get your variable? One is inside a routine the function doesn't know where to find that.

    Try using TempVars:

    Code:
    Do Until myRST.EOF
    TempVars!OHRef = myRST!PlannerInitials
    Docmd.OpenReport "Schedule"
    myRST.MoveNext
    Loop
    Within the criteria for your query you would put. [TempVars]![OHRef]


    Unfortunately this didn't work ☹️

    It returned the same error.

  6. #6
    Board Regular
    Join Date
    May 2018
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Name Query Not Working

    The answer was found!! I placed
    Code:
    Dim myglobalvariableoh_ref as String
    In the incorrect location. Should have been defined in general Decs.

    Fixed!!

  7. #7
    Board Regular
    Join Date
    Jul 2010
    Posts
    399
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Name Query Not Working

    Glad you got the answer.

    Are you using access pre-2007? The TempVars way of doing this is basically the same thing except does not require a function to get the variable or for the variable to be declared independently. It works post 2007, i use it quite often without issue.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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