Recordset issue

[chappy68]

I am setting up treeview on a form which has not given me any problems. I made one small change and now I am having issues. All I am doing is using an option group with just two options (current user and all users). Current user has an option value of 1 and all users has an option value of 2. All I did was take my treeview code (which was working) and insert it into an IF statement. Seems easy in my head. I am getting an error of 3061 Too Few Parameters. Expected 1.

My code is below. Please help. I have hi-lited the error line (in red) the debugger is stopping on. By the way, I am able to open the query in Access 2007 and get results so there are records matching my criteria.

Private Sub AddAllNodes()

If Me.optCurrUser.OptionValue = 1 Then ' Checking to see which option box is checked.
Dim rst As DAO.Recordset

Dim strStatusNodeKey ' key for this STATUS node
Dim strOldStatusKey As String ' for detecting change in STATUS

' open the recordset
Set rst = CurrentDb.QueryDefs!qryMyTasksCurrUser.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF

strStatusNodeKey = rst!StatusNodeKey

If strStatusNodeKey <> strOldStatusKey Then ' check for change in category
' change in Status-add Status node
Me.tvMyTasks.Nodes.Add Text:=rst!TaskCatName, Key:=strStatusNodeKey
strOldStatusKey = strStatusNodeKey ' remember this as the current key for detecting changes
End If

' now add Client Name node
Me.tvMyTasks.Nodes.Add Relationship:=tvwChild, Relative:=strStatusNodeKey, _
Text:=rst!ClientName, Key:=rst!ClientNameNodeKey

rst.MoveNext ' next record in qeury
Loop
Else
' If option button for "All Users" is selected run this code. It sets up all the nodes
' for all records not closed in tblTaskDetails

Dim rs As DAO.Recordset

Dim stStatusNodeKey ' key for this STATUS node
Dim stOldStatusKey As String ' for detecting change in STATUS

' open the recordset
Set rs = CurrentDb.QueryDefs!qryMyTasksAllUsers.OpenRecordset

' loop through the rows in the recordset
rs.MoveFirst
Do Until rs.EOF

stStatusNodeKey = rs!StatusNodeKey

If stStatusNodeKey <> stOldStatusKey Then ' check for change in category
' change in Status-add Status node
Me.tvMyTasks.Nodes.Add Text:=rs!TaskCatName, Key:=stStatusNodeKey
stOldStatusKey = stStatusNodeKey ' remember this as the current key for detecting changes
End If

' now add Client Name node
Me.tvMyTasks.Nodes.Add Relationship:=tvwChild, Relative:=stStatusNodeKey, _
Text:=rs!ClientName, Key:=rs!ClientNameNodeKey

rs.MoveNext ' next record in qeury
Loop
End If
End Sub

 

[boblarson]

Change

Set rst = CurrentDb.QueryDefs!qryMyTasksCurrUser.OpenRecordset

to

Set rst = CurrentDb.OpenRecordset("qryMyTasksCurrUser")

But as a learning thing - you don't refer to querydefs with the bang. It would be

Currentdb.QueryDefs("qryMyTasksCurrUser")

 

[chappy68]

But as a learning thing - you don't refer to querydefs with the bang. It would be

Currentdb.QueryDefs("qryMyTasksCurrUser")

I appreciate the help. I will make the changes. Just out of curiosity, is using the bang less efficient or it just won't work? The only reason I ask is because, before I added the code for the IF, it worked correctly.

 

[boblarson]

Well, I may be mistaken then. Perhaps you can use the bang with QueryDefs because it is a collection. So I take that part back.

 

[chappy68]

Unfortunately, it did not work either. Even when I eliminated all the IF coding, it still didn't work as it had before. I went back to my original copy and am starting from scratch. I think I will try a Select Case and try to get it to work. I have never used the Select/Case functionality so that will be fun. I would have thought the IF would have worked but oh well.

Thanks for your help.