Edit field in VBA where field name is a variable

[5529]

Apologies if this is obvious, but I've been struggling to figure out how to do it...

I have a form and a VBA event in which I would like to update a field in the current record. However, the field name is stored as a variable so I can't just use Me!FieldName = __

I've tried simply the following, but get a "No current record" error. I appreciate why I'm getting the error, but how do I select the record currently visible in the form to edit?

foo = "bar"
Me.Recordset.Edit.Fields(foo) = True

 

[Galaxiom]

Me.Recordset.Fields(foo) = True

 

[pbaldy]

Or instead of:

Me!FieldName =

you can use

Me(foo) =

 

[Galaxiom]

you can use

Me(foo) =

That expression will return the value in a control if there is one by that name on the form. If there is none then it will return the field from the form's recordset.

If the recordset is what is required and there are both controls and fields by that name then recordset needs to be specified.

 

[pbaldy]

I was addressing:

However, the field name is stored as a variable so I can't just use Me!FieldName = __

Which I assumed meant that the first effort was simply setting the value on the form, which that can do.

 

[Galaxiom]

Yes. The question became more ambiguous with this line:
Me.Recordset.Edit.Fields(foo) = True

(How they got "No Current Record" with that one is a mystery to me too.)

It would really help if everyone understood the difference between fields and controls and how to address them. That was the main purpose of my comment.

 

[5529]

Yeah, this was the first time I was trying to edit a field when there was no corresponding control in the form, so I managed to confuse myself a bit. Thank you both for your help.

Me.Recordset.Edit.Fields(foo) = True
gave me the error "Update or CancelUpdate without AddNew or Edit"

but simply Me(foo) worked. Can't believe I hadn't thought to try that.

Many thanks again!

 

[pbaldy]

Happy to help!