Results 1 to 8 of 8
  1. #1
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170

    Populating a Field and Saving Record

    Hello,



    I have a field: Goal on my form that I want to be auto-populated based on a long-string of IIf statements that I am going to try and write. I also want this information recorded with the submission of each record.

    I have read pbaldy's: http://www.baldyweb.com/Autofill.htm

    But cannot figure out how to incorporate VBA code.

    Sample:

    Goal = [Process] - ("Yes" in [Break])

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    Populating fields with calculated results requires code (macro or VBA) in some event. The trick is figuring out what event. It could be a control AfterUpdate or button Click or form BeforeUpdate.
    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.

  3. #3
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    I have identified an event to start with, would you be able to point me to a VBA resource that explains field and arithemtic statements?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    Not sure what you mean by 'field and arithemtic statements'.

    Access Help has some guidelines on building expressions in query and textbox. Basically the same operators are used in 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.

  5. #5
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Sorry, what I was trying to say is something that talks about how to write vba code, specifically, how to combine if statements with math.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    "If statements with math"? You will have to be more specific on what you want. Possibly what you want is an IIf() expression in query or textbox. Again, search Access Help.

    Start with a review of http://office.microsoft.com/en-us/ac...010341717.aspx
    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.

  7. #7
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    I originally started with this IIF statement in the textbox:

    =IIf([Combo73]="Break",([Text33]*0.75),IIf([Combo73]="Lunch",([Text33]*0.5),IIf([Combo73]="Huddle",([Text33]*0.75),IIf([Combo73]="Full Shift",[Text33],"0"))))

    It works great. But I want the info in that textbox to be recorded with each record that I submit. After reading Baldy's post, I am under the impression that the only way to accomplish this is to: Translate the above statement into VBA coding and then attach it to the textbox under an "After Update" event.

    What I am looking for is how to translate the expression to VBA coding?

  8. #8
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    This thread is solved.

    Wrote VBA Code in After Update Event as follows:

    If Me![Combo68] = "Break" Then Me![Goal] = ([Text72] * 0.75)

    Thank you for your replies.

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

Similar Threads

  1. when saving a record problem
    By sireesha in forum Access
    Replies: 7
    Last Post: 01-30-2013, 05:44 AM
  2. Make form not saving new record right away
    By darekg11 in forum Forms
    Replies: 0
    Last Post: 09-17-2012, 01:22 AM
  3. Message Box for Saving Record
    By Mahavir in forum Access
    Replies: 5
    Last Post: 12-29-2011, 12:13 PM
  4. Saving Record
    By mwabbe in forum Programming
    Replies: 12
    Last Post: 09-15-2010, 12:09 PM
  5. record saving twice in table
    By ds_8805 in forum Forms
    Replies: 15
    Last Post: 04-14-2010, 07:16 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