Name Query Not Working

[Pestomania]

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:

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

#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!!

 

[Joe4]

Your code has typos all over the place. Is this really what your code looks like?

Set myRST = myDB.OpenRecordSet("PlannersList", [COLOR=#ff0000]dbOpemDynaset[/COLOR])

should be "dbOpenDynaset"

myRST.[COLOR=#ff0000]Closen[/COLOR]
End [COLOR=#ff0000]Sun[/COLOR]

should be:

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]

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:

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]

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]

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:

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]

The answer was found!! I placed

Dim myglobalvariableoh_ref as String

In the incorrect location. Should have been defined in general Decs.

Fixed!!

 

[stumac]

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.