Populate ComboBox list with Access data

[IT_roofer]

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!!

 

[merjet]

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

 

[merjet]

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

 

[merjet]

Oops. "for ComboBox2 and ComboBox3."

Merjet

 

[IT_roofer]

Awesome. I will plug that in and see what I come up with. Thanks merjet!

 

[IT_roofer]

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]

That sounds like you need a refence to Microsoft DAO x.x Object
Library using the menu Tools | References in the VB Editor.

Merjet

 

[merjet]

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

 

[IT_roofer]

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]

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

 

[merjet]

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