Forum Discussion

Chuckz1947's avatar
Chuckz1947
Copper Contributor
Dec 25, 2022

VAL function in afterUpdate property of control

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.

 

access

5 Replies

Sort By
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    Dec 29, 2022
    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.
  • Chuckz1947's avatar
    Chuckz1947
    Copper Contributor
    Dec 26, 2022

    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:

     

     

    • Karl_Donaubauer's avatar
      Karl_Donaubauer
      MVP
      Dec 27, 2022

      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

      • Chuckz1947's avatar
        Chuckz1947
        Copper Contributor
        Dec 28, 2022
        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.

Resources