Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
Here's my scenario:
Userform1 Contains two (2) Labels (Label1 and Label2) and three (3) ComboBoxes (ComboBox1, ComboBox2 and ComboBox3): (System Type) Label1.Caption = Asphalt (Vendor) Label2.Caption = JonCo (Base Layer) ComboBox1 (Mid Layer) ComboBox2 (Top Layer) ComboBox 3 When the Userform_Initialize() routine runs, I would like it to check the Caption of Label2 for the vendor name (in this case JonCo) and then populate the list of ComboBox1, ComboBox2 and ComboBox3 with the correct items from the database; in this case jonco.mdb. IE: if Label2.Caption = AllanCo then myDb = OpenDatabase("allanco.mdb") jonco.mdb info: Only three (3) columns: prType, prUse and prName I don't know how to write the sql query string, but I've seen enough info by searching I can establish the database and the connection. I just need to load the data now... so here's what I need from the database: If the prType = hot and prUse = base then I need ComboBox1 to be populated with all matching entries from the prName column. If prType = hot and prUse = mid then populate the list of ComboBox2 and same thing for ComboBox3: if prType = hot and prUse = top then fill in ComboBox3 with all matching entries... It sounds easy to me, but I lack the Access experience to correctly construct the query string. Thanks in advance!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
Here is the SQL for ComboBox1:
SQL = "SELECT * FROM Table1 WHERE " _ & " Table1.prType =" & Chr(34) & "hot" _ & Chr(34) & " AND Table1.prUse =" _ & Chr(34) & "base" & Chr(34) & ";" Change the table name to suit and "base" for ComboBox1 and ComboBox2. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
Here is the SQL for ComboBox1:
SQL = "SELECT * FROM Table1 WHERE " _ & "Table1.prType =" & Chr(34) & "hot" _ & Chr(34) & " AND Table1.prUse =" _ & Chr(34) & "base" & Chr(34) & ";" Change the table name to suit and "base" for ComboBox2 and ComboBox3. Hth, Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
Oops. "for ComboBox2 and ComboBox3."
Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
Awesome. I will plug that in and see what I come up with. Thanks merjet!
"merjet" wrote: Here is the SQL for ComboBox1: SQL = "SELECT * FROM Table1 WHERE " _ & "Table1.prType =" & Chr(34) & "hot" _ & Chr(34) & " AND Table1.prUse =" _ & Chr(34) & "base" & Chr(34) & ";" Change the table name to suit and "base" for ComboBox2 and ComboBox3. Hth, Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
Well - I modified your code a little bit suit the changes I had made while
you were typing :) ...and ran it and got this error: "User defined type not defined" and it showed this code: Dim db As Database It's a bit long, but here's the entire snippet of code: ================================================== = Dim db As Database Dim rs1, rs2, rs3 As Recordset Dim sqlBase, sqlPly, sqlSurf As String '== Set database ==' Set db = OpenDatabase("roofing.mdb") '== Set "base" ==' Set sqlBase = "SELECT * FROM vList WHERE " _ & "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _ " AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _ " AND vList.prUse =" & Chr(34) & "base" & Chr(34) '== Set "interply" ==' Set sqlPly = "SELECT * FROM vList WHERE " _ & "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _ " AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _ " AND vList.prUse =" & Chr(34) & "interply" & Chr(34) '== Set "surfacing" ==' Set sqlSurf = "SELECT * FROM vList WHERE " _ & "vList.mftrName =" & Chr(34) & rsholder2.Caption & Chr(34) & _ " AND vList.prType =" & Chr(34) & rsholder1.Caption & Chr(34) & _ " AND vList.prUse =" & Chr(34) & "surfacing" & Chr(34) '== Set Recordsets for each ==' Set rs1 = db.OpenRecordset(sqlBase) Set rs2 = db.OpenRecordset(sqlPly) Set rs3 = db.OpenRecordset(sqlSurf) '== Load up ComboBoxes ==' With rs1 If Not .BOF Then .MoveFirst While Not .EOF With rshot05 .AddItem rs1("prName") End With .MoveNext Wend End With With rs2 If Not .BOF Then .MoveFirst While Not .EOF With rshot35 .AddItem rs2("prName") End With .MoveNext Wend End With With rs3 If Not .BOF Then .MoveFirst While Not .EOF With rshot38 .AddItem rs3("prName") End With .MoveNext Wend End With Set rs1 = Nothing Set rs2 = Nothing Set rs3 = Nothing db.Close Set db = Nothing ================================================== = "merjet" wrote: Here is the SQL for ComboBox1: SQL = "SELECT * FROM Table1 WHERE " _ & "Table1.prType =" & Chr(34) & "hot" _ & Chr(34) & " AND Table1.prUse =" _ & Chr(34) & "base" & Chr(34) & ";" Change the table name to suit and "base" for ComboBox2 and ComboBox3. Hth, Merjet |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
That sounds like you need a refence to Microsoft DAO x.x Object
Library using the menu Tools | References in the VB Editor. Merjet |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
It sounds like you need a reference to the Microsoft DAO x.x. Object
Library. Use the menu Tools | Reference in the VB Editor. Hth, Merjet |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
Microsoft DAO 3.6 Object Library is what's available in my system. I'll try
that. Do you think I'll need to add in any SQL reference library items? "merjet" wrote: It sounds like you need a reference to the Microsoft DAO x.x. Object Library. Use the menu Tools | Reference in the VB Editor. Hth, Merjet |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
It sounds like you need a reference to Microsoft DAO x.x Object
Library. Use the menu Tools | References in the VB Editor. Hth, Merjet |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate ComboBox list with Access data
It sounds like you need a reference to Microsoft DAO x.x Object
Library. Use the menu Tools | References in the VB Editor. Hth, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get Excel 07 combobox to populate from MS Access table? | Excel Discussion (Misc queries) | |||
Populate Combobox with Pivot Table Data | Excel Programming | |||
how to populate a combobox with a list of unique values? | Excel Programming | |||
Populate unique list in combobox | Excel Programming | |||
Having data populate text boxes based on Combobox Value | Excel Programming |