overriding combobox row source

[mitchmcc]

I have a form which allows users to either add a new employee, or to select one from the list to modify. In both cases, there is a separate (but shared) "work area".



One of the items is which "Group" the employee is in. For a new employee, it makes sense for this to be a combobox that is bound to a query for just the valid groups, and this works okay.

But if you want to modify an employee, when you click the employee in the main listbox, I want to be able to force the Group combobox to be that employee's current group.

I am sure this is a very basic problem, but I tried setting the "Value" and that did not seem to work, and you cannot set "Text" unless the control has the focus.

Thanks for any help!

Mmitch

[aytee111]

The Employee should be a combo box not a list box. The form should be bound to the Employee table. When you select an existing employee requery the form and it will display all the valid data for that employee. You can have an "Add New" button which would go to a new record.

There are lots of articles on how to get to a record, with different ways to accomplish this. My suggestion is just one way of doing it.

[mitchmcc]

Unfortunately, I cannot re-design the application. :-(

I am pretty sure that what I need and want to do should be possible, namely, to
set the value of a combo box based on another control being clicked, as opposed to
the user having to click the cb and select an entry. So that is my question... is there
a way to say:

cboGroup.value = txtGroupName

Mitch

[aytee111]

I don't know what you mean by you can't redesign it, you seem to be doing some redesign?

Also, how are the other fields being populated when the employee is selected? If the form is bound to the employee table then the group will automatically be shown just like all the other fields. Is it an unbound form? Are you populating all the fields manually one by one?

[ssanfu]

Yes, you can set the combo box to a "group name".

For the "Group", are you storing the name of the group (text) or a FK to a table of group names?

What is the SQL of the combo box rowsource?

What is the name of the list box?
What is the SQL of the rowsource of the list box?

[mitchmcc]

The SQL of the combo box row source is

SELECT Name from [Group List]

It is intended (as I noted in my original post) to allow a new Employee's group to be
selected only from the valid groups.

In the case where an employee needs to be modified, I still want to set the same
combo box to be *that* employee's group name, which I have in my VBA code.

I tried doing

cbo.Value = txtGroupName

but it did not seem to "take", and thus my question.

The SQL behind the main list box essentially gets all the employee data from the [Employee Table] and displays it via the list box in columns.

So when the user clicks on an employee name, the VBA code "knows" which employee is being selected (actually all of the employee data, including the group) and then simply fills in a separate row of data fields (including the Group name combo box in question) to allow the items to be changed and then save.

[ssanfu]

Well, you didn't post the SQL of the list box so I don't know what fields are available. So the next best thing is to create an example of how you might write the code. See the attached A2K mdb.

Also note that "Name" is a reserved word and should not be used as object names. A list of reserved words can be found at:

http://allenbrowne.com/AppIssueBadWord.html

Using a naming convention would help solve that problem. Also, spaces and special characters (except the underscore) shouldn't be used in object names.

[mitchmcc]

Thanks to all who replied. I found the problem yesterday. Instead of trying to set the combo box to the *name* of the group, I had to set it to the database Id.

This was probably so basic that no one ever thought about it.