Selecting a table from a database (DBCombo)

[PJB]

I couldn't get it to work with a dbcombo although i'm sure there's a way, i got the following to work with a listbox, and a regular combo (either or will do i just added them both in to see if it worked):

Dim objConn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()

Set objConn = New ADODB.Connection
     
            objConn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Program Files\Landlord\db1.mdb;"
     
Set rs = objConn.OpenSchema(adSchemaTables)
  Do Until rs.EOF = True
     List1.AddItem (rs!Table_Name)
        Combo1.AddItem (rs!Table_Name)
  rs.MoveNext
Loop

rs.Close
objConn.Close
End Sub

of course you'll need to change variables and paths etc...
hope it helps

[PJB]

that's odd, my response to your question is on top instead of where it should be, now that's confusing

[1dumbguy]

I think it's because I edited my question ( maybe while you were replying ).
Anyhow, In the general declarations where I placed the

Dim objConn As ADODB.Connection
Dim rs As ADODB.Recordset

It highlights the objConn As ADODB.Connection
And a compile error comes up as "User defined type not defined"

I gather that I'm not referencing my combo box to the objConn statement ?

If a man talks to himself , And his wife doesn't hear him. Is he still wrong ?

[PJB]

It's putting it on type in other threads also, i posted it in the forum feedback section so John is aware

Add a reference to Microsoft ActiveX Data Objects 2.5 Library

think that will take care of that error at least

[1dumbguy]

Database programming is confusing
I'm trying to use a DBCombo box to give me a list of tables in my db. I was wanting to be able to select one of the 3.
but can't figure out where and / or how to place the code. Am I on the right track ?

Private Sub DBTable_Click(Area As Integer)
Dim db As Database
'This is the object that will hold the connection
'to our database

Dim rs As RecordSets ( I'm thinking this is wrong too)
'This is the object that will let you select
'The table you want to work with

Set db = OpenDatabase("D:\projects\Bobdude\plas.mdb")
'This activates the database object, telling it
'to link to the plas.mdb database.
'NOW I'M SOOOOOO LOST
End Sub

Maybe I don't even want to use a DBCombo I'm getting the impression they have to be linked to a datacontrol.

Should I place this in my FormLoad Event ?

If a man talks to himself , And his wife doesn't hear him. Is he still wrong ?

[1dumbguy]

That done it ... But it gives me more than just my tables (lol).
It gives stuff like
MSysAces
MSysModules

Can I get a little more precise with the Openschema Statement ?

Thanks again
P.s. Your reply went above someone elses question too

[PJB]

Try this:

Dim objConn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()

Set objConn = New ADODB.Connection
     
            objConn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Program Files\Landlord\db1.mdb;"
     
Set rs = objConn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")) 'add this in
  Do Until rs.EOF = True
     List1.AddItem (rs!Table_NAME)
  rs.MoveNext
Loop

rs.Close
objConn.Close
End Sub

let me know if that works for ya

[1dumbguy]

That done it ! Thanks. I've been trying tutorials upon tutorials upon tutorials trying to figure that out.
Thank you
Bob

[PJB]

Cool! Glad it worked out for ya, Happy coding!

[1dumbguy]

PJB One more toughie while we're in the area. I used a combobox to hook up to the particular table. now I want to use another combo box to hook to a particular column. I tried adding this satement.Combo2.AddItem = "(SELECT * FROM [combo1] WHERE _" Color# = " & combo1.Recordset(Color#))"
I't didn't work

Code
Dim objConn As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Form_Load()
Set objConn = New ADODB.Connection

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\projects\Bobdude\plas.mdb;"

Set rs = objConn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))

Do Until rs.EOF = True
Combo1.AddItem (rs!Table_Name)

Combo2.AddItem = "(SELECT * FROM [combo1] WHERE _"
Color# = " & combo1.Recordset(Color#))"

Combo2.Refresh
rs.MoveNext
Loop

rs.Close
objConn.Close
End Sub

I said "One Day " I'll make you all proud. Not "Today".

[PJB]

I'm a little confused on this one, but to start with it didn't work because you can't use a select statement on a control (combo1), is color# a field in one of the tables? or is it a field in each of the tables? are you trying to set it up so when you select a table_name in combo1 you see what's in the color# field in that table in combo2?

give me as much info as ya can on what your trying to do and i'll do my darndest to help

[1dumbguy]

You are correct . ( color# is a field in all 3 of the tables). The 3 tables are Identical copies of each other. Color# is my Primary key. It identifies the components of a particular color. (I match colors for a living). Sometimes in order to achieve the match you have to tweak the individual bases / pigments. So I'm trying to record the trends that are being set ( If a customer orders the same color I want to make sure he gets the same color ). The next time you fly , look around the plane. Chances are good that 90% of everything around came from a little place in Kent Ohio.
ANYHOW (Enough sales pitches).
We have 3 different products ( Those are the 3 tables in my db).
Vinyl ( for flooring ).
Latex ( for walls and Overheads )
Print Inks (For designs)

Somebody said datacontrols are for sissies ( So I'm trying to achieve manlyness) with code.

Combo1 is what is selecting the table I want to open.
Combo2 is what I want to use to select the color# field from the tables to populate a DBGrid with the COMPLETE history of the color# selected.
Other fields in the tables are
wo# ( Holds the work order# for which the color was matched)
Base1 ( Holds the name of Base1)
%Base1
Base2 ( Holds the name of Base2)
%Base2
Pig 1 ( Holds the name of pigment#1)
%Pig1
Pig2 ( Holds the name of pigment#2)

I wanted to be able view a dbgrid for any discrepancies, Mainly for quality control ( I want to see if we're using too much of one or more components to achieve a perfect match )

I hope this'll help.

Thanks again ( And happy sunday to you ).

A bad day fishing is far better than any good day at work !

[PJB]

ok, think i have something for ya, i didn't do anything with a grid, but let me know if ya need a hand with it:

Dim objConn As ADODB.Connection 'move all of this to general declarations
Dim rs As ADODB.Recordset


Private Sub Combo1_Click()
Combo2.Clear 'add all of the following under combo1 click event
rs.Open "Select Color# From " & Combo1.Text
Do While Not rs.EOF
    Combo2.AddItem rs.Fields(0).Value
    rs.MoveNext
Loop
rs.Close
End Sub

Private Sub Form_Load()

Set objConn = New ADODB.Connection
     
            objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=D:\projects\Bobdude\plas.mdb;" 
     
Set rs = objConn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE")) 
  Do Until rs.EOF = True
     Combo1.AddItem (rs!Table_NAME)
  rs.MoveNext
Loop

rs.Close ' take close connection out of here
End Sub

Private Sub Form_Unload(Cancel As Integer)
objConn.Close 'move the close connection to here
End Sub

try that and see if it does the trick, be careful using stuff like "#" in field names, sometimes ya run into probs. with special characters

Keep me posted!

[1dumbguy]

AAAUughhh
Getting an Error stemming from the
rs.open "Select color# From " & Combo1.Text Statement.

" The operation requested by the application is not allowed if the object is open "

I tried playing with that statement , Failed.
Time to go fishing. The crappie are hitting too good to pass up !

Thanks for the help it is appreciated.
Bob

Doan ax me iez toopid !

[PJB]

make sure you have Rs.Close at the end of the Form_Load event and the Combo1_Click event, just put objConn.Close in the Form_Unload event

[1dumbguy]

Everything was right, I think my computer just needed a break. Thanks for all the help !
Bob

[PJB]

cool, glad to hear it!