PDA

View Full Version : [SOLVED:] Userform ComboBox loaded from Access



ccarmody5
12-09-2015, 08:04 PM
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
12-09-2015, 08:59 PM
'///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
12-10-2015, 04:42 AM
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
12-10-2015, 07:54 AM
Thank you Aflatoon! This should help speed up the loading when connecting through VPN.