Changing Field ControlSource on the fly

[n2ridn]

How do I change the controlsource on the fly. In my code, I have fields that are named "qty#". Where the # is incremented based on the recordset where the quantity is greater than 0. I cannot address the controlsource property by putting the quantity in a for..next loop.

The error I am receiving is that the Function has a syntax error. However, I cannot figure out how to setup the reference to the controlsource in the function.

Any assitance would be appreciated.

Here is a sample of the code.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo errReport_Open
Dim intI As Integer
Dim strSQL As String
Dim con As Object
Dim rs As Object
Dim strField As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM [tblShipInventory];"
rs.Open strSQL, con, 1

For intI = 1 To 60
strField = "chkOpt" & intI
If (rs!chkOpt1) > 0 Then
FillControlSource (strField)
End If
If (rs!chkOpt2) > 0 Then
FillControlSource (strField)
End If
If (rs!chkOpt3) > 0 Then
FillControlSource (strField)
End if
Exit Sub


Private Function FillControlSource(strField As String)
On Error GoTo errFillem
Dim strFD As String
strFD = (strField)
Reports!rptinventorycounts!strFD.ControlSource = strField
Next intI
Exit Function

 

[Crowley16]

your code is a total mess...

the Next intI doesn't make sense since you're not using a For Loop.

it looks like you are trying to declare a function in the middle of another function, and not terminating either properly...

--------------------
Procrastinate Now!