Full Version: Struggling with strange subform behaviour - help needed.
UtterAccess Forums > Microsoft® Access > Access Forms

I'm really hoping someone can help me with this one. I've been stuck on it for the past two days, and its driving me crazy

Basically, i have a Main form for entering Tooling Information (Form A), this has a subform, Tool Spec (Form B). The relationship is defined as a 1 to 1. This is because any one particular tool can only have 1 spec or no spec at all (depends on the type of tool, if you can imagine).

My problem is, i'm putting together a data entry form, when i enter a tooling ID on form A, the ID is shown on (FormB) - thats fine (there linking correctly), as i expect. However, if i start to enter information on (Form B), and then i flick back to FormA and modify the tooling ID. It doesn't change the Tooling ID for that record, it creates a new record completely. My changes in form B are then lost and i end up with a row added in the tools table with that original ID (which is not what i require).

All i want to be able to do is enter a tooling ID, and then start entering details on the tool (in the subform), if i (or user) decides to change the tooling ID then i just want it to update the record that i'm working on. Not start a brand new tooling record, and save the old one as a seperate record.

I've tried creating a query/view bringing both sets of data together from table A and table B and this seems to work ( i do get a few update problems, "a user is currently editing this record, do you want to save changes to clipboard...))) its only on my machine at the moment (so another problem i guess) . However, because i need to link in several other tables, a view wouldn't be the best option, i was thinking of a tab form. With each tab housing a subform.

I'd appreciate any help you can spare on this one - thanks in advance.

Another similar thing, one of the other subforms stores image links. I have the same problem with that too. If i start a new record by adding a tooling ID, and then start adding some pictures to the images subform - say i then move out of the subform, back onto the main form (A) and change the digits of the ToolingID, the images are being stored under the old ID, even though i haven't explicitly saved the record (clicking forward arrow etc).

It's apparent i'm lacking some further/higher knowledge about subforms/relationships....please help if you can...

