Adding a blank row, or "All" to top of Combobox list

  • Thread starter Thread starter alfaista
  • Start date Start date
A

alfaista

Hello! I am working in 2007, I found some code in Help that showed me how to
add an All, or None, to the top row of a combo or list box.
The code was called, AddAllToList and you set your row source type property
to the code.
I am a proffecient programmer, but have never done this type of thing.

Well, it works GREAT!! But, my users are able to navigate off the form, add
a new record or update the records, and return to the form. I need to code
to rebuild the combobox and it is not. It appears it does not rebuild on the
Requery method.


I have pasted the code below It seems that the "intcode" coming into this
when a requery is done, is 8. But just adding 8 to the Case list does not
work, I think I would want it at the same level as initialize.

Um, so, anyone have any ideas or help!?!? (Oh, I renamed it AddAllToList1 so
I could have this code on more than one form at the same time.)

Function AddAllToList1(ctl As Control, lngID As Long, lngRow As Long, _
lngCol As Long, intCode As Integer) As Variant

Static dbs As Database, rst As Recordset
Static lngDisplayID As Long
Static intDisplayCol As Integer
Static strDisplayText As String
Dim intSemiColon As Integer

On Error GoTo Err_AddAllToList1
Select Case intCode
Case acLBInitialize
' See if function is already in use.
If lngDisplayID <> 0 Then
MsgBox "AddAllToList1 is already in use by another control!
Contact your database administrator."
AddAllToList1 = False
Exit Function
End If

' Parse the display column and display text from Tag property.
intDisplayCol = 1
strDisplayText = "<None>"
If Not IsNull(ctl.Tag) Then
intSemiColon = InStr(ctl.Tag, ";")
If intSemiColon = 0 Then
intDisplayCol = Val(ctl.Tag)
Else
intDisplayCol = Val(Left(ctl.Tag, intSemiColon - 1))
strDisplayText = Mid(ctl.Tag, intSemiColon + 1)
End If
End If

' Open the recordset defined in the RowSource property.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(ctl.RowSource, dbOpenSnapshot)

' Record and return the lngID for this function.
lngDisplayID = Timer
AddAllToList1 = lngDisplayID

Case acLBOpen
AddAllToList1 = lngDisplayID

Case acLBGetRowCount
' Return number of rows in recordset.
On Error Resume Next

rst.MoveLast
AddAllToList1 = rst.RecordCount + 1

Case acLBGetColumnCount
' Return number of fields (columns) in recordset.
AddAllToList1 = rst.Fields.count

Case acLBGetColumnWidth
AddAllToList1 = -1

Case acLBGetValue
If lngRow = 0 Then
If lngCol = intDisplayCol - 1 Then
AddAllToList1 = strDisplayText
Else
AddAllToList1 = Null
End If
Else

rst.MoveFirst
rst.Move lngRow - 1
AddAllToList1 = rst(lngCol)
End If
Case acLBEnd
lngDisplayID = 0
rst.Close
End Select

Bye_AddAllToList1:
Exit Function

Err_AddAllToList1:
MsgBox Err.Description, vbOKOnly + vbCritical, "AddAllToList1"
AddAllToList1 = False
Resume Bye_AddAllToList1

End Function
 
If this is not a long list (and it works well with 200 or so values) I'd
suggest another way.
Add Checkbox to teh tble you are picking from.
Create a subform that looks like a list box.
Add a "Select all" and "Clear All" button to the top of the form with
queries to set the check boxes True or False.
Results are based on queries where the check box is true.
The form will stay in it's current form until changed.
 
alfaista said:
Hello! I am working in 2007, I found some code in Help that showed me how
to
add an All, or None, to the top row of a combo or list box.
The code was called, AddAllToList and you set your row source type
property
to the code.
I am a proffecient programmer, but have never done this type of thing.

Well, it works GREAT!! But, my users are able to navigate off the form,
add
a new record or update the records, and return to the form. I need to
code
to rebuild the combobox and it is not. It appears it does not rebuild on
the
Requery method.
Click to expand...
You can add the row in your source query with a Union operator, so you don't
need any code. The combo box row source query could be something like this:
Select customerID, customerName
From Customer
Union All
Select Top 1 Null as customerID, "(All Customers)" as customerName
From Customer
Order By customerName

The parentheses in the All Customers name will sort it to the top of the
list.
 
Back
Top