Results 1 to 6 of 6
  1. #1
    Phill Marley is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Location
    Woking Surrey
    Posts
    4

    how to create an event macro that updates a field after another field has been changed

    Hi this is my first post so please be patient.

    I have a table which has 3 fields. the first "Ordnumber" is a number the second "Status" is a combo Text field with 3 options (These are "Enquiry","Provisional" and "Booking") the third is "Reference". what I am trying to create is an onupdate event macro in a form that when a selection is made in "Status" the third field which is "Reference" is populated with the first digit of the choice made in the combo box ( so this will be a "E","P" or "B") followed by the number in Orderno.

    eg

    Orderno Status Reference

    12345 Enquiry E12345 (original enquiry)
    12345 Booking B12345 (Enquiry changes to a booking)



    I tried this code but it always comes up with an error

    = Reference: iif([Status]="Enquiry","E"&[Orderno],iif([Status]="Booking","B"&[Orderno],iif([Status]="Provisional","P"&[Orderno])))

    any help would be appreciated

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    when the user picks ENQUIRY, you can also get the 1st letter in the event ..
    in the combobox AFTERUPDATE event, click the ellipsis button, then CODE BUILDER...
    then assigne the reference field the 1st letter in the combo box

    Code:
    Private Sub cboBox_AfterUpdate()
    reference = left(cboBox,1) & Orderno
    end sub

  3. #3
    Phill Marley is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Location
    Woking Surrey
    Posts
    4
    hI ranman256

    Do I have to repeat this code for the other two combo box choices Ie create 2 new private subs one for booking and one for Provisional

    Regards
    Phil

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    what other combo boxes?
    you only mentioned status combo.

  5. #5
    Phill Marley is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Location
    Woking Surrey
    Posts
    4
    Sorry I get it now
    The code will pick up the first digit of whatever choice was made in the como box

  6. #6
    Phill Marley is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Location
    Woking Surrey
    Posts
    4
    Hi Ranman

    Just to say thankyou for your help last week. The code worked fine . I just wondered what was wrong in the expression that I originally entered ( The code I originally posted in my thread ) what was the syntax error that I kept getting . Obviously its not important now that you supplied the correct code, but if I was to try and use the expression builder again it would be useful to understand where I was going wrong.

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

Similar Threads

  1. Replies: 4
    Last Post: 10-14-2015, 02:33 AM
  2. Replies: 5
    Last Post: 04-05-2014, 08:52 PM
  3. Replies: 1
    Last Post: 08-20-2013, 02:17 PM
  4. Replies: 4
    Last Post: 12-01-2010, 01:12 PM
  5. Replies: 7
    Last Post: 05-29-2009, 04:27 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