thanks Paul
Welcome to UA
o you have the frmMainA linked parent child to the fsubFormB? the table with the specs shuld have an FK that is the PK from the tools table. I am also a bit confused with this "Entering a tool ID" stuff.
Tool ID should be an autonumber generated by access. If you have identifiers that you use in the plant, then this is something different. I wuld be using the autogenerated number as my PK and this would be my FK in the specs table.
nce you get these linked that informatin will stay together as you scrll around.
Welcome to UtterAccess Discussion Forums!!!
My any chance is your main form data in the form's header and your subform in the details section? I ran into something similar recently and the solution was to make sure main form data and the subform were both in the details section.
Hello AQM_UK, thanks for responding.
I'll try to elaborate further, sorry for the confusion. Form A is linked to subformB via a ToolID (unique number). ToolID is the primary key of both respective tables. The relationship between them is one-to-one (as the subform (table b) is really a possible extension of table A, if you want to think of it as that).
Now lets say i go into my data entry form, which consists of main form A, and subform B. I enter a new toolID number (uniquely generated outside of access, but still unique for all intents and purposes). After entering a toolID, i then move over to the subform (tool ID is carried over into subform, as expected) I enter some tool spec information there. Before i finish completing the tool spec information in subform B, i decide to move back to subform A and modify the ToolID (say add another digit on the end). When i do this access doesn't update the record i'm working on, it actually saves the record with that old ToolID value (i checked the underlying table), and creates a new record(which i don't want) with the modified toolID. Because the subform is linked on ToolID, it updates to point to a new record as well, losing the details i just entered.
Thanks for the input....appreciate that.
Hi Glenn,
do have a header but it doesn't include any bound fields. All the fields (tab control, subforms) are in the detail section.
Sorry then.
The problem you are describing sound similar to what I was dealing with but I guess the similarity ends there.
Hi pj.
You are doing things a bit wrong here.
I am worried that you say ToolID is the PK in both tables. It should be the PK in the TableA and the FK in TableB and TableB should have its own PK called SpecID (or something like that)
The next thing that worries me is this.
If it is unique from outside the system, why would you be modifying this by adding another digit on the end????
The next thing you are trying to do is this.
You add your unique number and this is linked parent/child. When you go into the subfrm and start to create data, the linked parent control will populate the same control (which should be your FK) in the subform.
Now, for this inexplciable reason you change your PK in the main form, and expect this to automatically update the child. What you have just done is create and orphan. As the record already exists in the subform it can nly create a new record and you have lost the link when you changed your key.
So I point you back to my first post and say yu should not be using this number for anything other than the user.
You should use your access autonumber as the PK in the main form and this should be the FK in the TableB. Then your other unique number should be a text field in the table and be used as a form of description for the tool.
When you have done this, everything will work properly.
Hey Jim,
Thanks for your input, i really appreciate this.
Just going back to your first comment "If it is unique from outside the system, why would you be modifying this by adding another digit on the end".
The reason being Jim, is that while they are entering an asset ID, a user may decide that they haven't entered it correctly, or maybe they are just testing to see what happens when they do change it (stress test). My point being, is that if they do change it, it shouldn't save the old value as a record in the database, the entire record is still being edited, i don't want it to make an implicit save until the user is happy with all the values on both forms (and, only then save it).
Your second point about updating the subform, is a good one and your right the child does update with the ToolID from the main form. However, i can't create multiple ophans anyway, because the relationship is 1 to1 between form A (table a) and subform b (table b) - integrity rules (Referential) prevents this from happening (and rightly so). Its strictly a one to one relationship between these two tables (forms A-B)
Because i am modifying the ToolID (its actually called assetID, i was trying to simplify its meaning in this context slightly), MS access seems to think..."oh right...he wants to save that old ToolID as a record, and create another record with this new value" - which is wrong.
All i want to be able to do is say "hey, i've got a record that i'm currently editing, oh! one sec i wanna change this asset no, i got it wrong first time, so i modify it (access then updates the modified toolID in the child too) and success - job done!". This is at the data entry stage too, not editing existing records i should add (if they edit a record later, once they've added a new one, they won't be able to change toolId, it will be set in stone) .
Futhermore ToolID (or asset ID in r/l world terms) is unqiue thats why it is used as the primary key for all my tables, along with some compound keys when needed.
I want use another layer for primary keys( in the form of autonumber fields, good point by the way). This i feel would be redundant though, as users would never search on autonumbers, and it would mean alot more indexes unnecessarily. ToolID is a unqiue value across all tables in the DB, it should be suitable.
There has to be a simple solution to this, other than to unbound all the controls, get them to fill in everything and then write all the unbound controls to the corresponding tables, which by this point i simply don't have the time to do i regret to say.
go back to your design which is flawed and this is why you are in trouble.
The autonumbers are not for the users, they are for the developers. They also take up small amounts of data storage.
Orepeat, use your "Unique" tool ID as a description. You link that to the autonumber PK later and this is how you search. The user thinks they are using the "unique" key, but you are using the PK/FK. This also means that this data is only stored once, in the TableA.
You are getting into trouble because you are modifying your PK. YOU SHOULD NEVER DO THIS.
So, take my advise, or ignore me.
If you take my advice yur database will work. If you ignore me, then why bother to post to the forum in the first place.
Okay Jim.
I'm going to run with your idea. I know you must be banging your head against the wall by now - i am sorry.
Would you recommend i propogate this change throughout all my tables? You see there are many child tables running off (table a), all linked by ToolId (asset number in r/l). For example asset images, asset types, suppliers, asset location history, asset modification, asset part production, remove ToolId as a primary (make it a normal field. but indexed) from these too and have autonumbers PK's as global identifiers?
There are one or two, 1 to 1 relationships too (as mentioned above), it goes against my judgement to change these to 1 to many as well - what do you think
Thanks again, for your help.
Well, it comes to a design issue. Hwever, I would retrspectively try to get a PK/FK accross all tables. Hwever, if the others are wrking yu may be able to get away with just one fix.
If they are 1 to 1, then keep is this way.
I'm making the necessary changes retrospectively, on what is already in place. Thanks for help, i really do appreciate it - i'd be stuck otherwise.
Is it possible to fire (programmatically) an event of a control on a subform, from a main form.
Something along the lines of, raise_event (mainform.subform.control.event)?
Well it is possible to have something happen on a subform when an event is triggered on a main form.
Here are the naming conventions for main/ sub and subsub forms.
That's a very useful table - I appreciate the help there. Thanks Jim
No problem Paul.
evert anytime you are stuck.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.