Forum Discussion
How do I refer to a table field name in vba code during update process?
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?
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.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.
- My goal is to dynamically create with vba code the left (assignment) side to name table fields that accept data on the right side. That is why the tGrid table actual field names are A1, A2, B1, B2, etc. -- to facilitate vba code
