Forum Discussion

Copper Contributor

Jul 18, 2021

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

access

George_Hepworth

Silver Contributor

Jul 18, 2021

Jeo0o7

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.

Jeo0o7

Copper Contributor

Jul 19, 2021

George_Hepworth

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.

George_Hepworth
Silver Contributor
Jul 19, 2021
Jeo0o7

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.
- Jeo0o7
  Copper Contributor
  Jul 19, 2021
  Thanks so much for the replies, George. I know you are correct! I understand, and will work on it that way!
  - Jeo0o7
    Copper Contributor
    Jul 20, 2021
    All I needed was the correct way of referencing:
    Also, wild card in sql statement was ok to use.
    Thanks for everything, George!
    sField = "A1"
    With rs
    .Edit
    .Fields(sField) = "B"
    .Update
    End With
Jeo0o7
Copper Contributor
Jul 19, 2021
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