Results 1 to 11 of 11
  1. #1
    aharown is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    10

    New to access: why can't I set a field value using a macro from a form event?

    Long time FileMaker Pro solution builder, but I have to create a solution in MS Access at work.
    The paradigm shift has me often befuddled.



    What I'm trying to do:

    I have four fields: Attributed, State, Locale and SourceID
    I want to create SourceID values automatically when I key in Attributed values. SourceID needs to be constructed from a portion of the Attributed field with state and Locale appended.

    The struggle so far:
    • On a datasheet view of the table in question, I can use a Before Entry Data Macro to set the SourceID field. Unfortunately, it only populates when leaving the current record. Annoying. Also, I want to be able to do this from a form most of the time.
    • On a form bound to the table, tried various Event approaches to updating the SourceID field, but it won't allow me to use "setfield" ... says it can't be used in the current context.


    So ... I'll try to resist the urge to ask why setfield is so dang hard to use. Skip that: how do I use a macro to set a field value, from a form, triggered automatically when I exit the field (not when I exit the record)?

    Oh, I also tried to create a named macro, since Event on a form seemed to want that. Sure enough, in a named macro, can't use setfield either. Doesn't even appear in the actions catalog.

    Edit: I should mention -- reason I'm not using a calculation field is that I need to use SourceID in a relationship to another table.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have you considered using the default value? Also do not recommend the use of calculation fields - they can't be used in in relationships anyway. you can auto populate a child field if the form is a subform to the parent record form by setting the subform control linkchild and linkmaster properties and defining the relationship in the relationships view - no code required. Also not clear what you mean by 'attributed field/values'/'state'/'locale'

    Data macros have limited scope and for that reason, rarely used. Given the huge difference in approach between filemaker and access you would be advised to describe in simple terms (avoid technical terms) what you are trying to achieve. Provide some example data with a before and after.

  3. #3
    aharown is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    10
    "What I'm trying to do:

    I have four fields: Attributed, State, Locale and SourceID
    I want to create SourceID values automatically when I key in Attributed values. SourceID needs to be constructed from a portion of the Attributed field with state and Locale appended.'

    I'm not sure I know how to make it simpler than that.... I could add an example.

    On a form...
    Say I put "Joe Schmo" in the Attributed field, MN in the state field, and 12 in the Locale field.
    I want the SourceID field to be set to, say, "JoeMN12"
    I'd like this happen whenever I change the values in any of the fields involved: Attributed, State, or Locale.

    I did try using Default Value for SourceID but there was a problem: it would not allow the value of SourceID to be calculated based on other field values. I gather that this is because when a record is created, the fields don't have any values.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't use macros, Data Macro or otherwise, only VBA. I don't even see a BeforeEntry data macro. There are BeforeChange and BeforeDelete.

    Regular macros have a SetValue action. Have to click ShowAllActions to expand the actions catalog list.

    In VBA would be simply: Me!fieldname = Me.controlname (or some expression that constructs the desired value)

    This is a poor unique identifier. What if there is more than one Joe in MN and 12?

    Name parts should be in separate fields. Now first have to parse the Joe part from the "Joe Schmo" input then concatenate to other data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    aharown is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    10
    Thanks. On "Before Entry," my mistake: it's Before Change.

    On poor unique identifier: it would be if the data was actually people's names, but I was just throwing together a quick eg. In reality it would be an agency name. These particular agencies always include the city in the name, hence the state identifier to set apart from an agency by the same name in another state. Once a while, I see same name and also same state. That's where Locale kicks in. Set manually. It's very rare. If I ever get this off the ground, I'll add some validation or something to error me if it's not unique, but in this table... not likely to happen.

    I've done a tiny bit of VBA work in MS Word macros, but not enough to understand it well yet.

    Is "Me!" a sort of built in variable that means "current form" or "current table" depending on context?

    So, should I understand the VBA, in my example case, to be, somewhat hypothetically:

    Me!SourceID = Trim(Left([Attributed],3)) & IIf([State]<>"",[State],"") & IIf([Locale]<>"",[Locale],"")

    I imagine the syntax for the expression has to be different for VBA?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum..........

    My path was Xbase (dBase II/III) to Filemaker to FileMaker Pro to Access (Access 95, IIRC).

    dBase II/III (flat file dB) - had to program everything! But total control.
    Filemaker/FileMaker Pro (flat file dB) - quick to setup & use, but frustrating because only had scripting. Took me a year to complete a FMP dB...
    Access - (RDBMS) - Powerful, but took a long time to figure out. Event driven - much different than XBase (procedure driven).

    I think I still have a copy of FMP4 somewhere...... Hmmmm.

    OK, enough of that.........


    Just so we are on the same page:
    What you have and what you are trying to achieve are, obviously, to different things.
    What you have:
    Quote Originally Posted by aharown View Post
    I have four fields: Attributed, State, Locale and SourceID
    I want to create SourceID values automatically when I key in Attributed values. SourceID needs to be constructed from a portion of the Attributed field with state and Locale appended.
    <snip out the macro stuff>
    Edit: I should mention -- reason I'm not using a calculation field is that I need to use SourceID in a relationship to another table.
    The edit info helps. You should be using an autonumber type field for your relationship (an number type field) instead of a text field.

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/Autonumbers

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp



    Quote Originally Posted by aharown View Post
    <snip>
    I'm not sure I know how to make it simpler than that.... I could add an example.

    On a form...
    Say I put "Joe Schmo" in the Attributed field, MN in the state field, and 12 in the Locale field.
    I want the SourceID field to be set to, say, "JoeMN12"
    I'd like this happen whenever I change the values in any of the fields involved: Attributed, State, or Locale.
    The example really helped/clarified. This is more of what you are trying to achieve. (IMO)

    If you want to combine fields "Attributed", "State" and "Locale", that should be done in a query. I always (well, 99% if the time) base my forms on queries. That way, any changes are will automatically calculate the "SourceID" so it is always up to date. And the autonumber PK field doesn't change so the tables are still linked correctly.


    Since you have two related tables, you could use a main form/sub form for adding/editing data.. No VBA required. (and I never use macros)


    My $0.02............



    Edit: since you are dipping you toe into Access, maybe these will help you get started (sites I've collected):
    Attached Files Attached Files

  7. #7
    aharown is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    10
    There is some helpful info there and I appreciate that. It's interesting, though, how often these kinds of discussions question what a person is attempting to do rather than helping them do what they've decided to do. :-)

    I'd tell the whole story of why it makes sense in my situation to use a text field as a foreign key in a relat. with another table, but ... it's boring, long and not really relevant. The really short version is, yes, I've thought it through and, sadly, it really is necessary. But it's only temporary, so that's the upside! It just might be a year or two before the really messy data I'm stuck with is all migrated, restructured, and turned into something sane (by people other than me). My role currently is exporting lots of rubbish data and then cleaning it up a whole lot so I generate some decent reports. (But the exported data has to be combined with records I create by the dozens every day. So I basically work with my own database cobbled from exported data and my own data. Yeah, it's nuts, but it's life... and more fun than it sounds.)

    So a few followup questions:

    * If I was going to populate SourceID using a query, would that need to be an update query? I've worked with select queries quite a bit over the last year or so, but haven't branched at all into actually changing data in tables using queries. So I'm not clear on how that works via a form.

    * Increasingly looks like I ought to just dive seriously into VBA. It's strange to me that so many things that seem like very basic operations anyone would want to do in a functional database seem to require VBA programming to do in Access, but if that's what it takes, I've learned script languages before. Gotta start somewhere.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    There is some helpful info there and I appreciate that. It's interesting, though, how often these kinds of discussions question what a person is attempting to do rather than helping them do what they've decided to do. :-)
    Nobody here is trying to put you down or discount your efforts and objectives. But, keep in mind you're asking advice from people with dozens of years experience with Access.
    These people have successfully completed hundreds of complex Access applications, and know what is required to do so.
    It's strange to me that so many things that seem like very basic operations anyone would want to do in a functional database seem to require VBA programming to do in Access, but if that's what it takes, I've learned script languages before. Gotta start somewhere.
    That's very good advice. Access is a powerful beast, but YOU must learn to master the beast.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Me is a VBA constant that references the form or report it is behind, not a table or query. The bang (!) and dot (.) are often interchangeable in code (macros, VBA, SQL) but in VBA the dot will provoke intellisense popup tips. So typing Me. will help prevent spelling errors.

    And yes, an expression along the lines you posted, which is valid VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If I was going to populate SourceID using a query, would that need to be an update query? I've worked with select queries quite a bit over the last year or so, but haven't branched at all into actually changing data in tables using queries. So I'm not clear on how that works via a form.
    your data is normally bound to the form - the record will be updated (or created if a new one) when you close the form/go to another record/use vba to force a save

    Increasingly looks like I ought to just dive seriously into VBA. It's strange to me that so many things that seem like very basic operations anyone would want to do in a functional database seem to require VBA programming to do in Access, but if that's what it takes, I've learned script languages before. Gotta start somewhere.
    define 'functional'. A very basic application requires no code at all. But your requirement requires splitting a field value in some way and creating a new value by concatenating values. As mentioned before, you would normally use a numeric primary key which is significantly faster in operation and requires less space. You can also use multi field indexes where duplicates are not allowed to detect an attempt to create a duplicate. None of this requires code.

    If you are trying to create an account number/identifier then that is a different proposition and will require code of some sort - or id calculated on a form or in a query as suggested by Steve - but it is not a good idea to use that account number as a key field for linking records together (implied by the use of the SourceID name).

  11. #11
    aharown is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    10
    Thanks, everyone, things are getting clearer, and I appreciate the help.

    My idea of "functional" might seem overly broad, but I've been working with relational database applications off and on since around 2002 and I've rarely built one that didn't have places where I wanted to transform values in fields based on data in other fields. To me, this is a major part of what I use a database for -- to automate that sort of thing. For me, workflow and user experience are usually top priority, so I am sometimes less efficient with data in order to be more efficient with the human element. (The two are not inherently at odds, but I've found that they do clash on a somewhat regular basis... So in various settings where I was not the developer, I've had to work with databases that were presumably tidy on the back end but were real time-wasters on the front end.)

    In any case, I need to play with some VBA scripts and get familiar with how they work. I've signed up for an online class that looks pretty well put together (and was on sale ) and I'm sure that will help also.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-28-2017, 07:28 AM
  2. Replies: 3
    Last Post: 10-20-2016, 09:56 AM
  3. Replies: 5
    Last Post: 05-23-2016, 09:15 AM
  4. Replies: 4
    Last Post: 10-14-2015, 02:33 AM
  5. Replies: 0
    Last Post: 07-13-2010, 07:45 AM

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