Recordset selected by IF statement

[chappy68]

Can I open the recordset using an IF statement?

For example, if radio button #1 is selected use Set rst = CurrentDb.OpenRecordset("qryMyTasksCurrUser") else use Set rst = CurrentDb.OpenRecordset("qryMyTasksAllUsers").

If yes, to the above question, how do I reference the option group and the choice made? Do I refer to the Option Group or the Option Button?

Do I have to do something to let the Option Group know which button is chosen?

Essentially, my goal is to create a recordset based on the choice (only 2 choices) in the option group.

I am new to option groups and haven't seen much in my limited research so I have no idea how to work with them.

 

[pbaldy]

Sure; you test the frame (group), not the buttons.

If Me.FrameName = 1 Then
  Set rst = CurrentDb.OpenRecordset("qryMyTasksCurrUser")
Else
  Set rst = CurrentDb.OpenRecordset("qryMyTasksAllUsers")
End If

 

[chappy68]

Why won't this work? My treeview is working until I enter this IF statement. I am getting an error 3061 Too few parameters. Expected 1.

The two queries qryMyTasksCurrUser and qryMyTasksAllUsers work when I run them from the Navigation Pane so I know data is in these queries and they work.

Am I missing some code somewhere else? Do I need to do something in the option frame? I am stuck and frustrated.

 

[pbaldy]

Do they have a form parameter in them? If so:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

You can usually also wrap the form reference in the Eval() function.

 

[chappy68]

Are you talking about the two queries? If yes, qryMyTasksCurrUser does refer to a hidden form that holds my login information so I can limit the results to just that user.

Sorry the link you sent is very technical to read. Can you please push me in the right direction. I really did not understand what it was telling me. Below is the code for the recordset and my treeview. Would it be possible for you to help me decipher what was being said.

Private Sub AddAllNodes()
Dim rst As DAO.Recordset

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

' open the recordset

If Me.opgUser = 1 Then
Set rst = CurrentDb.OpenRecordset("qryMyTasksCurrUser")
Else
Set rst = CurrentDb.OpenRecordset("qryMyTasksAllUsers")
End If

' 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
End Sub

 

[pbaldy]

It's telling you that OpenRecordset can't resolve form references in the query. In the query try replacing

Forms!FormName.ControlName

with

Eval('Forms!FormName.ControlName')

 

[chappy68]

I like your explanation much better than Microsofts.

I will give it a try. Thanks again for taking your time to help me.

 

[pbaldy]

No problem. I think I've played golf in Fresno.

 

[chappy68]

I didn't realize your were from Nevada. I have relatives in Las Vegas. I was the CFO of a country club in Fresno. Great place to work until they couldn't pay the bills. Never had a better day than watching the sun come up over the mountains while standing on the dew moist greens. It was cool.

Thanks again for your help. Slowly but surely getting my app ready for work.