Limit Combo box records

[BFlat]

I have a combo box (CBA) on a form (FRMA) that has 11 fields. On the form I also have a text box (TXTA) that shows the current user's ID. Not all of the 11 fields are relevant to all of the users and some of them should just be viewable by me.



Is it possible to implement some type of code based on user ID text box (TXTA) to limit the fields/options that are availabe in the combo box (CBA)?

[pbaldy]

Sure; you'd test the user and set the row source property of the combo appropriately. Probably also have to change the column count and column widths properties. Another option would be to have 2 combos and display the appropriate one.

[BFlat]

I'm not necessarily sure how to change the record source of the combo box. Right now it is populated from a table (TABLEA) that lists the various reports. In the table are only the report names and an auto ID.

[pbaldy]

I thought there were 11 fields, that would be displayed or not depending on user? I guess I don't understand what you're trying to do.

[BFlat]

I have 11 diferent reports in the database. The name of each of the reports is listed in a table along with an auto id. The combo box I have on a form is populated by this table. Also on the forms is a text box that extracts the user id. Not all of the reprots should be viewable by all of the users and thus I would like to restrict what reports appear in the combo box for selection by the user. For instance User 1 only needs to select between reprots A, B, C and F and User 2 only needs to see reports A and C. Of course I would need to be able to see all the reports in the combo box.

[alcapps]

what is you data source for the combo box? can you list the query and what is the name of the textbox that has the UserId?
what is the name of form?
bottom line you need to add a criteria under the userid in your data query so that you have a where statement like this.

where useridfieldname = forms!formName!TextboxUseridName

once you do that after update of the textbox userID field
me.combo.requery
then your reports should change based on userid that changes..

[BFlat]

The data source for the combo box (CBA) is TBLA. All that is in the table are the name of the reports (and an associated autoID of course). The combo box is located on a form filter (that has no control souce). The userid feild is (TXTA) and the formname is FRMA.

There is no query in the form, combo box or text field.

[alcapps]

You could try and change the row source of the combo box to a query. Use tlbA pull in the fields you want and filter based on the Userid.
so something like this
CBA.rowsource = "select AutoID, ReportNameField from TBLA where Userid = 'TXTA' Order by ReportNameField"
in TBLA you can add a sort order field like SortOrder type long. (report 1 would get 10 and just incriment them by 10 report 2 would be 20)
then you can sort the reports in the order you want.
if you have a userid text box you can use that in your sql statement as well.. Say your text box is on the same form as txtUserID
CBA.rowsource = "select AutoID, ReportNameField from TBLA where Userid = forms!FRMA!txtUserID Order by ReportNameField"

change the Order by to Order by SortOrder
Anyway hope this helps..

[BFlat]

I was wondering if there was a code I could employ to make certain options at the combo box invisible. I know there are options such as CBA.listcount, CBA.listrows, CBA.rowsouce but i'm not sure which one would work.

[pbaldy]

That would typically be done with query criteria. You'd add a field to your table that enabled you to distinguish that way.