Results 1 to 3 of 3
  1. #1
    wilpeter is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    18

    SetValue of a Yes/No field with a Macro using an IIF formula - doable?

    I'm using A2003 (yes, still). An accounting type form requires the "Type" be entered as either (numeric) 1, 11, or 2. On the same form, one of the three Yes/No fields must be checked as confirmation.
    My hope is that I can use the "SetValue" action in a macro.
    My first attempt (to set the "Assiniboine" field to "Yes" or "On"):
    Item: [Forms]![DisbursementMade]![Assiniboine]
    Expression: IIf([Forms]![DisbursementMade]![Type].[AfterUpdate]=1,Yes)
    That isn't doing anything for me.

    The default value for the "Type" field would normally be 1 (but that presumes the user will not skip past it without confirming it) so I have no default value.
    The other two Yes/No fields would also be Set by the same logic: (if Type=11, [Bank]=Yes) (if Type=2, [PettyCash]=Yes)



    Alternatively, I've tried the reverse approach: If [Assiniboine]=Yes, SetValue of [Type] to 1 so far without success. I'm not using VBA, since the bookkeeper changes from year to year and there is no Administrator. Although the 'club' computer has A2010, and can run an .mdb file with macros, successfully, I prefer to work in A2003 without VBA or SQL. I prefer to work in Access 2003 because the db can then be used by A2003 and A2010 systems alike; whereas if I add a macro within 2010, using the Expression option within Properties, it will create an "Embedded Macro", which is not readable if the file is later opened in A2003.

    Is there hope for me?

  2. #2
    wilpeter is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    18
    In case my situation wasn't clear, here is an expanded picture of the requirements:
    There are two forms "Disbursements" and "Receipts" - each has a "Type" field and 3 checkboxes (Assiniboine, TDBank, and PettyCash). The Type field values are 1, 11, and 2 in disbursements and 4, 41, and 2 in receipts.
    So for one macro (SetValue) the Item is [Forms]![Receipts]![Assiniboine].[Before Update]
    and the Expression is IIF([Forms]![Receipts]![Type].[After Update]=4,Yes,No)
    But nothing happens.

  3. #3
    wilpeter is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    18
    OK, got it. My problem was in mentioning the Before Update and After Update within the expressions. Works OK when strung in sequence without.
    Thanks anyone who thought about responding.

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

Similar Threads

  1. Macro - Open Report, SetValue
    By NLA in forum Programming
    Replies: 1
    Last Post: 11-17-2012, 09:53 PM
  2. SetValue Macro
    By BrianF in forum Programming
    Replies: 1
    Last Post: 10-21-2012, 08:49 PM
  3. SetValue Macro action
    By rkalapura in forum Programming
    Replies: 3
    Last Post: 12-19-2011, 11:54 AM
  4. No SetValue in Macro editor
    By asabri in forum Programming
    Replies: 2
    Last Post: 09-21-2011, 01:39 PM
  5. Problem with SetValue Macro on Form
    By Accidental DBA in forum Forms
    Replies: 3
    Last Post: 04-01-2011, 01:48 PM

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