CboBox row source - How to construct a list from a constant and a variable?

[RonL]

I want to dynamically construct a combox row source to designate a filter for a report. So it will be unbound, and I envision the row source type to be "list". I want the first member of the list to be a string, like "all" or "master." The remaining list items should be ascending numeric characters, starting with "1" and ending with "n", where n is drawn from a control bound to a field. (N represents the number of cameras used on a particular shoot. The idea is to build in flexibility to generate a particular camera's shotlist or the master shotlist, where the total number of cameras will vary by shoot.)

What's the proper way to construct that sort of row source?

Thanks.

[ranman256]

The point of the combox IS to bind it to a list you want to use. A query that reads from a table..in your case

tCameras
-----------
[shoot]
[Camera]

the query will pull the list of cameras for your particular shoot. Keep them in the list. This query can also filter via another combo (cboShoot)
Use tables and queries.

[RonL]

Thx. Not following, but guess I wasn't clear. Isn't the .value of cboMyControl (or lstMyControl) the text that is displayed? All I want is programmatic access to that value. But the possible choices are to be constructed from a mixture of a literal string and a bunch of calculated figures (derived from a single value - the total number of cameras). Guess I can write a UDF.

So let me rephrase: Will this work

cboMyBox.rowsource = MyUDF(ShootsID) where .rowsourcetype has been defined in design as "Value List" and the UDF resolves to a literal string, properly delineated for a cbo or lstbx value list?

[June7]

Use looping structure and AddItem method http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[ssanfu]

Isn't the .value of cboMyControl (or lstMyControl) the text that is displayed?

Not necessarily. It depends on what the Bound Column property is set to.

If you have a table/query with one column in the query, then yes, you are correct.
If you have a table/query that has 3 columns in the query, the bound column property can be set to column 1, 2 or 3. Usually, the bound column is 1, but I have seen where someone set the bound column to a different number.

If you are setting the Row Source Type property to "Value List" and there is only one column, then yes, the value of the combo box/list box is the value displayed.

What is the max number of cameras that could be on a shoot? Don't remember the total characters that can be put in a value list... (32,750)?

[RonL]

June, I forgot about that method. That's probably the way I'll go. Thank you! "Native" functionality much preferred to writing my own

Steve, thanks for the explanation. The help file for the .boundcolumn property says pretty much what you say:
http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx

However, the first sentence of the help on the .value property (which I realize is the default property for the control),
http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx
says: "The Value property is set to the text in the text box portion of the control." There's then some distinction made vs. the .text property, but nothing about binding.

BTW, if there was an issue caused by the .bound property, could I not use the .column(n) property to get the value I'd want?

At my (hobbiest) stage, this notion of binding is confusing. I thought that binding means that a change in a control's value will change an underlying table field represented in the controlsource. Since I don't have a controlsource in this case, I was thinking of it as unbound. Guess I gotta dig deeper into understanding the binding concept, since it appears to apply even to the rowsource. I think this is what Ranman was trying to tell me, doh!

No worries about the number of cameras. I guess Steve you're implying that that UDF approach would work. June's suggestion is still more sensible I think.

BTW, FWIW, there is a way to construct a query to do what I want for the rowsource. (I'd done it months ago in a test and forgot.) It will show only those cameras in the current recordset though. It combines a select distinct with the fixed text item(s) through a UNION operator.

Anyway, thank you to all. -Ron

[June7]

Confusing BoundColumn with ControlSource.

ControlSource binds to field (or in an UNBOUND textbox this can be an expression).

BoundColumn determines which column value is saved.

A combobox or listbox can have multiple columns and the BoundColumn determines which column sets the control's value, even if the ControlSource is not set (an UNBOUND control).