Glitch in subform/main form linking?

[84hickman]

On my form I have a list box containing manager names. Below that list box is a subform. This subform shows the multiple store associated with that manager, multiple programs associated with each store, a percentage for each program, and a yes/no column.

The form is used to add and delete programs and stores from each manager's list and to edit the associated percentages. I've linked the subform to the main form using the manager's name. As planned, when I click on a name the subform updates to show only that manager's information below.

I seem to have a bit of a glitch (it, I've made some sort of error). The first name in the list of managers is "Aaron", his first store and program are 302 and F. The issue is this:

Say I select a different name in the list box (let's say Bradley). When I click on his name the appropriate information shows on the subform. I view his information and change some data, save and close the form.

If I now open the table which corresponds to the data in the subform I find that Bradley has now been assigned store 302, program F. This happens to the first store/program listed for the first manager every time.

I don't even know where to begin to fix this...
Hellllpppp please!!

 

[revans]

How are your LinkChild/MasterFields properties filled in? Did you put in the ManagerName field by hand/use the wizard or did you set up some code to dynamically fill them out?

Is your master form bound to a table?

Does your listbox refresh (through some kind of code) the subform or are you relying on the LinkChild/MasterFields properties to update the form for you?

You mentioned you save the form. How is this performed? A button with some code that saves the data? What is this code? Do you simply close the form (and the db saves the data on its own)?

I've seen a db overwrite data like this in the past and it usually is dependent on crazy dynamic links between the forms through code. I can't say I've seen this when using a simple drag-drop of subforms onto main forms. Also based on what you've said so far, if your main form in bound to the managers table it's possible the subform overwriting the managername from the table not the listbox.

hth,

Rich

 

[84hickman]

There really is no code on this form.
I made what I thought to be a fairly simple form that appeared to work correctly.

The subform was added by dragging and dropping onto the main form and using the Field Linker on the Property Sheet to link the two.

Both forms were made from the same table and therefore list the same table as the record source... could this be the issue? How do I go about fixing this??

 

[revans]

If both of your forms are based on the same table you have one of two fundamental design issues going on.

I would suggest you have two tables: one for the managers and one for the stores. They are two different entities and deserve a table each as they can each have different properties (fields that describe them). This is called normalizing data or database normalization. Check here (and other places on the web) for more info. With this set up your form/subform scenario should work fine.

Alternatively, you can keep your one table situation and just use one form. Include the store on the main form and change the store field to a combo box listing all the stores in the base table. I only recommend this if you don't plan on using the database for any length of time and this is simply a one-time thing you're working on. The problem with the one-table approach is you will quickly run into scalability issues, frex, a manager with multiple stores will be stored more than once in the table or will have multiple store fields. Your queries and reports will be crazy.

hth,

Rich

 

[84hickman]

I'm not doing a very good job of explaining my situation. The problem is not with my database design. I have many other queries/forms/reports that are all much more complicated and have no issues

Some info that may help:
I have a Store table -- Number, Name, Manager, Area Manager, Division Manager, Install Manager, etc..
I have a table for each type of manager that JUST stores the manager names
etc etc...
I have a table that is called "Bonus" with the following fields:
Install Manager which looksup from the Install Manager table.
Store which looks up the store number from the store table.
Program which looksup from the program table.
Percentage
Yes/No


My main form is based off of this last table. There is a list box pulling values from the Install Manager field.
My subform is is also based off of that table. It shows, in datasheet view, the info for the chosen install manger.

Hopefully this makes things a little more clear...

 

[revans]

The first name in the list of managers is "Aaron", his first store and program are 302 and F. The issue is this:

Say I select a different name in the list box (let's say Bradley). When I click on his name the appropriate information shows on the subform. I view his information and change some data, save and close the form.

If I now open the table which corresponds to the data in the subform I find that Bradley has now been assigned store 302, program F. This happens to the first store/program listed for the first manager every time.

I'm trying to recreate your scenario, and I think I have. I've got a listbox on the main form. The main form is bound to the same table my subform is bound to. The two forms are linked to the same field the listbox is drawing from.

I open the form to the first record, select a name in the listbox. The subform changes data to show that name (they're linked to the same field). Some data in the subform is edited then saved.

The problem here is I'm still on the first record. It used to be Aaron, now it's somebody else. That's why the first record's manager name is always changing.

So, are you using the main form to 'search' or select a manager and the subform to change data? If so, unbind your main form.

Or is the subform merely informational/read-only? Since the two forms are based on the same table, any change to the subform will be reflected in the record selected on the main form (with the proper refreshes of course). If so, don't change data in your subform, only change data with your main form.

Or, have I missed something?

I hope that helps.

Rich

 

[84hickman]

Thanks for all your help!
It makes sense that the issue is with both forms being bound to the same table.

If I remove the table from the "Record Source" line of the Property Sheet then I can't simply link the subform to the main form (because it is unbound).

I'm thinking that I'd have to create a query with the columns from the bonus table, enter the value from the list box on this form as the criteria for Install Manager, make a form based on this query, and place it on original (no unbound) form. Is there a simpler way than that?

 

[revans]

Hmm. I cheated. I named my listbox the same as my field name. So when I took the recordsource out of my main form I also took the controlsource out of my listbox (leaving the recordsource in the listbox so it can look up names). But the listbox kept the name the same.

The LinkMasterFields property of the subform would be set to the name of the listbox. Selecting a name in the listbox changes the subform data.

hth,

Rich

 

[84hickman]

Thanks so much! I've got it fixed!

Silly, but I never thought to just type the name of the field I was linking; I assumed that I couldn't do it since the "builder" didn't work on an unbound form.

Now my main form is unbound and the two forms are still linked through the Install Manager field.

Thanks again for the guidance.