Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: populating an unbound dropdown with a query

    It's been a while since I posted in large part because it has been a while since I coded in access so pardon my ignorance on this one.

    I've got a form that I'm using to allow the user to enter "event" data into a table. While my database is being used for something else, I think the easiest way to think about it is as if this was a tracking system that would allow a user to track interactions with a person (when do they call, when do they stop by, who were they with when they stopped by, etc.).

    My event data entry form is mostly unbound. There is are two fields at the top of the form that display the project name and number and there are a two fields that display the first and last name of the subject but the rest of the form is unbound. My thought was the user could select what sort of event this was (phone, visit, etc.) and who was involved with the subject (which sales person brought the client in for example) and then take notes and when the user clicks add this event it would run an input query and drop the data into the table.

    I've figured out how to do 99% of that. The part I'm missing is populating the drop down list with the sales people names. Here's the trouble... I've got a table that contains all the sales people names but some staff only works with some projects. I'd like to embed a query that populates the dropdown with a list of the sales people who might work with this client based on the project number. I can write the sql easy enough to produce this result but I can't figure out how to embed that sql statement into this dropdown.

    Thanks in advance for your help!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Set the SQL statement as the RowSource (?) of the drop down box.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Eureka! and a followup question...

    I knew I was forgetting something. I'll give that a go. So... can I use VBA to dynamically populat ethe row source? I ask because I was thinking I would dynamically generate the sql based on the contents of the form rather than try to pass a bunch of variables in... right now when I open the form it passes the key of the client record to the form, from that record I can populate the project information... I'd like to query the sales people based on the project information... if I were writing the sql in vba it would look something like:

    Code:
    strSQLHolder = "SELECT tblSalesPerson.FirstName_SalesPerson, tblSalesPerson.LastName_SalesPerson, tblProject.ProjectNumber_Project
    
    FROM tblProject INNER JOIN tblSalesPerson ON tblProject.ID_Project = tblSalesPerson.Project_SalesPerson 
    WHERE (tblProject.ProjectNumber_Project = '" & me.txtProjectNumber & "');"
    Can I just drop somethink like that in the row source or do I need to use VBA to generate the SQL onload or something else? Thanks for your help!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    YES! or is it NO!

    me.myList.rowsource = "SELECT this, that FROM here"
    works. any valid SELECT works. so do saved queries.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Alright... so I tried dropping that into the on_load and the on_current and neither seemed to get me my drop down to actually list anything. Did I miss a step? Should it be in the on_load or should it be in the on_current or does it not matter? Right now the VBA I have is as follows:

    Code:
    Me.cmbSalesPeople.RowSource = "SELECT tblSalesPeople.FirstName_SalesPeople, tblSalesPeople.LastName_SalesPeople, tblProject.ProjectNumber_Project " _
    & "FROM tblProject INNER JOIN tblSalesPeople ON tblProject.ID_Project = tblSalesPeople.Project_SalesPeople " _
    & "WHERE (tblProject.ProjectNumber_Project='" & Me.txtProjectNumber & "');"
    What am I doing wrong? Thanks for your help.

  6. #6
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    I figured out what I was missing... I just added:

    Code:
        Me.cmbSalesPeople.RowSourceType = "Table/Query"
    So... I dropped that into the On_Load and it worked like a charm! Thanks for your help on that!

    I do have another followup question. Is there a way to have it be a multi-column list int the drop down? Right now it is only showing the first column of data from my query (FirstName_SalesPeople). Thanks!

  7. #7
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by ArielZusya
    I do have another followup question. Is there a way to have it be a multi-column list int the drop down? Right now it is only showing the first column of data from my query (FirstName_SalesPeople). Thanks!
    In the Format tab of the Property Sheet for your combo box you'll see a field called "Column count". Make sure that's set to "3". By default, Access creates columns of equal width. If you have some fields that are longer/shorter than others, you may want to manually set the column widths as well.

    Another important property is the Bound Column, located on the Data tab of the property sheet. If your combo box is driving some other control on your form (say the Project Number feeds into the RowSource for another combo), make sure you set the Bound Column to the correct field. For example, if you wanted to bind to the Project Number, set the Bound Column property to "3", since that field is in the third column.

    Hope that helps.

  8. #8
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Brilliant! I knew it was going to be something simple. It has been months since I did any coding/Access work. Thank you for your help!

Posting Permissions

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