Name Query Not Working

Pestomania

Active Member
Joined
May 30, 2018
Messages
282
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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]

 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
The answer was found!! I placed
Code:
Dim myglobalvariableoh_ref as String
In the incorrect location. Should have been defined in general Decs.

Fixed!!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top