Jul 18 2021 12:42 PM
Here is my code:
Public Function fnGeneral
Dim rs As DAO.Recordset
Dim ss As String
Dim aLetter(1 To 14) As String
Dim lGrid As Long
Dim iLetter As Long
Dim sField As String
' Set up array of 14 letters
aLetter(1) = "A": aLetter(2) = "B": aLetter(3) = "C": aLetter(4) = "D"
aLetter(5) = "E": aLetter(6) = "F": aLetter(7) = "G": aLetter(8) = "H"
aLetter(9) = "I": aLetter(10) = "J": aLetter(11) = "K": aLetter(12) = "L"
aLetter(13) = "M": aLetter(14) = "N"
' Open grid table for entry
ss = "Select * from tGrid;"
Set rs = CurrentDb.OpenRecordset(ss, dbOpenDynaset)
For iLetter = 1 To 14
lGrid = lGrid + 1
With rs
.AddNew
!sField = aLetter(iLetter) & lGrid
.Update
End With
Stop
Next iLetter
MsgBox "Finished updating table tGrid!"
End Function
' Problem is: Although sField is correctly set to a field name in table tGrid called A1,
' I get the runtime error "Item not found in this collection" on the line !sField = aLetter(iLetter) & lGrid
' when attempting to update field A1. What can I do to fix the field reference?
Jul 18 2021 01:26 PM - edited Jul 18 2021 01:27 PM
To be honest, this is pretty opaque to me. What is the goal?
That said, I can see at least one potential place where it goes wrong.
You define a string called "SS" and assign a SQL Statement to it. Then use it to open a recordset. All standard stuff.
However, you do NOT specific which fields to retrieve from the table called tGrid. By using the wildcard, your recordset opens with as many fields are are already in tGrid, whether 1 or 26 or any number in between.
ss = "Select * from tGrid;"
Set rs = CurrentDb.OpenRecordset(ss, dbOpenDynaset)
Later you try to add a new record and assign a specific value to one of those fields:
!sField = aLetter(iLetter) & lGrid
That means your table tGrid has to have field "sField" in it, because you are trying to put the specific value--resuling from the concatenation of a value from the array and the current value of the LGrid variable--into the field called "sField".
Is that really the goal? If not what is the goal?
Thanks.
Jul 18 2021 06:21 PM
Thanks George. You have it correct about my goal. If you run my code you get a "Not in collection" error at the place you discussed. The variable has the correct field name , A1, as its value, but produces the error when you use that sField variable fot updating. It seems to me the local variable is being taken literally instead of being able to represent the value of the FieldName in the updating process.
Jul 18 2021 10:31 PM
Jul 19 2021 06:10 AM - edited Jul 19 2021 06:12 AM
I see. Thanks for the clarification.
If you want to address variable fields, you have to use the Recordset.Fields(x) collection, where x is either the index for the field in question, or the name of that field.
Jul 19 2021 09:21 AM
Jul 19 2021 05:16 PM
Jul 19 2021 06:35 PM