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

[TimMorgan]

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?

• 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?

• 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

• 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

• When I select the Debug from the menu option, could someone help me correct my syntax errors?
• When my compile errors are syntax free, am I supposed to select the Run option from the menu bar?
• 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 PC Review member)