1

Simple question, but I am new to databases. I have a field called 'ActorRetired?', and in some VBA code I need to set the value of it to -1. But When I try:

Me.ActorRetired?.Value = -1

I get the error 'Compile Error: Expected: Expression' with the question mark highlighted.

How do I refer to the field name without this error? So far I have tried

Me.[ActorRetired?].Value = -1
Me."ActorRetired?".Value = -1

But neither has worked. Please help!

4
  • Try Me.ActorRetired_.value. But I'd recommend you to rename your control/column to some name which does not contain problematic chars, for example, to DoesActionRetired.
    – Akina
    Commented Oct 6, 2020 at 8:30
  • @Akina Doing that gives me 'Method or data member not found' and there's a lot of things I would have to change if I changed the field name, but if there's no other solution there I will. Commented Oct 6, 2020 at 8:44
  • In VBA (form module) type Me. - intellisense will suggest all possible variants, look in the list for your column name as it is interpreted by VBA. When I create a table with the column named ActorRetired?, then create a form with this table as a rowsource using master, and then try to refer to the column's value in Form_Current - shown expression works correctly. So describe detailed what do you do...
    – Akina
    Commented Oct 6, 2020 at 8:48
  • You might need to use Me![ActorRetired?] = -1 - it's been a good long while since I used Access, but I remember something along those lines for fields that aren't directly added as an object to the form.
    – Hannah Vernon
    Commented Oct 7, 2020 at 13:39

1 Answer 1

1

You can't have special characters in a VBA name. The bang operator ( the ! in Me![ActorRetired?] = -1) is just a shortcut for string based name lookup. It's not syntactically clear so I recommend using an actual string so it's clear that is what you want to do.

If you want to access the underlying Field using a string you would go through the form recordset like this:

Me.Recordset.Fields.Item("ActorRetired?").Value = -1

Or if you have a control of the same name you can go through the forms control collection like this:

Me.Controls.Item("ActorRetired?").Value = -1

Not the answer you're looking for? Browse other questions tagged or ask your own question.