VBA processing a field in a Query that needs a parameter (1 Viewer)

[Strike_Eagle]

Hello again everyone.

I have a series of queries that are used in various forms and reports. Two of my queries are specialistLookupQuery and countOfSpecialistLookupQuery. While I know I can generally get away with one query for this, they are used for many different reasons throughout the database, plus, thats how my boss wants it (not a school project).

The problem:

According to the facility that logs in (stored in a [TempsVar]![varFacility]) my above two queries have different records. One facility might have 180 people, another 250, and each facility changes each day, so nothing static. I need to pull a random 10% of the people for each facility for evaluation.

How do I open the count query and get the number (countOfSpecialistLookupQuery with a CountOfLastName field) stored in its only field and process it in my VBA code? I will also need to store the names generated in the original query (specialistLookupQuery) in an array and use the random generator to pick ten percent.

I can great a random object in VBA no problem. I cna create an array no problem. I can use the random numbers generated (loops and all that jazz). I just can't figure out how to get that varFacility variable into my query from VBA. Here's what I have so far:

Const constantQueryName As String = "countOfSpecialistLookupQuery"
Dim database As DAO.database
Dim recordset As DAO.recordset
Dim queryDef As queryDef
Dim parameter As parameter
Dim recordsToUse As Integer
 
Set database = CurrentDb
Set queryDef = database.QueryDefs(constantQueryName)
Set recordset = queryDef.OpenRecordset()
 
parameter.Value = varFacility
 
recordsToUse = recordset![CountOfLastName]
 
MsgBox "You have " & recordsToUse & " records."
 
recordset.Close
database.Close

 

[AccessMSSQL]

You can try modifying your query in code instead of using a parameter value but you will have to build the SQL for your query dynamically and then use this function to modify the query sql.

Just pass the queryname and the sql string to the function.

Public Sub ModifyQuery(p_qName As String, p_sql As String)
Dim l_db As DAO.Database
Dim qdef As DAO.QueryDef
    On Error GoTo ModifyQuery_err
 
 
 Set l_db = CurrentDb
    Set qdef = l_db.QueryDefs(p_qName)
    qdef.SQL = p_sql
    qdef.Close
    Set qdef = Nothing
    Set l_db = Nothing
    'Set l_ws = Nothing
ModifyQuery_exit:
Exit Sub
ModifyQuery_err:
MsgBox "Modify Query Error: " & Error$
GoTo ModifyQuery_exit

 

[Strike_Eagle]

Ok, not sure I follow, here are my two queries.

specialistLookupQuery:

SELECT QA_NTIDLookup.LastName, QA_NTIDLookup.FirstName, QA_NTIDLookup.Facility
FROM ([User Information List] INNER JOIN QA_Supervisors ON [User Information List].ID = QA_Supervisors.[Created By]) INNER JOIN QA_NTIDLookup ON [User Information List].ID = QA_NTIDLookup.[Created By]
GROUP BY QA_NTIDLookup.LastName, QA_NTIDLookup.FirstName, QA_NTIDLookup.Facility
HAVING (((QA_NTIDLookup.Facility)=[TempVars]![varFacility]))
ORDER BY QA_NTIDLookup.LastName;

countOfSpecialistLookupQuery

SELECT Count(specialistLookupQuery.LastName) AS CountOfLastName
FROM specialistLookupQuery;

The first is the one that needs the parameter, but the second has the count I need. How do I apply your method to my query. Sorry, I am still learning VBA and some of your code is very foreign to me.

 

[AccessMSSQL]

So you are trying to get varFacility into the first query as the parameter so here is how you would do that - not sure where you are setting varFacility in your code

parameter.Value = varFacility

- is this a variable you already set previously someplace?

Here is my code:

Dim strSQL as string
strsql = "SELECT QA_NTIDLookup.LastName, QA_NTIDLookup.FirstName, QA_NTIDLookup.Facility"
strsql = strsql & " FROM ([User Information List] INNER JOIN QA_Supervisors ON [User Information List].ID = QA_Supervisors.[Created By]) INNER JOIN QA_NTIDLookup ON [User Information List].ID = QA_NTIDLookup.[Created By]"
strsql = strsql & " GROUP BY QA_NTIDLookup.LastName, QA_NTIDLookup.FirstName, QA_NTIDLookup.Facility"
strsql = strsql & " HAVING (((QA_NTIDLookup.Facility)=" &[COLOR=red] varFacility[/COLOR] & "))"
strsql = strsql & " ORDER BY QA_NTIDLookup.LastName;"
 
