Forms ControlSource with DAO

[Ryan]

I have this code in the load event of my form.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim q As QueryDef
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDBPD")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*Financial Class" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Me.frmFCDBTotal.ControlSource = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
Me.DB1.ControlSource = rst.Fields(i).Name
Case 2
Me.lbl3.Caption = rst.Fields(i).Name
Me.DB2.ControlSource = rst.Fields(i).Name
Case 3
Me.lbl4.Caption = rst.Fields(i).Name
Me.DB3.ControlSource = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
If Len(Me.lbl3.Caption) < 2 Then
Me.lbl3.Visible = False
Me.DB2.Visible = False
End If
If Len(Me.lbl4.Caption) < 2 Then
Me.lbl4.Visible = False
Me.DB3.Visible = False
End If
End Sub

If I set the forms control source to AllBalancesByFCandDBPD then the data
displays, but if I dont assing a control source to the form then no #name
displays in the textboxes. The labels still get their values, but not the
textboxes. Do I have to set the forms control source? I have tried to
include it in the event like this
Me.Form.ControlSource = rst
but that didnt work. This form has been giving me hell for about a week
now, and the control source seems to have a mind of its own. Please help!

 

[OssieMac]

Hi Ryan,

Untested but I don't think you can use Me.Form.ControlSource = rst. You need
a string name. I am assuming that this name can change so you want to be able
to use a variable name and that is the reason you are not simply using

Me.Form.ControlSource = "AllBalancesByFCandDBPD"

if this is the case then near the top of your code try something like this

Dim strCtrlsource as string

strCtrlsource = "AllBalancesByFCandDBPD"

You should then be able to use it in the following line

Set qdf = db.QueryDefs(strCtrlsource)

and also use it to set the controlsource like this

Me.Form.ControlSource = strCtrlsource