Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    1

    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.

    Please help!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A current solution to this problem consists in executing a dynamic query to insert the values from the combo into the table.

    Code:
    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
    Another, perhaps simpler solulion would consist in creating 2 bound TextBox controls (they can be hidden, i.e. they can have their Visible property set to False) and update these controls from the values in the Combo:
    Code:
    Sub ComboName_AfterUpdate()
    
        Me.Text_Col1 = Me.ComboName.Column1
        Me.Text_Col2 = Me.ComboName.Column2
    
    End Sub
    As the 2 TextBox controls (Text_Col1 and Text_Col2) are bound, their values will be saved as the values of the other controls in the form.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by bsmith0404 View Post

    ...I have a form with two conditional combo boxes...

    ...all of the data is transferred to the associated column in the "Customer Table" except the Branch and Division...
    As I read this, you have one Combobox for Branch and one for Division. If this is correct, the easiest solution of all would be to Bind the Comboboxes to the appropriate Fields in the underlying Customer Table.

    In Form Design View
    1. Select a Combobox
    2. Go to Properties - Data
    3. Use the dropdown arrow and select the appropriate Field for the Control Source Property
    4. Repeat Steps #1 - #3 for the second Combobox
    The fact that you're using the Branch Combobox to cascade and set the RowSource for the Division Combobox doesn't prevent it from also being Bound to a Field

    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

Posting Permissions

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