'then you would use my function above - which you should paste in a new module, like this:
ModifyQuery("countOfSpecialistLookupQuery",strsql)
This will put your varFacility value in the query.

I hope I am understanding your need correctly.

 

[Strike_Eagle]

yes, I actually set my varFacility on another form and carried it over to this form, globally, using the [TempsVar] function. Your solution seems reasonable, let me try it out.

Before I do, though, I want to make sure I understand this correctly, the

ModifyQuery("countOfSpecialistLookupQuery",strsql)

is to be placed in its own module, correct?

 

[AccessMSSQL]

Yes, not that specific line of code but the actual procedure that I posted earlier. As long as it is declared as Public you should be able to access it from any module.

 

[Strike_Eagle]

I see, ModifyQuery is just a way to call the function, or sub. Ok, let me give it a shot and I will get back to you. Either way, I appreciate your help and time!

 

[Strike_Eagle]

Ok, I know I had to do something wrong. I used your function, and have been studying it, and I think I can see the logic used there. My error is that when my code

Debug.Print ModifyQuery("countOfSpecialistLookupQuery", strSQL)

to call your above function keeps giving me the error "Expected function or variable"

Is there something I am missing here?

 

[AccessMSSQL]

what are you trying to debug.print? You can't use debug.print that way. The ModifyQuery doesn't return anything- it just modifies your query "countOfSpecialistLookupQuery".

 

[Strike_Eagle]

Ohh, lol, ok. I was just trying to view the outcome for testing so I could move on to the next step. I guess the only way to view that would be to DoCmd.RunSQL

With those changes it still tells me "Expected Function or variable"

 

[AccessMSSQL]

Oh..didn't realize you were getting an error. so you need to debug.print strSQL and then copy the statement from your immediate window and paste it into a query to see where the error is occurring. You should put the debug.print before the ModifyQuery Statement

 

[Strike_Eagle]

Yes, sorry, the error is that it isn't understanding the ModifyQuery is a function. Even though I have it declared Public as

Public Sub ModifyQuery(p_qName As String, p_sql As String)

Either way, I just need that one number from that one field of that one query. There has to be a better way. I think its time to go another route until I can learn more VBA. I do appreciate your help though and thank you.

 

[nanscombe]

I think the zipped database (attached) may illustrate what you're trying to achieve?

I have a form frmVars which has a comboBox to pick a facility.

The value is picked up by a function, varFacility(), (defined in a module) to be passed into the query.

If the facility is a string then it would be....

Public Function varFacility()
varFacility = Nz(Forms("frmVars").cboFacility, "")
End Function

If the facility is a number then it would be....

Public Function varFacility()
varFacility = Nz(Forms("frmVars").cboFacility, 0)
End Function

The query then uses that function as one of it's criteria.

SELECT QA_NTIDLookup.LastName, QA_NTIDLookup.FirstName, QA_NTIDLookup.Facility
FROM ([User Information List] INNER JOIN QA_Supervisors ON [User Information List].ID = QA_Supervisors.[Created By]) INNER JOIN QA_NTIDLookup ON [User Information List].ID = QA_NTIDLookup.[Created By]
GROUP BY QA_NTIDLookup.LastName, QA_NTIDLookup.FirstName, QA_NTIDLookup.Facility
HAVING (((QA_NTIDLookup.Facility)=[B]varFacility()[/B]))
ORDER BY QA_NTIDLookup.LastName;

I've also included an example of a piece of code (defined in a module) that will grab the result from the query as well.

Private Sub cmdRunCountOfQuery_Click()
Dim lngCount As Long

' The variable (Long)  lngCount is derived by using the in-built function
'  DCount, which returns the number of records returned,

lngCount = DCount("LastName", "specialistLookupQuery")

' Open the count query to see the result
DoCmd.OpenQuery "countOfSpecialistLookupQuery"
DoEvents

' Output the result of the recordcount in a Msgbox
MsgBox "Records returned: " & lngCount
End Sub

I hope this helps.

 

[Strike_Eagle]

Thank you nanscombe. That was a well recieved answer. I simply needed a number to do the math off of later.

    Dim lngCount As Long
    
    lngCount = DCount("lastName", "specialistLookupQuery")
    DoEvents
    
    MsgBox "Records returned: " & lngCount

The above code was actually all I used, as it returned the information needed. What I plan on doing is randomizing and gettining 10% of the rows of the one query (which is why I created the count query) and dumping them into a temporary table. Those would then be used in another table later.

Accessmssql, I want to thank you too. I think I overcomplicated the question in order to fullfil the as much detail as possible request that people usually have when trying to assist. While your answer is great, I couldn't get it to work for me. Thank you both!