ID field in subform

[DavidCantor]

I have a form with a product ID and subforms with other fields from the same table. I enter the ID in the main form but am not able to enter data into the fields in the subform. I get an error that the PK cannot be a null value. How do I link the subform to the form so that the ID I enter in the main form will be applied to the data in the subform too.

thanks

 

[boblarson]

You don't use the same table on a main form and a subform. Why are you doing it this way instead of just having one form without the subform?

 

[DavidCantor]

I have subforms because the table has many fields, I created the subforms to display the fields under categories

 

[boblarson]

I have subforms because the table has many fields, I created the subforms to display the fields under categories

Sounds to me that your table structure is not properly normalized.
Read this - http://support.microsoft.com/kb/283878

Can you post a screenshot of your table structure (Relationships Window)? Or a copy of the database?

 

[DavidCantor]

I have one table with 100 fields joined as 1 to 1 to three more tables that have 30 fields each

 

[DavidCantor]

i cannot split the tables any more

 

[boblarson]

i cannot split the tables any more

I'll betcha you can. Wanna make the bet?

 

[DavidCantor]

the main table is product information, then I have three tables for the icons displayed on the products. The icon table has 50 possible values and the user needs to select when the icon value is present and its location. I have not found a way to break the tables down.

 

[boblarson]

I have not found a way to break the tables down.

Are you just going to keep stating that or are you going to post either a copy of the database (with fake data) or at least a screenshot of your relationships with all of the fields showing?

Just because you have not found a way to do it doesn't mean there isn't a way.

 

[DavidCantor]

I attached the relationships. each table has more fields the icon tables have many more nutrients and the symbols table different types of symnols

 

[boblarson]

You didn't show all of the fields in the tables but immediately I know that it is not properly designed. You should not have repeating fields.

So, having fields like

B_Smart_Choices_Location_1
B_Smart_Choices_Location_2
B_Smart_Choices_Location_3

and

tblcon_2
and
tblcon_3

with identical but slightly different names (using the table number) is totally off.


So the big question is -

Do you want help getting it to the proper point? We are willing to help but only if you want to do it right.

 

[DavidCantor]

yes of course I do, what are your suggestions?

 

[boblarson]

yes of course I do, what are your suggestions?

A good starting point would be if you can just upload a shell database with only the tables that you currently have and it can be without data, although having some basic fake data in there could help reduce the number of questions we'll have to ask as I don't know your business so that will be important.

 

[DavidCantor]

how can I send it to you without posting it here in this forum

 

[boblarson]

how can I send it to you without posting it here in this forum

Sorry, I thought based on your PM you meant to post it here.

I can PM you an email address to send it to but it will be at least 10 hours before I can get to it as I can't download attachments to email at work.

 

[boblarson]

Did you ever send this to me? I haven't seen it. I sent you a PM (private message). Did you get that?