Name Query Not Working

Pestomania

Board Regular
Joined
May 30, 2018
Messages
138
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:

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,045
Office Version
365
Platform
Windows
Your code has typos all over the place. Is this really what your code looks like?
Code:
Set myRST = myDB.OpenRecordSet("PlannersList", [COLOR=#ff0000]dbOpemDynaset[/COLOR])
should be "dbOpenDynaset"

Code:
myRST.[COLOR=#ff0000]Closen[/COLOR]
End [COLOR=#ff0000]Sun[/COLOR]
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.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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
[COLOR=#ff0000]TempVars!OHRef = myRST!PlannerInitials[/COLOR]
Docmd.OpenReport "Schedule"
myRST.MoveNext
Loop
Within the criteria for your query you would put. [TempVars]![OHRef]

 

Pestomania

Board Regular
Joined
May 30, 2018
Messages
138
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.
 

Pestomania

Board Regular
Joined
May 30, 2018
Messages
138
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
[COLOR=#ff0000]TempVars!OHRef = myRST!PlannerInitials[/COLOR]
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.
 

Pestomania

Board Regular
Joined
May 30, 2018
Messages
138
The answer was found!! I placed
Code:
Dim myglobalvariableoh_ref as String
In the incorrect location. Should have been defined in general Decs.

Fixed!!
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,840
Messages
5,446,817
Members
405,416
Latest member
galoli

This Week's Hot Topics

Top