Change Subforms Recordset - Runtime

[Ian Mac]

All,

I'm having great difficulty working out the syntax to change the Recordset on a Subform. I'll try my best to explain.

I have a 'Menu' subform which gets it's recordset from an ADO recordset, it's a continuous form using a Textbox for he list of options.

The problem I'm having is, I'm using the same form to display a different list of menu options from a different table, but, the using the same recordset.

Here is the code to create the recordset.

Public Sub GetMenu()

Dim strSQL As String

Call OpenConn

Set rsMenu = New ADODB.Recordset

Select Case intMenu

Case 1
strSQL = "SELECT HeadingID, Heading, Active, HeadingDesc FROM FRKHeading;"
Case 2
strSQL = "SELECT DSO, Active FROM DSO;"

End Select

With rsMenu
    
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockBatchOptimistic
    .Open strSQL, conn
    .ActiveConnection = Nothing
    
End With
    
Call CloseConn

End Sub

When the subform loads it uses the first case as the recordset and I have the Textbox ControlSource as 'Heading'

What I'm trying to do is, when the user click a button rsMenu then picks up the second case from the database and the menu should change.

I have had no problem setting the ControlSource of the textbox to 'DSO' but I can't figure out how to re-Set the Recordset so it knows that 'DSO' is a field which can be used.

I'm assuming this is what I need to do as currently, when the button is clicked is displays #Name?, as the ControlSource has changed no problem but it obviously doesn't see the recordset as having that field in it.

Here is the code (including failed syntax) I am using:

Private Sub cmdDictMan_Click()

intMenu = 2

Call GetMenu

'Set Forms("frmMain").Controls("sfrmMenu").RecordSource = rsMenu
'Set Forms![frmMain]![sfrm1].Recordset = rsMenu

Forms![frmMain].Controls("sfrmMenu").Controls("txtMenuItem").ControlSource = "DSO"
'Forms![frmMain].Controls("sfrmMenu").Requery

End Sub

I hope I've been able to explain my question correctly.

Any help is appreciated.

Thanks,

 

[boblarson]

Forms!YourMainFormNameHere.YourSubformCONTAINERNameHere.Form.Recordsource = rsMenu

And this might help you with how to use subform syntax:

1. When you are dealing with subforms, you are actually dealing with two parts - the subform CONTAINER (that which houses the subform on the main form) and the subform itself.

2. The subform and subform container can be named the same, but are not necessarily so. You need to check before writing the code. If they are the same then it simplifies things but it doesn't really matter if it is, or isn't, because you just have to refer to the container.

3. When you are doing things, like setting the recordsource on the subform, you are not really requerying the container, as it doesn't have a requery method, but the subform itself does. So, when you are referring to a property, or method, on the actual subform (not the container), you need to have the subform container name and then .Form. between the container name and the method, or property, so Access knows you want to refer to the form's method or property and not the container's method or property.

 

[Ian Mac]

Thanks Bob for taking the time to provide an extensive reply.

However the sytax you have provided doesn't appear to be working.
I am actually trying to Set the Recordset of the subform not the RecordSource.

On the form's load event I'm using

Set Me.Recordset = rsMenu

Which there is no problem with

Using the code you have suggested returns an error that I am trying to reference an Object which is closed or doesn't exsist.

The following code to change the ControlSource of a Textbox on the Subform, is working fine:

Forms![frmMain].Controls("sfrmMenu").Controls("txtMenuItem").ControlSource = "DSO"

So the form is open and can be manipulated.

I can only assume that I'm getting #Name? in the fields as the textbox control source has been changed but it needs the ADO recordset to be refreshed to the form as it must currently be looking at the previous incarnation of the Recordset which doesn't contain that field.

Basically I'm trying to re-use the form and re-use the ADO code as I don't how many menus I will need and it means I only have the Select case to update and away I go.

Any suggestions.

Thanks Again,