Assign recordset - subform

L

LisaB

I created a table with my form name, subform name and sql. I'm trying to
look thru and assign the recordset to the form and each subform. I cannot
get the recordset to assign to the subform.
Thank You!

Private Sub cmbx_select_group_AfterUpdate()
Dim str_formname As Form
Dim str_subform_name As Form
Dim str_form As String
Dim str_subform As String

If IsNull(Me.cmbx_select_group.Column(1)) Then
MsgBox "Please select a group.", vbCritical
Me.cmbx_select_group.SetFocus
Else
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
'Use the ADO connection that Access uses
Set cn = CurrentProject.AccessConnection

'Create an instance of the ADO Recordset class, and
'set its properties
Set rst_recordsets = CurrentDb.OpenRecordset("SELECT FormName,
FormSQL_1, FormSQL_2, sql_variable, sub_form" & _
" FROM tbl_recordsets;")
With rst_recordsets
.MoveLast
.MoveFirst
Do While Not .EOF()
str_form = !FormName
'Here checking to see if subform - if not the code below works to assign the
recordset to the main form.
If Not IsNull(!sub_form) Then
str_subform = !sub_form
'I've tried a varation of things here but nothing seemed to work
Set str_formname = Forms(str_form)
Set str_formname = Forms(Me.[subfrm_GroupPlan_Reports_Edit])
Set str_subform_name = Me(str_subform)
Else
Set str_formname = Forms(str_form)
End If
str_formsql = !formsql_1
str_formsql_2 = !formsql_2
str_formvar = !sql_variable

Set rs = New ADODB.Recordset
str_id = Me.cmbx_select_group.Column(1)
With rs
Set .ActiveConnection = cn
.Source = str_formsql & str_id & str_formsql_2
MsgBox .Source
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set str_formname.Recordset = rs
Set rs = Nothing
Set cn = Nothing
.MoveNext
Loop
End With
End If
 
L

LisaB

I'm getting the error - can't find the form 'subform_name' referred to in the
macro expression name or Visual Basic code. What is the proper syntax for
the subform name?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top