Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    21

    Unanswered: Question: Access 2010 – how do I update Unbound fields from a Combo Box (total 3 col

    Question: Access 2010 – how do I update Unbound fields from a Combo Box (total 3 columns)?

    I created a form via query. The form will be used for users’ data entry. When a user selects a value from a Reason Code drop-down option (bound field from a combo box), I want the associated Exception and Description values (unbound fields from the combo box) to display and also update in the table. Currently, upon selection of the Reason Code, all three fields successfully appear in the form. However, I can only get the Reason Code (bound field) to update in the table record, and not the Exception and Description fields.

    I’ve read similar Q’s & A’s in previous forums and became somewhat familiar with updating a table containing unbound fields from in a combo box. Since I hardly know anything about Visual Basic coding, I am uncertain where I am supposed to place my code within the form. Below are some background information and my questions.

    Table Name: tbl_cycletrans (main table)
    Field Name: Reason Code -- (setup as Combo Box)
    Field Name: Exception (Per Reason Code) -- (setup as Text Box)
    Field Name: Description (Per Reason Code) -- (setup as Text Box)

    Table Name: tbl_reason code (3-column combo box)
    Field Name: Reason Code -- (column-1 bound field)
    Field Name: Exception (Per Reason Code) -- (column-2 unbound field)
    Field Name: Description (Per Reason Code) -- (column-3 unbound field)

    Query Name: Tim - qry cycle trans – tbl_accts – tbl_reason_code (to obtain data in form)

    Form Name: Tim - form qry cycle trans – tbl_accts_reason_code (used for data entry)

    Below is what I’ve tried, but did not work. Could someone please answer the following questions?

    1. In hind sight, perhaps I should have used a different naming convention for my data names (i.e. no usage of spaces between words and no parentheses). Will this be a problem in my coding?

    2. In the form design view and on the Event tab of the Property sheet, I clicked in the After Update property and selected “[Event Procedure}” from the drop down list. I then clicked the small button with the dots on it to access the code module. When entering code in Event Procedure, which Select Type of the property sheet should I be in?
    – Form type
    – Combo Box type of the Reason Code field
    – Combo Box type of the Reason Code field, Text type of Exception field, and Text type of the Description field

    3. I used the Combo Box type of the Reason Code field. Below is one of the unsuccessful codes that I used between the two lines reading: “Private Sub Form_AfterUpdate()” and “End Sub”.

    Syntax: [Forms]![FormName]![ComboBoxControl].(unbound col nbr)

    Private Sub Form_AfterUpdate()
    [Forms]![Tim - form qry_cycle trans - tbl_accts - tbl_reason_code]![Reason Code].(1)
    [Forms]![Tim - form qry_cycle trans - tbl_accts - tbl_reason_code]![Reason Code].(2)
    End Sub

    4. When I select the Debug from the menu option, could someone help me correct my syntax errors?

    5. When my compile errors are syntax free, am I supposed to select the Run option from the menu bar?

    6. Am I supposed to create a macro to run future updates?

    Any assistance that you could provide me would be greatly appreciated.


    Thx -Tim (new db Forum member)

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally speaking you use:
    Code:
    Me.Text1.Value = Me.Combo1.Column(0) ' Leftmost column.
    Me.Text2.Value = Me.Combo1.Column(1) 
    Me.Text3.Value = Me.Combo1.Column(2) 
    ' etc.
    Have a nice day!

  3. #3
    Join Date
    Sep 2012
    Posts
    21

    Compile Error to: How do I update Unbound fields from a Comb

    Hi Sinndho,

    Thank you so much for your quick response! Since I am new to Visual Basic coding, please pardon my ignorance. I still am receiving a Compile Error that states: “Compile error: Invalid use of property. “

    Here is what I’ve tried. I clicked on the Form Property Sheet. On the Event tab I selected “After Update” and “[Event Procedure]”. Then I coded the following based on my interpretation of your answer:

    Private Sub Form_AfterUpdate()
    Me.[Reason Code].Column (0)
    Me.[Exception (Per Reason Code)].Column (1)
    Me.[Description (Per Reason Code)].Column (2)
    End Sub


    The compile error highlighted “.Column” on the line:
    Me.[Reason Code].Column (0)

    Could you tell me what I am still doing wrong?

  4. #4
    Join Date
    Sep 2012
    Posts
    21

    Compile Error to: How do I update Unbound fields from a Combo Box (total 3 columns

    Hi Sinndho,

    Thank you so much for your quick response! Since I am new to Visual Basic coding, please pardon my ignorance. I still am receiving a Compile Error that states: “Compile error: Invalid use of property. “

    Here is what I’ve tried. I clicked on the Form Property Sheet. On the Event tab I selected “After Update” and “[Event Procedure]”. Then I coded the following based on my interpretation of your answer:

    Private Sub Form_AfterUpdate()
    Me.[Reason Code].Column (0)
    Me.[Exception (Per Reason Code)].Column (1)
    Me.[Description (Per Reason Code)].Column (2)
    End Sub


    The compile error highlighted “.Column” on the line:
    Me.[Reason Code].Column (0)

    Could you tell me what I am still doing wrong?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Please read my answer carefully. The general structure of the statement is:
    Code:
    Me.TextBoxName.Value = Me.ComboBoxName.Column(x)
    where x is the numeric index of a column is the ComboBox list, with the first (leftmost) column being column zero. So, what's the name of the ComboBox control and what are the names of the 3 TextBox controls?

    2. These statements should be placed in the AfterUpdate event handler procedure of the ComboBox, not in the AfterUpdate event handler procedure of the Form. If the ComboBox control is bound (i.e. if it is linked to a field in the RecordSource property of the Form), you should repeat the statements in the Current event handler procedure of the Form.

    3. Except for the underscore (_), refrain from using spaces or other non-alphabetic characters in the names of your objects (Tables, Columns, Forms, Controls, etc...), sooner of later you'll be in trouble otherwise. You can use the CamelCase naming schema or use an underscore character to separate various_parts in the names.
    Have a nice day!

  6. #6
    Join Date
    Sep 2012
    Posts
    21

    SUCCESSFUL SOLUTION to updating multiple Unbound fields from a Combo Box.

    Awesome Sinndho! I’ve been working on this coding problem for days and you’ve provided this Visual Basic language novice with the solution! I cannot thank you enough! In case someone else runs into a similar problem, here is my solution, even when using special characters and spaces in my text field names.


    SOLUTION:

    I went to the Property Sheet of my Form. I clicked on the Event tab of my Combo Box named "Reason Code". I selected “After Update” and “[Even Procedure]”. I then coded the following successfully:

    Private Sub Reason_Code_AfterUpdate()
    Me.[Reason Code].Value = Me.[Reason Code].[Column](0)
    Me.[Exception - per Reason Code].Value = Me.[Reason Code].[Column](1)
    Me.[Description - per Reason Code].Value = Me.[Reason Code].[Column](2)
    End Sub

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome, although I do not understand why you would need to use:
    Code:
    Me.[Reason Code].Value = Me.[Reason Code].[Column](0)
    Have a nice day!

  8. #8
    Join Date
    Sep 2012
    Posts
    21
    Oh, I thought I was supposed add that part based on my interpretation of your initial example/response. Again, I'm just a novice VBA user. Thx again for your assistance!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •