05-22-14, 12:02 #1Registered User
- Join Date
- May 2014
Unanswered: Table Update from a Form Combo Box
I have a form with two conditional combo boxes. The information for the CBs is contained in one table that is only used for to provide the information for the CBs. The form itself contains a lot of information about individual customers. The information is then stored in a "customer table".
The problem I have, the information that is in the combo boxes also needs to be stored in the customer table. Since the data comes from a different table and the combo boxes are not associated with the customer table, I cannot figure out how to get the information to update into my customer table when I create a new record in the form.
For example: Table "BranchAndDivision" has queries that are used to update the combo boxes in the "Customer Form". When a Branch is selected, the Division combo box only shows options associated with that Branch. When a new cutomer is added using the "Customer Form", all of the data is transferred to the associated column in the "Customer Table" except the Branch and Division.
I assume I have to use some type of an After Update Event, but cannot figure out how to get it to write to the Customer Table since the CBs are bound to the BranchAndDivision table.
05-22-14, 17:14 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
A current solution to this problem consists in executing a dynamic query to insert the values from the combo into the table.
Dim strSQL As String ' If the row already exists in the table. ' and the values are of type Text. ' strSQL = UPDATE TableName SET Column1 = '" & Me.ComboName.Column(1) & _ "', Column2 = '" & Me.ComboName.Column(2) & "';" CurrentDb.Execute strSQL, dbFailOnError
Sub ComboName_AfterUpdate() Me.Text_Col1 = Me.ComboName.Column1 Me.Text_Col2 = Me.ComboName.Column2 End SubHave a nice day!
05-24-14, 17:42 #3Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
In Form Design View
- Select a Combobox
- Go to Properties - Data
- Use the dropdown arrow and select the appropriate Field for the Control Source Property
- Repeat Steps #1 - #3 for the second Combobox
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007