Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    5

    Unanswered: problem on populating DB from dependent combobox

    Good morning,

    I'm new on this forum and it is a pleasure for me to be her to submit my question to you.
    I need an help on this problems in an access (2003) form.
    I'm trying to realize a input form able to record the data concerning customer order.

    In this form, I have two combo boxes connected each other.
    Each of these combo are taking data by two tables : [tbl_DescrPrd] and [tbl_PrdPack].

    The tables have the same structure :
    [tbl_DescrPrd] :
    Col.1 [ID_prd] ; Col.2 [Prod Name] ; Col.3 [Packaging] ; Col.4 [Packaging description] ; Col.5 [Article code] ; Col.6 [Id_pack]

    [tbl_PrdPack] :
    Col.1 [ID_prd] ; Col.2 [Product]

    The columns [ID-prd] are linked by relationship


    The second combo (pack) is showing perfectly the results coming by the selection of the first combo (product). So no problem here.

    The selection of the two combos has to input a new data in a specific table, but unfortunatly that's is not happening and the combos are inputing data referred to different coloumns. If I try to correct the "bound column", I can have success with the second combo (pack) but with the first combo (product) I receive an error.

    Both the bound columns (in the combos properties) are assigned to number "1".


    Here the VBA code :

    Private Sub cbo_product_BeforeUpdate(Cancel As Integer)
    Dim sManagerSource As String

    sManagerSource = "SELECT [tbl_DescrPrd].[ID_pack], [tbl_DescrPrd].[ID_prd], [tbl_DescrPrd].[packaging] " & _
    "FROM tbl_DescrPrd " & _
    "WHERE [ID_prd] = " & Me.Cbo_product.Value
    Me.Cbo_pack.RowSource = sManagerSource
    Me.Cbo_pack.Requery
    End Sub


    I don't know if these data can help to have a clear picture of the problem. I can also send the specific file.

    THanks a lot for all the help you can give me

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Welcome to the forum blues66!

    The best advice I can give you regarding comboboxes is this...

    1. Comboboxes are used so that you can enter/display a "description" type value but store the ID value (such as ProductID) in the table. Keep this rule in mind:
    - Your form's recordsource should "JUST" be based upon the main data table (ie. don't link your relational tables in the recordsource of the form.)
    - Comboboxes then let you "display" the corresponding description value of an ID field (using the rowsource query of the combobox which is based upon JUST your relational table that has ProductID and ProductDescription.) So for example, I have a combobox on a form where the controlsource of that field = ProductID (the value I actually want to store in the main table, but I want to "display/enter" the ProductDescription into the combobox.) Thus, the rowsource for my combobox is based on my relational "lookup" table ONLY which has all the ProductID's and ProductDescriptions. The 1st column of the rowsource query is the ProductID and the 2nd column of the rowsource query is ProductDescription. I set my column width for the combobox = 0";2" (ie. hide the 1st column (ProductID) and show the 2nd column (ProductDescription)).

    Try to think of a combobox as a way to type in the description value but actually store an ID value. This eliminates the need to "link" your relational table into the RecordSource for the form (- the recordsource for the form should again, "JUST" be based on your data table and not linked with any relational tables.)

    The thing I like to do when dealing with comboboxes is put in this code in the AfterUpdate event of a combobox (for example, ProductID) to make sure I'm retrieving/storing the correct value (given that the bound column is column 1 and the column widths = 0";2").

    Private Sub ProductID_AfterUpdate()
    msgbox "The value being stored is: " & me!ProductID
    msgbox "The value being displayed is: " & me.ProductID.column(1)
    End Sub

    Now you can use a combobox to populate another combobox on a form (but again, keep the rule that the form's recordsource is JUST based on my main data table and the comboboxe's rowsources are JUST based on the relational lookup tables.)

    I'll add to this later....sorry...I've got to run.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2009
    Posts
    5
    Thanks for the help and for the lesson.

    to run? I've just finish a skyrace..... so, welcome in this world.

    I wait the last part of your post

    thanks again

Posting Permissions

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