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.
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.
Code:
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
Last edited: