Using VBA to call a Query which takes a parameter from a textbox

[tcneo]

Hi,

I'm trying to stretch my VBA skills.

So I wrote the following code;

My first query is intQuery_for_AutoAssignment which returns a table of tasks to be assigned. Fields include the date, analyst_ID and duration.

My first loop runs through each record from the about query in a recordset.

For each record in the above, I tried to call another query (intQuery_for_Analyst_Time_Used) which returns a table of analysts which are available. This query requires a value, the date.

So what i did was to assign the date from the record to a textbox (txtAutoAssignDate) so that when i call the second query, the second query can get the date.

But what I run the above code, i got an error 3061 saying that the line ("Set myS = CurrentDB..... etc) expected a parameter, 1.

Note: MsgBox in the loop to represent what I would do with the records.

        Dim myR As Recordset
        Dim myS As Recordset
             
        Set myR = CurrentDb.OpenRecordset("intQuery_for_AutoAssignment")
    
        Do Until myR.EOF
     
            MsgBox ("The date is " & myR![Task_Detail_Date] & " and the analyst is " & myR![Analyst_ID] & "the duration is" & myR![Duration_of_Task])
         
            'Trying to get analyst availability
            Me.txtAutoAssignDate = myR![Task_Detail_Date]
            Set myS = CurrentDb.OpenRecordset("intQuery_for_Analyst_Time_Used")
         
            Do Until myS.EOF
                MsgBox ("The date is " & myS![Work_Date] & " and the analyst is " & myS![Analyst_Name] & "the availability is" & myS![Available_Duration] & "time left is" & myS![Time_used])
     
                myS.MoveNext
             
            Loop
         
            myR.MoveNext
          
        Loop
     
        ' Close and release Recordset
        myR.Close
        Set myR = Nothing

 

[Gasman]

You would need to show the query "intQuery_for_Analyst_Time_Used"

This might prove useful? http://www.accessmvp.com/thedbguy/codes.php?title=generic

 

[arnelgp]

does "intQuery_for_Analyst_Time_Used" query have a criteria pointing to an Object in a Form?
if the form is not open then surely it will ask for parameter.

your requirement is i think fairly simple and can be done through a simple Insert Query.

 

[tcneo]

does "intQuery_for_Analyst_Time_Used" query have a criteria pointing to an Object in a Form?
if the form is not open then surely it will ask for parameter.

your requirement is i think fairly simple and can be done through a simple Insert Query.

Yes, it has a criteria pointing to a textbox, txtAutoAssignDate. Hence, the following line of code (extracted from above):

Me.txtAutoAssignDate = myR![Task_Detail_Date]
Set myS = CurrentDb.OpenRecordset("intQuery_for_Analyst_Time_Used")

In the above 2 lines, I'm trying to update txtAutoAssignDate with the Date from one of the record from the myR recordset. So that when i SET the recordset for myS, the query, intQuery_for_Analyst_Time_Used, can get it from txtAutoAssignDate.

Unless, there is a way (*) to modify the query so that i can call it with VBA and directly pass it the date as a parameter.

I set the textbox's Visible property to NO, is it an issue?

*: i'm sure there is, just that i do not know it.

 

[tcneo]

You would need to show the query "intQuery_for_Analyst_Time_Used"

This might prove useful? http://www.accessmvp.com/thedbguy/codes.php?title=generic

the SQL for the query is as follows:

SELECT Analyst_Availability.Work_Date, Analyst_Availability.Analyst_ID, Analyst_Details.Analyst_Name, Analyst_Availability.Available_Duration, Nz(Sum([Task_Detail_Table]![Duration_per_Qty]*[Task_Detail_Table]![Qty]),0) AS Time_used, [Analyst_Availability]![Available_Duration]-Nz(Sum([Task_Detail_Table]![Duration_per_Qty]*[Task_Detail_Table]![Qty]),0) AS Time_left
FROM Analyst_Details INNER JOIN (Analyst_Availability LEFT JOIN Task_Detail_Table ON (Analyst_Availability.Analyst_ID = Task_Detail_Table.Analyst_ID) AND (Analyst_Availability.Work_Date = Task_Detail_Table.Task_Detail_Date)) ON Analyst_Details.Analyst_ID = Analyst_Availability.Analyst_ID
GROUP BY Analyst_Availability.Work_Date, Analyst_Availability.Analyst_ID, Analyst_Details.Analyst_Name, Analyst_Availability.Available_Duration
HAVING (((Analyst_Availability.Work_Date)=[Forms]![Main Menu]![txtAutoAssignDate]));

 

[arnelgp]

you need to change some code, for it to work:

...
...
        Do Until myR.EOF
      
            MsgBox ("The date is " & myR![Task_Detail_Date] & " and the analyst is " & myR![Analyst_ID] & "the duration is" & myR![Duration_of_Task])
          
            'Trying to get analyst availability
            ' arnelgp
            '
            With CurrentDb.QueryDefs("intQuery_for_Analyst_Time_Used")
                .Parameters(0) = CDate(myR![Task_Detail_Date])
                Set myS = .OpenRecordset(dbOpenDynaset)
            End With
            'Me.txtAutoAssignDate = myR![Task_Detail_Date]
            'Set myS = CurrentDb.OpenRecordset("intQuery_for_Analyst_Time_Used")
          
            Do Until myS.EOF
                MsgBox ("The date is " & myS![Work_Date] & " and the analyst is " & myS![Analyst_Name] & "the availability is" & myS![Available_Duration] & "time left is" & myS![Time_used])
      
                myS.MoveNext
              
            Loop
          
            myR.MoveNext
          
        Loop
...
...

 

[tcneo]

you need to change some code, for it to work:

...
...
        Do Until myR.EOF
     
            MsgBox ("The date is " & myR![Task_Detail_Date] & " and the analyst is " & myR![Analyst_ID] & "the duration is" & myR![Duration_of_Task])
         
            'Trying to get analyst availability
            ' arnelgp
            '
            With CurrentDb.QueryDefs("intQuery_for_Analyst_Time_Used")
                .Parameters(0) = CDate(myR![Task_Detail_Date])
                Set myS = .OpenRecordset(dbOpenDynaset)
            End With
            'Me.txtAutoAssignDate = myR![Task_Detail_Date]
            'Set myS = CurrentDb.OpenRecordset("intQuery_for_Analyst_Time_Used")
         
            Do Until myS.EOF
                MsgBox ("The date is " & myS![Work_Date] & " and the analyst is " & myS![Analyst_Name] & "the availability is" & myS![Available_Duration] & "time left is" & myS![Time_used])
     
                myS.MoveNext
             
            Loop
         
            myR.MoveNext
         
        Loop
...
...

thanks! can you briefly explain why the code has to be changed to this in order for it to work?

 

[theDBguy]

Hi. Just curious, did you try the generic function linked to in post #2?

If you do, all you have to do is change this line:

Set myR = CurrentDb.OpenRecordset("intQuery_for_AutoAssignment")

into this:

Set myR = fDAOGenericRst("intQuery_for_AutoAssignment")

Hope that helps...

 

[tcneo]

Hi. Just curious, did you try the generic function linked to in post #2?

i didn't. seems like a lot of lines and i don't really understand what is happening.

 

[theDBguy]

i didn't. seems like a lot of lines and i don't really understand what is happening.

Hi. I edited my post above. It should be very simple to use. Please review my edited post above and post any questions or clarifications you need explained.

 

[theDBguy]

i didn't. seems like a lot of lines and i don't really understand what is happening.

By the way, I can understand not wanting to use code you don't understand. However, if you're willing, you can give the function a try first, just to verify if it works for you or not. Then, if you decide you want to keep using it, we can then explain how it works to help you understand its code.

 

[Gasman]

thanks! can you briefly explain why the code has to be changed to this in order for it to work?

Beacuse as you have found out, when using a query for a recordset, the parameters are not available directly?

 

[tcneo]

Hi. I edited my post above. It should be very simple to use. Please review my edited post above and post any questions or clarifications you need explained.

so i have to put the fDAOgenericRst() code in a code module, and then call it (change the line as you mentioned in your earlier post) instead of OpenRecordSet, right?

Thanks!

 

[theDBguy]

so i have to put the fDAOgenericRst() code in a code module, and then call it (change the line as you mentioned in your earlier post) instead of OpenRecordSet, right?

Thanks!

Yes, that's right. Just make sure you don't name the module fDAOGenericRst. You can call it Module1 or something else.