Hiding Columns in my Subform Makes it stop working.

[Dontremb]

Ok, so, this is a bit of a problem. I've been trying for 3 days to figure out exactly what the issue is.

I've TECHNICALLY figured out a workaround, but I don't like it, and it also means I don't fully understand how it's working.

Ok, here goes:

I have a Many-to-many relationship with Personnel, Vacancies, and a Compilation table.

My form is from the Vacancy table, and displays all that information.

The Subform in that form is based on my compilation table, and shows some information from my Personnel table, as well.

I can add a new person to the subform just fine, and it automatically creates a new record in the Personnel Table, and the compilation table (which combines that person to that vacancy.)

My problem arises when I'm trying to assign a person I already have to that subform.

I have a combobox which is linked to my Personnel Table. It displays Last Name and first name, but actually uses my IDPersonnel (Primary Key) and assigns it to that field in my subform.

It works PERFECTLY as long as I have IDCompilation,IDPersonnel, and IDVacancy displayed in that subform.

But, when I try to "HIDE" them, it gives me the stupid "One-to-Many" error. In fact, I only have to have 2 of those columns displayed at a time. I checked and rechecked this, BTW. Not only that, but I can't even MOVE the columns to the end of the subform. (Well, I can move ONE of them, but I have to leave 2 in their original location.)

I have absolutely no idea why it's doing this, but I think I'm missing something on how the form/subform works. ANY help or ideas would be greatly appreciated.

Thank you very much.

 

[Dontremb]

Ok, in addition to that. If I RESIZE more than one of those columns to make them smaller, I also get the error, except that in addition to the error, my Personnel table gets a blank line with an IDPersonnel Number and assigns it to that IDVacancy Number.

It's making me absolutely crazy. The size of a column display shouldn't have any impact on how a subform works, right?!

 

[BJZeak]

Here are a couple ideas based on what I have done with subforms.

A) within the subform properties there are 2 fields called LINK CHILD and LINK MASTER which may be a help in some situations

AND OR

B) try looking at using explicit "<obj>.Requery" such that if you make a change on any object in the form you can explicitly refresh the others. ie if you have a text box that is critical to a subform, when you exit that Text object, you could call a global update function with a parameter that specifies the calling object ... the global function just has to cycle through and requery all of the form's ojects excluding the calling one. depending on the comlexity of the form, doing a contant requery may not be desirable for every object so use your best judgement

I have used both these methods to ensure correct data is shown on my forms at all times.

 

[BJZeak]

reading your comments again ... there are some subtle things that occur as I just experienced, see my last thread, when columns are hidden.

I too was getting frustrated as I spent at least 2 evenings with something that worked in one form but not another ... so I finally just started hacking away at the issue by making one change at a time with a basic form until I understood how the property changes affected the form's actions. The problem I find with getting answers from FAQ's is asking the right questions. The answeres are probably out there but I never seem to be able to find them because I don't use the right terms???

The project I am working on currently is to provide my user with a datasheet in a compact form so it would show fields of interest in columns on the datasheet but still dynamically show the hidden column details on the form as the user scrolls through the datasheet ... to do this I used SELECT * for the subform plus used the Form Wizzard to generate a form that contained ALL of the fields. I then hid all the columns that would be displayed on the form and used the FORM_CURRENT event as a trigger to update the form. The key to all of this working was the subform had to have all the fields defined. I started having weird issues when the form query was Selecting all fields and one or more fields were not on the form. The TAB STOP was also a stumbling block for me.

 

[Dontremb]

Thank you very much, BrixTreme, for responding. I've heard of LINK CHILD and LINK MASTER before, but I don't really know what that means.

Also, I read about TAB STOP problems before as well, but all my fields have a tab stop set to yes.

I'm not even trying to do anything crazy with my subform. It just shows the data from 2 tables (which are linked), and one field that does a look up.

I just can't understand why even RESIZING columns would prevent it from working. Or why it doesn't matter which 2 of the Primary Key/Foreign Key fields I have displayed, as long as there are at least 2 out of 3.

This boggles my mind.