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
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