Share via

Access 2010: Error 2447 when trying to access control value property on new record

Anonymous
2014-03-13T19:57:00+00:00

I have an Access database with a data-bound form. The database is in the old .mdb format, but I'm using it with Access 2010. I want code to run on the GotFocus event of certain controls (combo boxes and text boxes) that sets the control value to a default if it's not already set. Here's what I've tried:

Private Sub Office_Unit_GotFocus()

    If Me.Office_Unit.DefaultValue <> "" And IsNull(Me.Office_Unit.Value) Then

        Me.Office_Unit = Me.Office_Unit.DefaultValue

    End If

End Sub

If I'm in an existing (previously saved) record, this works correctly. However, when I'm in a new record, I get the following error message:

Run-time error '2447': There is an invalid use of the dot (.) or ! operator or parentheses.

If I debug at this point, I'm able to retrieve the DefaultValue property of the control

Me.Office_Unit.DefaultValue

But if I ask the debugger for Me.Office_Unit.Value or just Me.Office_Unit, I get the error message mentioned above.

I've come up with a reasonable workaround for this issue (it's not generally useful, but it works well enough in this particular application). However, I'd love to understand what's going on here and how to fix it properly.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2014-03-14T15:33:39+00:00

    Whoops, I started composing that last night and came in this morning to find I hadn't sent it. 

    I meant to do it like this:

    Private Sub Office_Unit_GotFocus()

    If Not Me.NewRecord then

        If Me.Office_Unit.DefaultValue <> "" And IsNull(Me.Office_Unit.Value) Then

    Me.Office_Unit = Eval(Me.Office_Unit.DefaultValue)

        End If

    End If

    End Sub

    And I added your suggestion.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-03-14T14:29:47+00:00

    Incidentally, a control's DefaultValue isn't always directly assignable to its Value property, because the DefaultValue is a string that has to be evaluated -- it may even be a function expression.  Probably if you were going to do this in a general case, you would want to use the Eval() function to evaluate the property and assign the result to the value:

        Me.Office_Unit = Eval(Me.Office_Unit.DefaultValue)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-03-14T14:23:58+00:00

    Isn't that the same as the original code?

    Was this answer helpful?

    0 comments
  4. ScottGem 68,810 Reputation points Volunteer Moderator
    2014-03-14T11:28:27+00:00

    You already have a Default property set for the control. You do not need to do anything for new records. So I would change your code to:

    Private Sub Office_Unit_GotFocus()

        If Me.Office_Unit.DefaultValue <> "" And IsNull(Me.Office_Unit.Value) Then

            Me.Office_Unit = Me.Office_Unit.DefaultValue

        End If

    End Sub

    Was this answer helpful?

    0 comments
  5. Anonymous
    2014-03-14T01:15:26+00:00

    I'm a little puzzled.  You say this only happens on a new record?  But won't these controls be set to their default values automatically?  Is this happening for unbound controls only, or bound controls only, or both?

    Was this answer helpful?

    0 comments