Userform ComboBox loaded from Access

[ccarmody5]

This is my first post and I hope I'm posting in the correct place...

I am loading several userform combo boxes from an access file. Right now I have to use individual 'lookup' tables to load each combo box. I would really like to use one 'Lookup' table with different columns but can't seem to get it to work. It seems that if the columns don't have an equal amount of rows filled out the combo boxes come up blank. Any assistance would be great!!!

'///Load Model cbo
rst.Open "SELECT DISTINCT [Model] FROM Models ORDER BY [Model];", cnn, adOpenStatic
rst.MoveFirst
With Me.cboModel
.Clear
Do
.AddItem rst![Model]
rst.MoveNext
Loop Until rst.EOF
rst.Close
End With

[ccarmody5]

'///Load Model cbo
'The following line corrected the issue:
rst.Open "Select * from [Lookup] WHERE Department ORDER BY [Department]", cnn, adOpenStatic, adLockOptimistic
'Old code: rst.Open "SELECT DISTINCT [Model] FROM Models ORDER BY [Model];", cnn, adOpenStatic
rst.MoveFirst
With Me.cboModel
.Clear
Do
.AddItem rst![Model]
rst.MoveNext
Loop Until rst.EOF
rst.Close
End With

[Aflatoon]

FYI, you don't need a loop:

rst.Open "Select [Model] from [Lookup] WHERE Department ORDER BY [Department]", cnn, adOpenStatic, adLockOptimistic
Me.cboModel.Column = rst.GetRows

should suffice, and is also more efficient than returning a load of fields you don't need.

[ccarmody5]

Thank you Aflatoon! This should help speed up the loading when connecting through VPN.