Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Thanked 0 Times in 0 Posts

    Update Value of Form Field If Another Field Has Specific Data

    I have a subform in Access 2013 with certain fields that need to be automatically completed if another field has a particular response.

    In one instance, I have a "Contact Last Name Textbox" field where if the "Relationship to Applicant Combobox" field is "Self", the "Contact Last Name Textbox" field needs to autocomplete with the data in the "Applicant Last Name Textbox" field in the form that contains the subform.

    I have attempted to create a hidden field for "Applicant Last Name Subtextbox" and using an "Iff" expression have the "Contact Last Name Textbox" field refer to it if the condition I was looking for was met. However, that did not work.

    Can anyone help me with a VBA code to use with an "After Update" event? My knowledge of VBA is nil, so the existing threads with similar but different questions don't really help. However, I'm sure I can take that code and run with it for the rest of my fields.

    Thanks in advance.

    Here is a sample of what I mean (for the sake of clarity):

    Applicant Form

    Last Name [Last Name Textbox]
    First Name [First Name Textbox]

    Contact Subform

    Last Name [Contact Last Name Textbox] <-Should agree with [Last Name Textbox] if [Relationship to Applicant Combobox] = "Self"
    First Name [Contact First Name Textbox]
    Relationship to Applicant [Relationship to Applicant Combobox]
    [Hidden Applicant Last Name Subtextbox]
    Last edited by Roadhouse*; 2014-09-04 at 19:53.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts
    Is your form constructed so that the [Contact_Last_Name] text box is always entered before the [Relationship_to_Applicant] combo box is chosen? If so you could use a VBA procedure on the combo box that triggers on the AfterUpdate for the combo box. The statement would look like:
    If [Relationship_to_Applicant] = "Self" Then
        [Applicant_Last_Name] = [Contact_Last_Name]
        ' In this case the else clause isn't really necessary
    End If
    You could also use the IIF() function and have a single line procedure, but then you need to specify the false condition. The If - Then - Else syntax is generally viewed as more flexible and more readable.

    This all presumes that you subform is bound to a record set that will automatically update the data in the table(s) involved. If you are using an unbound form, you will need lots of VBA to actually save the data. You will also need to deal with situations where a user uses a mouse to click subform controls in a sequence other than the tab sequence, and you will probably want to set a default for the combo box, presumably "self" in order to issues when that control is skipped. Hope this helps you get going with your form design.

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