Not saving records in subform

[Nima4444]

Hi
I have created a form containing a subform and in subform when i add a record and go to another record in main form and then come back, i can not see the record i had added. When i go to the tables I see that there are my records in subform but they are not linked to their foreign key that is in the main form.


There are some people with some IDs in main form and there are some products with some IDs in subform and when i go to tables i see that ID products that i added are there but the IDs of people that should be linked to them are not and all of them are 0 instead of for example 4
And I think this problem occurs randomly because I create another tables like these in another database and it works just fine
Can anyone help me please?

[ranman256]

in form design mode,
select the subform , set properties to the key field ( here = mykey):
LINK MASTER FIELDS = mykey
LINK CHILD FIELDS = mykey

when the subform is syncd to the keys, it will enter the key for you.

[Nima4444]

I've done that before but still the problem remains

[Missinglinq]

And I think this problem occurs randomly because I create another tables like these in another database and it works just fine

Sounds like corruption. Perhaps you could try the 'create a new file and import everything into it' routine...it's remarkable how often that works!

Barring that, we really need you to remove any confidential data, ZIP up the file and attach it so we can actually get our hands on it, in order to troubleshoot it for you.

Also, the earliest format you could save the file to, before zipping (preferably v2007) the more people here can open it and give a hand.

Linq ;0)>

[Nima4444]

Sorry but I couldn't save it to a previous version because it gave me some errors that there are some features in this version that can not be saved as a previous version.
I said that this seems to be random and it is! the words in my file were persian and I wanted to change them to English and then upload it and when I did this the form worked just fine!I just changed some names!!
Now in another part of my data base I have another form named Defects form.I have 2 problems in there now.I have a main form,a subform,a sub sub form and a sub sub sub form in it.when i change something in subform that's fine.When I add a record in sub sub form,go to another record in subform and then come back,it isn't there!when I go to the tables I see that they are there but when I come back to my form they aren't there.I have to say that the records that i have added in table itself can be shown in form but the records I add in forms can not be shown.
The other problem is that I have 4 levels of subforms but when I want to use form wizard and I asks me how do you want to view your data,I can just have a sub form and a sub sub form below it and my sub sum sub form sticks with my sub sub form in one table.I don't have problem with that but when I want to add a record in my sub sub sub form,an error occurs : "Cannot add records join key of table j4 not in recordset". I think that the problem is because my sub sub form and my sub sub sub form are in one table.

[ssanfu]

I would suggest you step back and fix a few things in the dB.

Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.

I renamed objects in the database window and I renamed fields in the tables, deleting the spaces and special characters (of the names I could read )

"Year", "Month", "Week" and "Day" are reserved words and built in functions in Access and shouldn't be used as object names.
Plus you have a special character in the name (bad).


I would also get rid of the look up FIELDS and calculated FIELDS.
Evils of look up FIELDS: http://www.theaccessweb.com/lookupfields.htm
Calculated FIELDS : http://allenbrowne.com/casu-14.html


Contracror.ContractorPhoneNumber should be a TEXT type field.
J2.ProductID_FK MUST be a Long Integer (it is a Double).


Because you have test data in the dB, I could only set Referential Integrity on one relationship.

[Nima4444]

Thank u so much for spending your time on my database,I really appreciate it.
I've done the things that you said and they were really really helpful because I didn't know most of them,but my problem still remains.
First of all I can't see the records that I add in my sub sub form (DefectID_FK) when I go to another record and come back,but I know they are added in table because when I go to the Table Day_Database they are there!
And second,I don't know what to do with my sub sub sub form (DefectGradeID_FK and Number_Of_Defects),because I still can't add records in there because of the error : "Cannot add records join key of table j4 not in recordset".
Here is my database after changing the names and all.

[ssanfu]

Ok, here goes.......

Naming the database with several dots (".") in the name is bad because that is what separates the file name from the extension.
Windows can get confused when a file name is like "DataBase.Up.1 (1).accdb". (there are 3 dots in the name... )

First of all I can't see the records that I add in my sub sub form (DefectID_FK) when I go to another record

You didn't say which form, so I'm guessing the main form is "DefectForm". But you do not have any "sub sub forms".
In the main form "DefectForm", you have 2 sub forms ("J1_Subform_DefectForm" and "J3_Subform_DefectForm").
To have a "sub sub form", you would have a main form (lets call it "MainForm"), within that form there would be a sub form (lets call it "SubForm1)" and within THAT sub form, another sub form, lets call it "SubForm2 (the "sub sub form").


You still have the calculated fields (which better done in queries) and Look up FIELDS (bad).
In the table "DAY_DATABASE" you have many look up FIELDS, which should be in data in tables. For instance, the field "TechnicalOperator". What are you going to do if/when you need to add/delete a "TechnicalOperator"? You (or someone) must go into the design of the dB and make the change. If the names were in a table, you could have a form to make the change. Anyone would be able understand and do the change. You've done this with the tables "Defect" and "DefectGrades". This should have been also been done (IMHO) with the fields "Machine", "Supervisor" & "TechnicalOperator". (I NEVER use Look up FIELDS in tables.)

And speaking of fields "Supervisor" & "TechnicalOperator", normally names are split into first and last. This makes it easier to search for and sort. If you want the full name, you can concatenate the names in a query.
(Apologies if I am wrong, but it looks like Arabic names in the table. Maybe the names would be split into 3 fields: given name (ism), patronym (nasab) and family name (nisba)?)

<snip> And second,I don't know what to do with my sub sub sub form (DefectGradeID_FK and Number_Of_Defects),because I still can't add records in there because of the error : "Cannot add records join key of table j4 not in recordset".

This is because of the form "J3_Subform_DefectForm" record source is:

the two table in the red box. That query is not updatable, which is causing the problem.
I don't understand why there are two tables, so I merged them into 1 table (I named it "J5").

I created a new sub form to replace "J3_Subform_DefectForm" named "J5_Subform_DefectForm".
I modified the control layout of the main form "DefectForm". (also the two sub forms)
When you look at the 3 forms in design view, you will notice the PK fields are hidden; PK fields should never be visible when the field is an autonumber.

The purpose of an Autonumber type field: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

See: Microsoft Access Tables: Primary Key Tips and Techniques




In the attached dB, open the form "DAY_DATABASE" (that is another thing. Having multiple objects named the same is confusing. I would use the prefixes "tblDAY_DATABASE" and "frmDAY_DATABASE").
In the sub form "J1", you will see the first record for Product is "HI". Notice the sub form "J5" has a record. Now, in sub form "J1", click on the record for product "Ok". Notice the record in sub form "J5".

Enough for now??
I'm going to .... then .

[Nima4444]

I don't know how to thank you and I can't believe that you spent so much time on my dB.Really thank you so much and I will do the changes for the fields like "supervisor" and others.
Now my dB is very good thanks to you but I'm afraid that next time that I want to create a dB these problem occur.You think these were because of just naming or something else that you didn't figure out?
Anyway I am very grateful and really appreciate it

[ssanfu]

Having a good naming convention is important, but wasn't causing your problems. Using only letters and numbers (maybe the underscore) in names definitely makes it easier to reference objects in queries and code.


The rule is "Normalize until it hurts, de-normailze until it works" (refering to table design).
Your tables "J3" and "J4" were in that situation (IMO), so I "de-normalized" them. This time it worked; maybe next time a different path would need to be taken.


Good luck with your project.......