Row Source -Table/Query

[joesmithf1]

Hi All,

This is a question that is a bit complicated to describe, so I'll try my best.

I have a form with a 'List Box' and i am using 'Table/Query' to fill the box. The table I am using is, "Department;" where it contains a list of all company's departments(Accounting, HR, Payroll, etc.). Now one thing that is NOT listed in the department table is the word "ALL."

So since I have to have this word in the List Box, so users can select "ALL" in the form, I'll have to manually go into the "Department" table to add it in, everytime I need to refresh the table with updated departments.

Is there anything that I can do VBScript code wise or in the "Row Source" in design view that I can fix this issue?

I under some might ask, how hard is it do add this in manually? Please keep in mind the 'department' list box is only one of the 5 tables that I have to refresh at least once a week, so I don't want to have to manually go in everytime to input the word ALL five time.

Thank you.

Joe

 

[Greyowlsl]

hi

This is all i could find for you its off a website, hope it helps.
,Leon


Adding "All" to a listbox or combobox:

If the RowSourceType of the control is a "Table/Query", there are two ways of doing this. One requires the use of an Union query, and the other one requires a callback function to fill the control. Generally using an Union query is easier. Callback functions are important in certain cases, but perhaps an overkill in this situation.

For example, if your combo's RowSource is this SQL statement

SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID;

you can then easily add "(All)" as the first choice. Also, if CustomerID is the bound field but it's width is set to zero (so that the user only sees CompanyName), you can store a NULL (if the bound field is not the primary key of the table), or someother value in the bound field.

SELECT CustomerID, CompanyName FROM Customers UNION Select Null as AllChoice , "(All)" as Bogus From Customers ORDER BY CustomerID;

If the RowSourceType is set to "Value List", you can simply concatenate "(All)" as the first choice when the form opens. So if the RowSource of the control Combo0 is

"Hello"; "World"

Then this code will change it to

"(All)";"Hello"; "World"

'******* Code Start ********
Private Sub Form_Open(Cancel As Integer)
With Me.Combo0
.RowSourceType = "Value List"
.RowSource = "(All);" & .RowSource
End With
End Sub
'******* Code End ********