• Setting up a combox using a table for list (Access 07)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Setting up a combox using a table for list (Access 07)

    Author
    Topic
    #449360

    I am developing a user form on a access data base and would like to to have a combo box with a list of things to be picked. I can set the list up with in the properties, but I was wondering how I could use a table and the combo box would just reference the table. Any tips on how this can be done?

    Viewing 0 reply threads
    Author
    Replies
    • #1101556

      You can set the Row Source property of the combo box to the name of the table. I don’t think it gets simpler than that. What exactly do you want?

      • #1101560

        ok thanks I will try this

      • #1101799

        What I am exactly after is setting the row source to a table, called customer and I query all in the field called Company. The next combo box is where I am having trouble. I would like to query only Company Reps names from Customer table where the company name is = to the company selected in the first combo box.

        Hopefully this makes sense.

        Thanks Ahead

        • #1101800
          • #1101806

            Thank you for the link.
            I added the SELECT [tblContact].ID, [tblContact].Company FROM tblContact ORDER BY [Company]; statment to combo box cboCompany and when the form is ran it shows the primary key values. is there a way to offset one more column?

            • #1101807

              Set the following properties:
              Column Count: 2
              Column Widths: 0″;1″

              Setting the width of the first column effectively hides it, so that the second column is shown.

            • #1101813

              Another issue I seem to be having is with my code stopping on Me.cboCompanyRep.RowSource = “SELECT Company Rep FROM” & _
              ” tblContact WHERE Company = ” & _
              Me.cboCompany & _ < error grabing the value from the combobox
              saying Compile error:
              Method or data member not found

            • #1101815

              You should replace cboCompany with the exact name of the combo box on your form. If the name contains spaces or punctuation, place square brackets around the name: Me.[Combo Name]

            • #1101817

              Thank you for the tip. I am almost of fto the races having the event procedure not stop on anything.

              although nothing is showing up in the 2nd combobox I goto the vb scipt editor an stop the code to view the varibles and it seems to be grabbing the primay key value from the first combobox pick?

            • #1101825

              That’s correct.

            • #1101826

              I think my problem is when following the instructions on the MSDN link you have attached to the post. I do not know how to do the step:

              An Access 2007 database exists which contains two tables, tblCategories and tblProducts. The Category field in tblProducts is a lookup column that is linked to the Category field in the tblCategories table.

              Does this matter?

            • #1101834

              Perhaps you should explain in detail what you’re trying to do…

            • #1101872

              Sure,
              What I have setup is Two Tables,
              tblContact
              tblRequest_Contact

              tblContact Is a table that Stores customer details ex. Company, Company Rep, Street Address ect. for lists

              TblRequest_Contact is a table that will store jobs.

              I have created a Job fill out form that so far has 2 comboboxes and a text box.
              Combobox 1 is for picking a Company, which I want connect to the tblContact Company field to get a list and only List the unique companys so no multiples. The use will pick a company from the list and populate the Company field in tblRequest_Contact (Currently I have this working)
              Combobox2 is a Company Rep, Which I want to syncronize with the company combobox, so which ever company is picked then the list will show only customer reps belonging to the Company pick in the combobox list. I believe a query is needed that looks at the Combobox1 value and select only company reps that belong to that company.
              The Textbox is for An Address which again is a query, that reports multiple fields from the tblContact
              Street Address, Postal Code, Province, Country

            • #1101874

              Since a company can have multiple reps (I assume grin), you should have a table tblCompany that lists the companies. Each company will have a unique CompanyID (autonumber), plus a CompanyName and other information relevant to the company.
              The table tblContact contains a unique ContactID (autonumber), plus CompanyID (number, long integer) to link to tblCompany, plus ContactName and other information relevant to the contact.
              The first combo box will have a query based on tblCompany as Row Source, and the second combo box will have a query based on tblContact as Row Source.
              Both combo boxes have two columns, of which the first (which contains the unique key) will be hidden because its column width is set to 0. So the user selects a name, but the value of the combo box will be the corresponding ID.
              When the user selects a company in the first combo box, the After Update event procedure changes the SQL of the Row Source of the second combo box to return only contacts whose CompanyID equals the value of the first combo box.

            • #1101879

              Ok so what I gather from this is I need another Table called Company that lists the Company Names individually.
              I can then have a relationship with tblContact for getting the Companys information
              Correct?

            • #1101885

              Yes, the way I described in my previous reply.

            • #1101886

              Ok thank-you. So I have setup my Tables this way. as for the SQL statment behind combobox1:
              Option Compare Database

              Private Sub cboCompany_AfterUpdate()
              ‘ Update the row source of the cboProducts combo box
              ‘ when the user makes a selection in the cboCategories
              ‘ combo box.
              Me.cboCompanyRep.RowSource = “SELECT [Company Rep] FROM” & _
              ” tblContact WHERE CompanyID = ” & _
              Me.cboCompany & _
              ” ORDER BY [Company Rep]”

              Me.cboCompanyRep = Me.cboCompanyRep.ItemData(0)
              End Sub

              This doesnt seem to work anysuggestions?

            • #1101887

              Could you post a stripped down copy of your database? See post 401925 for instructions.

              Note: the database should be an .mdb, not an .accdb file, most Loungers (including me) don’t have Access 2007 yet, so they can’t open an .accdb.

            • #1101888

              Thanks

            • #1101897

              You should pay attention to the details, you had fields of the wrong type and superfluous fields. Please study the attached version of your database carefully.

    Viewing 0 reply threads
    Reply To: Setting up a combox using a table for list (Access 07)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: