Filling several Combo Boxes on Form Load 1

[rtshort]

I know there has to be a better way to fill several Combo Boxes on a form. I'm using the SSTab Control and have 3 Combo Boxes that I need to fill from a database when the form loads. I tried using the click event, but I was going in circles.

This is what I have. It works, but as I said, there has to be a better way.

Private Sub Form_Load()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = "Driver=SQL Server;uid=;pwd=;Server=Server;database=Test"
cn.Open
sql = "Select * from Hulls"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF = True
cboHulMake.AddItem rs.Fields("HulMake&quot.Value
rs.MoveNext
Loop
rs.Close
sql = "Select * from SternDrives"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF = True
cboSdrMake.AddItem rs.Fields("SdrMake&quot.Value
rs.MoveNext
Loop
rs.Close
sql = "Select * from Transmissions"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF = True
cboTrnMake.AddItem rs.Fields("TrnMake&quot.Value
rs.MoveNext
Loop
rs.Close
End Sub

I am open for suggestions if someone has one, or Two. Rob
Just my $.02.

 

[dpaulson]

The only suggestion that I would make is that if your not going to use the recordset for anything else, why not just retrieve the fields that you need. It should execute a bit faster. If you open the recordset as forwardonly, it will speed up a bit more.

cn.Open
sql = "Select HulMake from Hulls"
rs.Open sql, cn, adOpenForwardOnly, adLockOptimistic
Do Until rs.EOF = True
cboHulMake.AddItem rs.Fields("HulMake&quot.Value
rs.MoveNext
Loop
rs.Close
David Paulson

 

[rtshort]

Thanks David. I need all of the records, but it probably won't be over 25 or so in each of the 3 databases. That should be OK. I'll try the ForwardOnly though.

I was wondering if filling up 3 ComboBoxes back to back, on the Form Load Event, was bad programming or if it was acceptable.

Thanks again. Rob
Just my $.02.

 

[cheers]

A better way to do this is to use classes. Create a class module called Hull. It should something like this. Of course, all the names should match those in your db.

Private m_Hull_ID As Long
Private m_Name As String
Private m_length As Long

Public Property Get Name() As String
Name = m_Name
End Property

Public Property Let Name(v As String)
m_Name = v
End Property

Public Property Get Hull_ID() As Long
Hull_ID = m_Hull_ID
End Property

Public Property Let Hull_ID(v As Long)
m_Hull_ID = v
End Property

Public Property Get length() As Long
length = m_length
End Property

Public Property Let length(v As Long)
m_length = v
End Property

Then create a class module called Hulls. This module will contain functions for your Hull class. It will look something like this:

Private Col As New Collection
Private cls As clsHull

Public Sub Load_Combo(c As ComboBox)

c.Clear
For Each cls In Col
c.AddItem cls.Name
c.ItemData(c.NewIndex) = cls.Hull_ID
Next cls

End Sub


Now, let's suppose the name of your db is Boats and the name of your table is Hulls. Create a system DSN called Boat - Tabels and when you load your form put this in the form load event:

private sub Form_Load()

Dim Conn as ADODB.Connection
Dim RS as ADODB.Recordset

Set Conn = New ADODB.Connection

Conn.ConnectionString = "DSN=Boat - Tabels;UID=;PWD=;DATABASE=Boats"

Conn.Open

'Create the recordset
Set RS = Conn.Execute("Select name from Hulls&quot

'Now comes the easy part. If you have a combo on your form
'named cboHulls, then load it like this:

Hulls.Load_Combo Me.cboHulls
'this will show the first item in the list then
me.cboHulls.ListIndex = 0

End Sub


That's it!. Pretty darn simple once you get the hang of it.

Hope this helps

Ray

 

[rtshort]

Thanks Ray for the response. I'm trying to go DSN-less with this app. Also the above code fills 3 different ComboBoxes from 3 different tables. (Hull for make, Transmission for make, and SternDrive for make) All of the other information in the remaining ComboBoxes comes from a select statement from a database using the mid function to trim the table name to just the information that needs to be in the Combo Box.

If I could do this in a class I would be interested in doing so. I've only been at this for about a year teaching myself as I go along and haven't gotten up on classes and .bas modules yet.

Thanks again
Rob
Just my $.02.

 

[cheers]

OK, so DSN-less connections is not a problem. Your connection will look like the following and make sure it is on one line.

Conn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"

Then the load combos would all be the same. Remember, you MUST make a class for each object so you can fill the combo's properly.

As for the connection stuff, here is a link that everyone should have for reference to this type of stuff:

http://www.able-consulting.com/tech.htm

Take the time and learn how to do classes. You will never go back.

Hope this helps!

Ray

 

[rtshort]

Thanks Ray, I'll try it. Rob
Just my $.02.