Adding a value to a ComboBox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello

The record source of the combo box is a Table/Query, I use in my code :
Combo1.RowSource = SqlQry

But I want to add the String "ALL" to my combo box I don't want to add this
value into the table, this value will allow me to see all the value in the
combo box and to stop the Query finction

Thanks
 
Miss Granger,

By way of example, if we assume that SqlQry is:
SELECT PKField, Field1, Field2
FROM tblTable

(Assuming PKField is the primary key)

....then to add "ALL" to the listbox, we need to create a special query for
it:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2
FROM MSysObjects
WHERE [Name] = "Forms"

Then we need to join them together in a UNION query:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2
FROM MSysObjects
WHERE [Name] = "MSysObjects"
UNION

SELECT PKField, Field1, Field2
FROM tblTable

To ensure that "ALL" is always the first record in the list, we need to
specify a custom sort order that is independent of (and doesn't alter) the
sort order defined in SqlSry:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2, 0 As SortOrder
FROM MSysObjects
WHERE [Name] = "MSysObjects"
UNION ALL

SELECT PKField, Field1, Field2, 1 As SortOrder
FROM tblTable
ORDER BY SortOrder

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thank you very much Graham

I'd like to know if you can help with cascading Combo Box.

I have 6 combo box in my form and I want to filter each Combo box depending
on what the user choose in one of the combo box, and each time the user
choose a value in a combo box I'll filter the reste of them

Thanks very very much


Graham R Seach said:
Miss Granger,

By way of example, if we assume that SqlQry is:
SELECT PKField, Field1, Field2
FROM tblTable

(Assuming PKField is the primary key)

....then to add "ALL" to the listbox, we need to create a special query for
it:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2
FROM MSysObjects
WHERE [Name] = "Forms"

Then we need to join them together in a UNION query:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2
FROM MSysObjects
WHERE [Name] = "MSysObjects"
UNION

SELECT PKField, Field1, Field2
FROM tblTable

To ensure that "ALL" is always the first record in the list, we need to
specify a custom sort order that is independent of (and doesn't alter) the
sort order defined in SqlSry:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2, 0 As SortOrder
FROM MSysObjects
WHERE [Name] = "MSysObjects"
UNION ALL

SELECT PKField, Field1, Field2, 1 As SortOrder
FROM tblTable
ORDER BY SortOrder

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Hermione said:
hello

The record source of the combo box is a Table/Query, I use in my code :
Combo1.RowSource = SqlQry

But I want to add the String "ALL" to my combo box I don't want to add
this
value into the table, this value will allow me to see all the value in the
combo box and to stop the Query finction

Thanks
Click to expand...
Click to expand...
 
Hermione,

Cascading combo boxes? No problem:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Hermione said:
Thank you very much Graham

I'd like to know if you can help with cascading Combo Box.

I have 6 combo box in my form and I want to filter each Combo box
depending
on what the user choose in one of the combo box, and each time the user
choose a value in a combo box I'll filter the reste of them

Thanks very very much


Graham R Seach said:
Miss Granger,

By way of example, if we assume that SqlQry is:
SELECT PKField, Field1, Field2
FROM tblTable

(Assuming PKField is the primary key)

....then to add "ALL" to the listbox, we need to create a special query
for
it:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2
FROM MSysObjects
WHERE [Name] = "Forms"

Then we need to join them together in a UNION query:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2
FROM MSysObjects
WHERE [Name] = "MSysObjects"
UNION

SELECT PKField, Field1, Field2
FROM tblTable

To ensure that "ALL" is always the first record in the list, we need to
specify a custom sort order that is independent of (and doesn't alter)
the
sort order defined in SqlSry:
SELECT 0 As PKField, "ALL" As Field1, Null As Field2, 0 As SortOrder
FROM MSysObjects
WHERE [Name] = "MSysObjects"
UNION ALL

SELECT PKField, Field1, Field2, 1 As SortOrder
FROM tblTable
ORDER BY SortOrder

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Hermione said:
hello

The record source of the combo box is a Table/Query, I use in my code :
Combo1.RowSource = SqlQry

But I want to add the String "ALL" to my combo box I don't want to add
this
value into the table, this value will allow me to see all the value in
the
combo box and to stop the Query finction

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top