Subform problems - is it my database structure? (1 Viewer)

[Crash_Pie]

I am going around in circles. I am trying to design a database to submit for an assignment but it is driving me mad! I am not sure if the problem is with my actual database structure (eg the relationships) or the way I am trying to use the forms?

What I am trying to do is have a main form with several tabbed subforms. My main form doesn't actually do anything, it's just where the subforms reside (is that part of the problem?). The subforms should enable me to fill out information relating to a faulty item, eg, plumbing leak in a bathroom, I should also be able to set the priority, who requested the repair job, who logged the maintenance request, and the name of the company that will carry out the repair.

But, when I try and enter data in the first subform and then click on the tab for the next subform I get an error to do with not being able to enter data due to related records in a different table. Is that because of the enforcing of relationship integrity? I'm really quite lost with this, and I've experimented with all sorts of things, including changing primary and foreign keys, parent and child links with subforms etc, but all leading to other problems.

As a last resort I have hacked out the main part of the database that is causing me grief and have posted it here hoping someone will see the error of my ways and tell me how to resolve it.

I've searched through the forums on this site many times and also posted a few other problems too, I've have been able to fix all of the problems I've encountered before now, but this one has me stumped.

Any assistance would be greatly appreciated. Thanks.

 

[boblarson]

Your links in the Faults tab didn't match (you didn't have FaultID as both Master/Child) and the Job Requestor ID didn't need to have tblFaults.ReqID, it should just be ReqID and the same in the Service Provider tab.

It should work fine for you now. See attached.

 

[Crash_Pie]

Hi Bob

Thanks for having a look at the problem for me. I can see the changes you have made, but I still get the same errors as last time when I open your version.

If I open the Cutdown.mdb and use the main form record selector to select a new record, as soon as I enter data in any text box I get the error "You cannot assign a value to this object". After I click ok I can then actually add data, but then when I go to select a different tab I get "You cannot add or change a record because a related record is required in 'Tbl-ASGstaff''.

Did that happen for you?

 

[boblarson]

Sorry about that, I didn't really test much, just looked to see that the add new record button came enabled. I just tested much more and came up with this:

1. You need to unlink all of the subforms except the first tab
2. You need to change your input masks on your date fields to date input masks, including the form and in the table. The current input masks don't match with a date.
3. Change your link to the ASG Staff table in your relationships from Cascade Update to just Enforce RI.
4. Unlink the Fault subform from the main form and get rid of the recordsource of the main form; you don't need it.
5. You need all of the fields from the Faults table on the Faults tab. The other tabs right now are set up as input for each of the other tables, not the faults table.

 

[Crash_Pie]

Hi Bob

Thanks once again for the advice. In your reply, in step 1 you say not to unlink the first subform, but in step 4 you then say to unlink the first subform? Or have I missed something?

Re step 2, down here in New Zealand we use the date format dd mmm yy, so the masks work for me.

I've attached what I hope is the database with the changes you suggested, and you'll note that it still doesn't work. Once again after entering new data in the faults subform, when I select the next Tab I get the "You cannot add or change a record because a related record is required in 'Tbl-ASGstaff'' error.

Any help with resolving this is appreciated.

Thanks

 

[Crash_Pie]

Any chance anyone else can offer some advice please?

 

[boblarson]

Putting the other field in the faults tab on the faults subform was good, but you need to make them visible and the user needs to fill them out. Otherwise it does you no good to put them on there.

When I said to put them on there, I meant that they would need to fill out the information there. If you are trying to fill out that data on other tabs, then you are going about this incorrectly.

 

[Crash_Pie]

Hi again

Thanks for the advice, I really do think I've designed the structure wrong. I'll keep on experimenting and see what I come up with.

Cheers

 

[boblarson]

Now you CAN split up your data entry for the table into different tabs, but you don't need to use subforms for that, each tab doesn't need it's own recordsource. You could bind the main form to the faults table and have the various parts of that on each tab. And for the other tables you can have THEM on another tab using a subform, but not linked to the main form.

 

[Crash_Pie]

Hi Bob

That kind of sounds like what I was trying to do in the first place. I have moved all of the fields for the faults table onto the main form, with the other tables data on their respective subforms.

What I need the database to do is for every maintenance fault logged (Tbl_Faults), against each fault should be the corresponding record of who reported the fault (Tbl_JobRequesterDetails), which asset is faulty (Tbl_AssetDetails), and which contractor is going to carry out the repair (Tbl_ServiceProviderCompany).

If I add a new record to Tbl_Faults, how do I get all of the subforms to automatically increment to a new record as well so I can fill in their new details? I thought that by having Enforced Relationship Integrity ticked in the relationships, that this would happen? However it doesn't appear to be. How do I make the foreign keys increment by one? Will that solve my problem?

 

[boblarson]

If I add a new record to Tbl_Faults, how do I get all of the subforms to automatically increment to a new record as well so I can fill in their new details?

I think you still don't understand. Based on the tables you currently have, you DON'T need to have the subforms move to a new record or even linked to the main form as they are really not related. They are actually just lookup tables and the faults table is the only one that needs to be filled out for the fault. But, the others can be on there as subforms (UNLINKED subforms) so that you can add providers, ASG staff, etc. Those tables are NOT dependent upon the faults table and the faults table is not dependent on the others, other than to the extent that it gets the foreign key values from them via the combo boxes.

I guess the way to think of it is this way. If you have a fault, do you need to add a staff memeber to the ASG table for each fault that occurs? Of course not. You want, of course, to place in the faults table the id of the ASG staff, but you don't need to do anything with the ASG table to generate a record for the faults table.

 

[Crash_Pie]

Ahhh a light bulb is turning on somewhere..........

I think I've grasped what you've said, (And yes it does seem as though I had the completely wrong idea of how to use tabbed subforms) now can I ask how do I implement it?

Should I convert the foreign key text boxes on the main form to combo boxes to look up from their respective table?

In fact, from what you've said I really don't need the tabbed subforms at all?

 

[boblarson]

Should I convert the foreign key text boxes on the main form to combo boxes to look up from their respective table?

AH HA! I think you've got it.

In fact, from what you've said I really don't need the tabbed subforms at all?

You can still have the tabbed subforms if you want to have all of the maintenance of the lookup tables on the same form. But, essentially, you are correct - you don't need them.

 

[Crash_Pie]

Well there you go, I've made it work - of course it is totally different to what I originally planned but now it's even better!

Thank you very much for your patience and guidance Bob with helping me see the light! It's fantastic knowing that there are people like you on here willing to share their time and knowledge to help people like me who think they know what they're doing but in reality haven't got a clue.

No doubt you'll hear from me again....

Cheers

 

[boblarson]

Glad to hear you got it worked out. There's a LOT to learn about all of this and, even I know that I have a lot more to learn.