Run-time error '3265' while using dynamic variable names

[Sunnray]

I get an error on line . I have an array of variables in my table and access them using the for loop, see . It does not seem to be working at the moment. The online example I used wasn't doing the same thing. Am I not accessing it correctly?

Private Sub btnBuild_Click()
Dim counter As Integer, thisYear As Integer, nextYear As Integer, myVacaLevel As Date, myEarnedCredits As Double
Dim EarnedField As String, UsedField As String, textBalanceField As String, prevBalanceField As String
Dim MyDB As DAO.Database, MyARec As DAO.Recordset, MyDRec As DAO.Recordset, MyVLRec As DAO.Recordset
Set MyDB = CurrentDb

Set MyVLRec = MyDB.OpenRecordset("SELECT vacationBalanceCarryOver FROM tblVacationLog where sin = '" & txtSIN & "' and vacationYear = " & txtCurrentSchoolYear & ";")
If MyVLRec.BOF And MyVLRec.EOF Then ' Empty file
Set MyDRec = MyDB.OpenRecordset("SELECT vacdue FROM dossier where nas = '" & txtSIN & "' and date = #6/30/" & txtCurrentSchoolYear & "#;")
Set MyVLRec = CurrentDb.OpenRecordset("SELECT * FROM tblVacationLog;")
With MyVLRec
.AddNew
![SIN] = txtSIN
![vacationYear] = txtYear
![vacationBalanceCarryOver] = MyDRec!vacdue

For counter = 1 To 12
EarnedField = "vacationCreditsEarned" & Format(counter, "00")
UsedField = "vacationCreditsUsed" & Format(counter, "00")

Set MyPRec = MyDB.OpenRecordset("SELECT p.class, continu, vacjr1, vacjr2, vacjr3, vacan1, vacan2, vacan3 FROM acform a, fonction f, permanen p WHERE a.nas='" & txtSIN & "' and a.nas = p.nas and p.class = fon_no;")
myEarnedCredits = MyPRec![vacjr3] / 12

!Me(EarnedField) = myEarnedCredits
Next counter
.Update
End With
Else
End If

MyDRec.Close
MyVLRec.Close
MyDB.Close
End Sub

 

[Sunnray]

Each record consist of 12 entries for CreditsEarned and CreditsUsed. I have less than a week to wrap everything up so I may remove the loop and hardcode the loop for each month.

I can use Me.Variable to access an object but I'm unable to find the proper syntax for it to reference a field of a table. This may not even be possible.

Any help is greatly appreciated.

 

[pbaldy]

What is the text of that error, and where exactly do you get it? I see you setting variables with the counter but not using them. Offhand those lines look okay.

 

[Sunnray]

Run-time error '3265':
Item not found in this collection.

The error occurs where the is.
!Me(EarnedField) = myEarnedCredits

This table tblVacationLog consist of:
ID
SIN
vacationYear
vacationBalanceCarryOver
vacationCreditsEarned01 - 12
vacationCreditsUsed01 - 12

The last two fields occur 12 times numbered 01 - 12.

I cut out some code that is not relevent to the error in question. Me(EarnedCredits) is vacationCreditsEarned01 but !Me(EarnedCredits) is not an item found in this collection. I have also tried ![Me(EarnedCredits)]. Can I use the variable in this way?

 

[pbaldy]

Ah; are you trying to update the form or the recordset? You want the bang for the recordset or Me for the form, not both. If the recordset, I suspect you'll need to use the fields property.

 

[Sunnray]

' I can use a dynamic variable like this:
Dim dayfield as String, dReportCurrentDate as date, counter as integer
dReportCurrentDate = Now()
For counter = 0 To 30
dayfield = "txtDay" & Format(counter, "00")
Me(dayfield).ControlTipText = ""
Me(dayfield).BackColor = lngRed ' Out on this day
Me(dayfield).Value = Day(dReportCurrentDate + counter)
Next counter

' I suppose instead of having an 2 arrays of 12 variables I should normalize my table.
' The difference will be loading 12 records unto 1 screen.

Questions:

What is the proper term for these Dynamic Fields?
Bind 24 fields from one record or populate 24 fields from 12 records?

 

[The_Doc_Man]

Using the Me(varname) approach requires that varname is set to the text name of a given control on the form. You might also use Me.Controls(varname), same intent.

"Object not in collection" means that varname isn't the name of a control... OR that the name is correct but the object doesn't have a usable .Value property (for example). See, you have TWO collections in play here... the collection of controls implied with the syntax of Me(varname) AND the collection of properties associated with the thing that would be referenced by Me(varname).

The syntax also seems wrong because !Me should be Me! - and if you have something else that has a control actually named "Me" then change that control yesterday. Me is a reserved word in Access, applying to form and report contexts. The only place where that syntax (a leading !) would be legal is inside a "With" statement - but it SAYS that the object named by the With includes a member named ME. I don't think that is what you really meant.

 

[Sunnray]

I understand that Me refers to the form/report.

I am using it within a With statement. I was hoping that I could access multiple fields using 1 variable within a loop. Since this ability is restricted to controls I've modified my tables and changed the subform to have unbound textboxes.

Thanks for the replies

 

[pbaldy]

Like I said, you're combining a reference to the recordset with a reference to the form. You want one or the other. Either

!Whatever

or

Me.Whatever

You can use the variables with either, but the "!Me" is invalid.

 

[The_Doc_Man]

To be explicit, ...

     set rsX = currentDB.OpenRecordset( ... etc )
     with rsX
     ...
     !Me = etc etc etc
     ...
     end with

implies the existence of rsX!Me, which in this context CANNOT be a reference to the form. You have a !Me construct in your code inside your "With" block, and that is wrong about six different ways including abuse of a reserved word. Can't say it any plainer. In a "With" context, !Me is just not gonna work. On the other hand, Me!, even in "With" context, has half a chance of being quite OK.

In isolation, Me![controlname] implies that [controlname] is a control on the currently open form from which the class module is running. Note that in a general module, Me. or Me! doesn't work because Me is a specific shortcut only for forms and reports and only from the context of a class module.

Regarding your error 3265, the object that doesn't appear in a collection is ME - in the collection of the fields that make up the recordset. Because that syntax OVERRIDES the default meaning of ME in VBA class modules.

 

[Sunnray]

Like I said, you're combining a reference to the recordset with a reference to the form. You want one or the other. Either

!Whatever

or

Me.Whatever

You can use the variables with either, but the "!Me" is invalid.

This is what I was trying to get at. I didn't realize that one could use !Whatever
Thanks, I'm sure I'll be using this in the near future.