Using variable for field name on form

  • Thread starter Thread starter J
  • Start date Start date
J

J

On a field I have two text boxes (ABC and DEF). These
text boxes get populated by the user with counts.
Values from frmMain (user enters)
ABC = 100
DEF = 200

Values in MyTable
MyField MyCount
ABC 0
DEF 0

I want to update the MyCount field in MyTable with the
values on the form based on the respective values in the
respective fields

MyArray = Array("ABC", "DEF")
for each item in MyArray
set rst = currentdb.openrecordset("Select * from MyTable
where MyField = '" & item & "'")
With rst
.Edit
!MyCount = forms!frmMain!ABC (or forms!frmMain!DEF
and so on as it goes through the array)
.Update
end with
next item

I've tried setting up a variable for forms!frmMainABC. I
used (var = "forms!frmMain!" & item) and tried declaring
the variable as different things (string, field etc), but
nothing seems to work. I know I can work around this, but
it seems to be more efficient (and require less code) if I
can just assign a variable to the fields on the form b/c
the field names match the id's in the table. Any
suggestions??
 
J said:
On a field I have two text boxes (ABC and DEF). These
text boxes get populated by the user with counts.
Values from frmMain (user enters)
ABC = 100
DEF = 200

Values in MyTable
MyField MyCount
ABC 0
DEF 0

I want to update the MyCount field in MyTable with the
values on the form based on the respective values in the
respective fields

MyArray = Array("ABC", "DEF")
for each item in MyArray
set rst = currentdb.openrecordset("Select * from MyTable
where MyField = '" & item & "'")
With rst
.Edit
!MyCount = forms!frmMain!ABC (or forms!frmMain!DEF
and so on as it goes through the array)
.Update
end with
next item
Click to expand...


!MyCount = forms!frmMain(item)

Be aware that "item" is a reserved word and may be confusing
to both you and to Access
 
Back
Top