VAL function in afterUpdate property of control

Copper Contributor

In a real estate properties DB I have a form with:

1. a ctrl, Tstnumb, for target street numbers. The underlying field is a text field

2. a ctrl for the value of ValTstnumb, which holds the value of Tstnumb. The underlying field is a number field.

3. The objective is that when addresses, in particular the street numbers, are entered manually, the control Valtstnumb is populated.

4. The reason  for having Valtstnumb is that sometimes an address number could be something like 21A or 135 - 137, and frequently Valtstnumb is used to drive the sort order in a query.

I believe that in the past the following has worked.  It has been built into the form for decades but now produces a type mismatch error: Valtstnumb = Val([TstNumb])

 

I am using Access 365, and is subject to frequent Microsoft updates.  I am wondering whether a recent update might be the issue.

 

5 Replies
That should still work. Make sure your FE is in a Trusted Location.

@Chuckz1947 

Thank you for your reply.  Both the FE and BE are where they have always been.  Th DB is entirely functional in all other respects.  This is the error message, no matter what I enter as the street number, a number, a character or a combination:

 

Chuckz1947_0-1672064438906.png

 

Hi,

 

Have you tested if it is really a problem with Val() or rather with the reference to the control? What happens if you use:

 

Valtstnumb =TstNumb

and

Valtstnumb = Me!TstNumb.Value

and

Valtstnumb = Val(Me!TstNumb.Value)

 

?

 

Servus
Karl
****************

Access Bug Trackers

Access News
Access DevCon

None of the above solve the issue.
Not sure if I mentioned this, but in a query I use similar code that does work. I use the query method when I import batches of data. The query is:
UPDATE Properties SET Properties.Valtstnumb = Val([tstnumb])
WHERE (((Properties.Valtstnumb)<1));
The above populates the field ValTstnumb in records where ValTstnumb is not populated.
you should probably use the Control's Name rather than the Fieldname:

Me!ControlName_Of_ValTstnumb_textbox = Val(Me!ControlName_Of_Tstnumb_textbox & "")

and use the Expression in the "BeforeUpdate" event of your Tstnumb_textbox control.