Why Cannot Pass A Variable In A 'for I = ' Statement

[orcinus]

In the below code, I cannot seem to concatenate as:

r!OBJ_RUN = strObjRun & Trim(i)

so that it will pass the variable value:

1stObj (from strObjRun1 = "1stObj")

into the OBJ_RUN field in the tbl_Tracker table.

Is this even possible? Or am I going to actually have to write each instance out and forget using the for i = 1 to x statement?

===============================================
Public Function fcnRunRptTimed()

Dim d As DAO.Database
Dim r As Recordset


Dim strObjTbl As String
Dim strSQL As String
Dim i As Integer

Dim strObjRun1 As String
Dim strObjRun2 As String

strObjRun1 = "1stObj"
strObjRun2 = "2ndObj"

strTbl = "tbl_RptTracker"
strSQL = "select * from " & strTbl

Set d = CurrentDb
Set r = d.OpenRecordset(strSQL, dbOpenDynaset)

For i = 1 To 2

r.AddNew
r!ST = Now()
r!OBJ_RUN = strObjRun & Trim(i)
r.Update

Next i
End Function

[llkhoutx]

Try

r!OBJ_RUN = strObjRun & cstr(i)

[orcinus]

Try

r!OBJ_RUN = strObjRun & cstr(i)

This doesn't work either. I'm thinking this can't be done?

[c_smithwick]

Try specifically declaring your recordset as a DAO.Recordset. It might not be updatable as declared. You are saying it doesn't work. What specific error are you getting? Or is it simply failing to add the records? Is your OBJ_RUN field defined as a text field?

[llkhoutx]

It can be done.

The following is not correct

strTbl = "tbl_RptTracker"
strSQL = "select * from " & strTbl

Set d = CurrentDb
Set r = d.OpenRecordset(strSQL, dbOpenDynaset) 

It should be

strTbl = "tbl_RptTracker"
Set d = CurrentDb
Set r = d.OpenRecordset("tbl_RptTracker", dbOpenDynaset) 

You've misunderstood the meaning of a recordset.

[c_smithwick]

Found it:

Public Function fcnRunRptTimed()

Dim d As DAO.Database
Dim r As Recordset
Dim strObjTbl As String
Dim strSQL As String
Dim i As Integer

Dim strObjRun1 As String
Dim strObjRun2 As String

strObjRun1 = "1stObj"
strObjRun2 = "2ndObj"

strTbl = "tbl_RptTracker"
strSQL = "select * from " & strTbl

Set d = CurrentDb
Set r = d.OpenRecordset(strSQL, dbOpenDynaset)

For i = 1 To 2

r.AddNew
r!ST = Now()
r!OBJ_RUN = strObjRun & Trim(i)
r.Update

Next i
End Function

Look at your code. Since you aren't specifically declaring "strObjRun" as a string variable, the first time you use it, vba is setting it to a new variant variable with a null value assigned by default. Trying to concatenate a Null variant object and a string doesn't work. Try the following:

Public Function fcnRunRptTimed()
Dim d As DAO.Database
Dim r As Recordset
Dim strObjTbl As String
Dim strSQL As String
Dim i As Integer
 
strTbl = "tbl_RptTracker"
strSQL = "select * from " & strTbl
Set d = CurrentDb
Set r = d.OpenRecordset(strSQL, dbOpenDynaset)
 
For i = 1 To 2
    r.AddNew
    r!ST = Now()
    r!OBJ_RUN = CStr(i) & getOrdinalString(i) & "Obj"
    r.Update
Next i
 
End Function
 
Public Function getOrdinalString(intInput As Integer) As String
 
Select Case Mid(CStr(intInput), Len(intInput), 1)
'Evaluates the last digit of the input value
 
Case "1"
getOrdinalString = "st"
Case "2"
getOrdinalString = "nd"
Case "3"
getOrdinalString = "rd"
Case Else
getOrdinalString = "th"
End Select
 
End Function