TextBox as a Sub parameter

I am having a problem using a TextBox as a parameter in a Public Sub. I have tried passing it as a string, as a property of a form and with no luck. I am lost. It compiles but will not run without errors. I am an Access Rookie. Here is the code of the sub:

Public Sub AllCostsMTD(frm As String, tName As String, tboxName As String)
Dim cMTD As Currency
Dim Mos As Integer
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Dim strSQL As String
Mos = Month(Forms(frm).tbDateOfPurchase.Value)
strSQL = "SELECT " & tName & ".[Cost], " & tName & ".[DateOfPurchase] " & _
"FROM " & tName & ";"
Set rst = db.OpenRecordset(strSQL)
cMTD = 0#
If rst.RecordCount = 0 And rst.EOF = True Then
Forms(frm).tboxName.Value = Forms(frm).tbCost.Value
ElseIf rst.RecordCount = 1 And rst.EOF = True Then
If Month(rst!DateOfPurchase) = Mos Then
cMTD = cMTD + rst!Cost
End If
Forms(frm).tboxName.Value = cMTD + Forms(frm).tbCost.Value
ElseIf rst.RecordCount >= 1 And rst.EOF = False Then
'populate rst
rst.MoveFirst
rst.MoveLast
rst.MoveFirst
cMTD = 0#
Do While rst.EOF = False
Debug.Print "Month in Loop: " & Month(rst!DateOfPurchase)
If Month(rst!DateOfPurchase) = Mos Then
cMTD = cMTD + rst!Cost
Debug.Print cMTD
End If
rst.MoveNext
Loop
rst.MoveLast
Debug.Print Forms(frm).tbCost.Value
Forms(frm).tboxName.Value = cMTD + Forms(frm).tbCost
End If
rst.Close
Set rst = Nothing
End Sub

Hi,

You didn't indicate which line is causing the errors but the basic syntax for assigning a value to a textbox on a form using string variables might go something like this:

Forms(frm).Controls(tboxName).Value = "something"

Hope it helps...

Try

Forms(frm).Controls(tboxName).Value

Darn these slow fingers!!

Hi Paul,

pbaldy said:

Darn these slow fingers!!

Click to expand...

It just so happens I am not using my phone today, like I usually do.

Please post lengthy code between CODE tags to retain indentation and readability.

Textbox is not in a subform? Is it bound to field?

Thanks to all!! You all were so fast!! I just read after I posted about Collections which as a Rookie I had not run across. Thanks again to all of you. All of you had the same answer that worked.

Hi,

Glad to hear you got it sorted out. Good luck with your project.