G
Guest
I have a .dot that uses a userform to get a list of schools from an excel
spreadsheet and use them to populate a drop down box.
My AutoNew macro gets the information:
Sub AUTONEW()
Load UserForm1
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = opendatabase(SourceFile, False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
UserForm1.Show
End Sub
I have the userform set up that it has three check boxes to 'filter' the
data. Each click runs the update macro:
Sub UPDATELIST()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
Dim CONDITION As Integer
' Open the database
Set db = opendatabase(SourceFile, False, False, "Excel 8.0")
' Retrieve the recordset
'Stafford Only
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFF = 'X'")
End If
'PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X'")
End If
'GRAD PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
True Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND GPLUS = 'X'")
End If
'Stafford/PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = False
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and PLUS = 'X'")
End If
'PLUS/GRAD PLUS
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X' and GPLUS = 'X'")
End If
'Stafford/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and GPLUS = 'X'")
End If
'Stafford/Plus/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If
'None
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)
UserForm1.Repaint
' Cleanup
'Set rs.NoOfRecords = 0
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
Now, the form 'pauses' while it appears as though it's updating the records,
but when I click the drop down after the hourglass goes away, the records are
the same.
Do I need to clear the contents of the drop down box before repopulating?
If so, how do you clear a combo box's drop down list?
Thanks!
spreadsheet and use them to populate a drop down box.
My AutoNew macro gets the information:
Sub AUTONEW()
Load UserForm1
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = opendatabase(SourceFile, False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
UserForm1.Show
End Sub
I have the userform set up that it has three check boxes to 'filter' the
data. Each click runs the update macro:
Sub UPDATELIST()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
Dim CONDITION As Integer
' Open the database
Set db = opendatabase(SourceFile, False, False, "Excel 8.0")
' Retrieve the recordset
'Stafford Only
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFF = 'X'")
End If
'PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X'")
End If
'GRAD PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
True Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND GPLUS = 'X'")
End If
'Stafford/PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = False
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and PLUS = 'X'")
End If
'PLUS/GRAD PLUS
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X' and GPLUS = 'X'")
End If
'Stafford/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and GPLUS = 'X'")
End If
'Stafford/Plus/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If
'None
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)
UserForm1.Repaint
' Cleanup
'Set rs.NoOfRecords = 0
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
Now, the form 'pauses' while it appears as though it's updating the records,
but when I click the drop down after the hourglass goes away, the records are
the same.
Do I need to clear the contents of the drop down box before repopulating?
If so, how do you clear a combo box's drop down list?
Thanks!