Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: Using a combo box to update access table fields

    Hi all

    I'm new here, and relatively new to Access so please bear with me.

    I am using Access 2010. I have 5 tables set up: A customer table, Employee table, Products table, Invoice table, and an Invoice details table.

    I have also created Input forms for all of these: Customer Form, Employee form, Products Form, and an Invoice input form with a Invoice input sub form to update the Invoice and Invoice details tables.

    The first three forms and tables are a piece of cake and work well. It is the Invoice tables and forms I have trouble with. The invoice table has the following fields in it:

    Invoice Number (Primary Key) Invoice Date, Customer ID (Foreign Key) & Employee ID (Foreign Key)

    The invoice details table has the following fields: Details Unique Key (Primary Key) Invoice Number (Foreign Key) Product ID (Foreign Key) & Quantity

    The form I have created to populate the fields have the following fields linked from the Invoice input form:

    Invoice Number (Primary Key) Invoice Date,

    There is a sub form (Invoice Input sub form) that has a Quantity field linked from the Invoice details table.

    These are all fine, but I also need to populate the other fields in the invoice tables - namely the Customer ID, Product ID and Employee ID fields. My idea is to create a combo box to bring up the name of each, but enter the ID into the appropriate fields. (Using IDs in the combo box makes it hard to know what product or customer you are selecting) The information in the combo boxes are sourced from the Customer, Employee and Product tables and works fine.

    My problem is that when I select those combo box fields in the form (Say to create the invoice) it doesn't update the Invoice tables with the details in the combo boxes. Why? How do I make the database do this?

    Many thanks in advance

    Razor
    Last edited by RazorX; 05-26-11 at 03:48.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    I don't follow your post. Is a bit confusing. But in general; if one has values in columns of a combobox - - then those values can be written into any field of any form/subform.

    the combobox of course has one bound value. so you refer to that as me.comboboxname (with variant syntax if combobox is located in a parent main form or subform as appropriate)

    you refer to the other nonbound values in other columns by their column order i.e.
    me.comboboxname.column(2)

    with of course the same variants.

    hope this helps a little.
    www CahabaData com

  3. #3
    Join Date
    May 2011
    Posts
    3
    Hi NTC

    That might be my problem perhaps... in design view in the forms my combo boxes show as unbound. Do I need to bind them to a table, or a field in the form? If so how do I do this?

    Thanks

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    No. my use of the term 'bound' in this case refers to which value of the combobox's record (when that record is multi column) becomes the value of the combobox...it defaults to binding the first, most left column - but you can bind any column.

    So 1 value of your multicolumn combobox is the value of the combobox itself - the other columns' values can be used/called per my first post.

    This has nothing to do with whether or not the combobox itself is 'bound' to a record source of the form - - that is a totally different issue.

    Hope this helps.
    www CahabaData com

  5. #5
    Join Date
    Mar 2012
    Posts
    8
    Following this logic I have tried to use
    Code:
    Private Sub Form_AfterUpdate()
        Dim strSQL As String
        
        strSQL = "UPDATE Inc_t SET Reason_code = & Me.rcombo.Column(0).Value"
        
        CurrentDb.Execute strSQL
    End Sub
    So that the value from the combo box updates the field after the form is update, but I get an error that I have missing operator in query expression
    Code:
    & Me.rcombo.Column(0).Value
    Is this something that must be done on the After_Update of the combo box only or does the form have to be bound to the table before I do this?

  6. #6
    Join Date
    Mar 2012
    Posts
    8
    if there is anyone that can give me an idea of what I am doing wrong here I have been playing with this for a couple of days with no luck. I am confused if the problem is because I want the update to happen when the form is changed or closed, I am assuming this is possible or if the SQL is just incorrect.

    Thanks

  7. #7
    Join Date
    Mar 2012
    Posts
    8
    For anyone reading in the future, the easy thing for me to do was to not use an unbound combo box. I am sure there is a way to do this, but if you are new to access like me use a bound combo box and it updated exactly as it should have. No SQL or VBA required.

Posting Permissions

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