[jaykojc]

Hi all. I am sure this is a fairly basic question which shows a lack of understanding of how Access works, so if its stupid, I apologize in advance...

I used the form builder to create a new input form for data. There is one selection for "Manager", which defaults to a text box that you can navigate with the arrows on the bottom of the sub-form, which has the manager id (primary key), the date, and the return associated with it. It works as intended, but the text box won't work long term.

I expect to have a decent number of managers, so I'd like to make that a drop down box so the user can select it, not have to scroll through one by one. I changed it to a combo box, and specified the query to populate the box. When I select a different manager than the default, I get an error which says, "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I think its trying to create a new manager with the selected name from the combo box, but it won't allow duplicates (which it shouldn't). What am I missing conceptually here?

[Rockn]

You probably have the current form bound to the table and using the dropdown will change what is in the underlying table. Probably bound to the managers table.

[jaykojc]

The box in the Data tab called "Row Source" represents the bound, correct? I have it linked to the query to pull up the data and that didn't work. Then I tried the table directly and change the bound column to the MgrName field, but when using the table it will only populate with the MgrId's. The fields in the MgrTbl are MgrId, MgrName, StatusId, StrategyId. If I want it to be a list of all available MgrNames, how do I bound it that way?

[OBP]

It should be bound to the Manager ID but display the manager Name.
You have the mgrId as the Control Source and column 1 in the combo with it's width set to 0cm. You have the MgrName as the second column with the width set to around 2.5cm.
That stores the ID but displays the name as it should.

[jaykojc]

Thanks for the help guys. Everything looks right, the box pulls the right field and the left/right clickers on the bottom take me forward or back a manager. When I click one of the manager names in the drop down, that doesn't work (when you click it just makes the 'this ain't right' sound). Do I need to specify somewhere what to do when you select from the box? An 'on click' or 'on change' bit? Or do I have one of the bits within the properties screwed up so it doesn't accept the selection?

Again, thanks for all the help. I realize these questions are 101.

[OBP]

Are you trying to "select" a Manager or "find" a Manager?

[jaykojc]

Select. I'd like to be able to pick the manager in the drop down box to limit the returns in the sub query below.

[OBP]

Does the mainform/subform have the Master/Child links set?
When you select a Manager it puts the value in the ManagerId field which should then work on the subform.

[jaykojc]

Well I was able to see that the problem was that MgrTbl.MgrId was an autonumber field, so I changed it to a regular number and reestablished the relationships in the table between MgrTbl.MgrId and ReturnTbl.MgrId. But then when I try to change the manager via the box, it says, "Cannot perform cascading operation. It would result in a duplicate key in table "."

The relationship between MgrTbl.MgrId and ReturnTbl.MgrId is one-to-many, enforce referential integrity, cascade update related records, cascade delete related records.

[Rockn]

Is it possible to post a sample database? It could probably be sorted out in 5 minutes and we can tell you how to fix it.

[jaykojc]

Here it is; I took out most of the data except a couple of sample managers. The form I'm trying to make work is "MgrReturnQuery1."

[Rockn]

Here ya go. The main form itself does not need to be bound to a table or any data source at all so I deleted that. Created a query to populate the sub form and had the dropdown lists afterUpdate event requery the sub form.

[jaykojc]

Everything works. Thanks so much for the help, guys.

[jaykojc]

How does one take the MgrId from the parent form and use it as the default in a new record for the subform? It is being used (but hidden) in the parent form; can I just pass from one to the other and default the new record?

[Rockn]

The MgrID is used in column 1 of the dropdown list. Just reference the Me.Dropdownlist control and pass that value.