Two forms created from tables don't react the same way

[Exsubsailor]

I have 4 tables of workstations, workstation maintenance, Printers and printer maintenance. The forms were created from the tables. the maintenance sub forms are placed into the parent forms. When I add a maintenance event, the Workstation maintenance carries over the workstation name in the new record. The printer maintenance doesn't do this. I am unable to find the problem. Attached is my database.LT_Equiptment_test.zip

[aytee111]

Not sure how you are trying to accomplish this. First problem: never repeat names in tables, always carry ONLY the ID of the record, for both your workstations and your printers. That is part of what is called database normalization, not repeating data. One issue you will run into is if a name is changed on the main table, then it will be different on the secondary table and they will never match up again. Second problem, I can enter a different printer in the subform to the one displayed on the main form. Do not allow entry of this field.

[Exsubsailor]

aytee, I am looking for why the workstation maint subform carrys over the workstation name and the printer maint subform doesn't. And thanks for the suggestions for normalization. This is just a test database that I quickly threw together to include only an example of the forms exhibiting this issue. Simplifying to demonstrate what's happening.

[Homegrownandy]

there are bigger issues here than what you have asked.

your tables should only relate to one entity. the printers table should only have details for that one printer. If information is repeating itself then I would say you have done something wrong.

theres not actually a lot different but ill try and explain. Im looking at your printer maint table now. In the printer column it should show the ID of the printer being maintained. IDs have to be unique and that is how you relate your data.

I see you are actually doing this but youre doing it via the printer name. Whilst this may work for you now, its not scaleable, what if you have two of the same printers for example?

I'm not particularly good at access, but there are many on here who are. The best thing i think you could do is, outline what you are trying to achieve with the database as a whole, whats its purpose, who is it for, how many users.. any features. Then im sure you will be given some examples or advice of where to begin.

Failing that, please read about normalisaition.

[aytee111]

The reason is because the "Name" property is different to the Control Source property on the printer on the subform.

[aytee111]

No, that was me messing around. Access doesn't like it when you use reserved words as field names. I changed the fields in the tables to "Printer_Name" and then it worked.

[Exsubsailor]

Thank you for pointing that out! I have just reorganized my database to be "normalized" and I'm sure that will be a great move early on.

[Exsubsailor]

LT_Equiptment_test.zip
I have normalized my database as suggested but still have issues when I try to add maintenance records for Printers and Peripherals. Maintenance on workstations seems to work fine though.

[aytee111]

For your tables and fields, change the names everywhere that are Access reserved words. Also, in the relationships layout change all the joins to inner joins. The referential integrity of your data will be seriously out of whack with all these outer joins everywhere.

On the printer form, there is no way to add a new printer. When adding a maintenance record the links between the form and the subform is incorrect, the Master and Child link fields must be the same field. I suggest that you always have the same name between tables so that it is easily apparent which field belongs with which.

[Exsubsailor]

I finally located the issue. It had to do with the relationships in my forms source query. Once I removed all tables but the one for the parent form, everything worked fine. I ended up making another sub form to add the data I needed to the form as an interim fix but I do believe I can use more than one table in the parent source. Thanks all for the suggestions.