Many-To-Many Combo Problem

SteveNaw · July 19th, 2007, 07:54 AM

Ok I have 3 tables:

tblEmployee
tblEmployeeProject
tblProject

and I have 2 combo boxes:
lstProj
lstAvailProj

I am using this for my script:

Private Sub RequeryLists()

    ' declare local variables
    Dim strSource As String

    ' create source for "Project" list box
   strSource = "SELECT projectID FROM tblEmployeeProject " _
        & " WHERE employeeID = " & employeeID & ";"

    ' display Project Linked to Employees in list box
   lstProj.RowSource = strSource

    ' create source for "Available Projects" list box
   strSource = "SELECT projectID FROM tblProject " _
        & " WHERE projectID NOT IN " _
        & "(SELECT projectID FROM tblEmployeeProject " _
        & " WHERE employeeID = " & employeeID & ")" _
        & " ORDER BY 1 asc ; "

    ' display Projects still available in list box
   lstAvailProj.RowSource = strSource

Exit_RequeryLists:

    Exit Sub

Err_RequeryLists:

   MsgBoxErr.Description
    Resume Exit_RequeryLists

End Sub

----------------------------------------------------------

I have a button that runs the RequeryLists script, but
instead of the data from the tables coming up, just the
sql is printed in the box, the ";" however, is not in the
combo boxes. Is the script removing it or did I mess
something up? I have checked my SQL and that is right
when i just run a query with the SQL the data does come
up. Any help thanks

-Steve

gbianchi · July 19th, 2007, 08:09 AM

hi there.. you are a little confused ;)

see this: you are passing a Sql to the combo, but you are not passing any conection string to it so how will the combo connect to something??
Anyway, the above is not the method to do that.. One simple way to do this is to pass to the combo a datatable filled, and you only have to specify which column to show and which one to use as value.

Example:

Code:

        With yourcombo
            .DisplayMember = "Column to show"
            .ValueMember = "Column to use as value"
            .DataSource = AdataTable 'Can be others sources
            .SelectedIndex = -1 'to put it empty at the beginning
        End With

And one question.. why are you talking about scripts, there is no scripts in here, maybe functions or procedures...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

SteveNaw · July 19th, 2007, 08:15 AM

Ok i meant functions in my VB script sorry :p

Ok, so I can do that with both combo boxes that i have?

I want to show a list of projects that people are currently
on, and projects that are still available to them without
listing the same project twice.

Can i use .DataSource = (my SQL statement) ?

Thanks for quick response.

gbianchi · July 19th, 2007, 08:20 AM

again, no.. your sql statement is that, just a string.. it's doesn't even tell the control where to look for the info..
you have to pass a data structure to the combo, which was filled with all the actual data... somewhere you have to connect to your database and pull the data...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========