Results 1 to 5 of 5
  1. #1
    maxscott is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    5

    Related Tables in Macro

    Can I refer to a related table in a macro?



    I have table trans related to table trantypes on trantypeid. I want an event to do a calculation based on some data i add to trans.

    I therefore have a beforechange event in trans which reads...

    [trans].[amt] = [trans].[lamt] * [trantypes].[multiplier]

    but this always errors with

    the identifier '[trantypes].[multiplier]' could not be found

    Any help much appreciated

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Many of us here are not good at macros since we don't use them (OK, maybe just me).
    You could probably use a DLookup on the other table to get the multiplier instead of using [trantypes].[multiplier]
    The BeforeUpdate event may not be suitable for this. Whatever the contents of the control appear to be, they won't be used in your event unless you're referring to the control's .Text property. If just referring to the control or its default .Value property, the control holds one value while it's displaying another. In fact the control value could be Null. Maybe you need the AfterUpdate event?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Micron View Post
    The BeforeUpdate event may not be suitable for this. Whatever the contents of the control appear to be, they won't be used in your event unless you're referring to the control's .Text property.
    Don't know about macros, but this isn't accurate in VBA. Certainly true of the change event.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe I have my events confused, but I've experienced the discrepancy before - could have been LostFocus or some click event perhaps. Cannot recall if it matters that the control is bound or not. However, I quote this:

    While the control has the focus, the Text property contains the text data currently in the control; the Value property contains the last saved data for the control. When you move the focus to another control, the control's data is updated, and the Value property is set to this new value.
    Perhaps I should have said "may not be used..."?
    https://msdn.microsoft.com/en-us/lib.../ff822552.aspx
    Last edited by Micron; 04-01-2017 at 04:13 PM. Reason: added info

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Micron View Post
    Perhaps I should have said "may not be used..."?
    What, the event may not be used? Check the example code:

    https://msdn.microsoft.com/en-us/lib.../ff822552.aspx
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 01-20-2017, 02:21 PM
  2. Replies: 1
    Last Post: 06-06-2016, 06:05 PM
  3. Related Tables
    By roybb in forum Database Design
    Replies: 5
    Last Post: 08-27-2014, 03:30 PM
  4. Replies: 8
    Last Post: 06-09-2014, 09:26 AM
  5. Replies: 5
    Last Post: 12-14-2012, 04:21 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums