linking table in a query (1 Viewer)

[sibbbra]

hi
I have a form named frm line total net. in its source query design , when i link the table suppliers, the fields in the form disappear as shown. where is the problem. plz help.

 

[arnelgp]

it disappear because you are Linking on the wrong fields.

 

[sibbbra]

it disappear because you are Linking on the wrong fields.

how to set that

 

[arnelgp]

you link tblOrderProduct to tblSupplier via Supplier_ID.
and don't use Inner Join.
Left Join tblSupplier to tblOrderProduct.

 

[bob fitz]

IMHO it would also be better NOT to use Lookup fields in tables.
Suggest you also use a consistent naming convention for Access objects. Perhaps something like e.g.
tblPhoneBook rather than Phone_Book, tblOrdProd rather than tblorderproduct, frmCust rather than Customers, and frmProds rather than Products.
Also, consider if your db is properly "normalized". Tables with nearly 100 fields, with names like Bank6_in and Bank6_out, etc, would suggest that it is not.

 

[sibbbra]

you link tblOrderProduct to tblSupplier via Supplier_ID.
and don't use Inner Join.
Left Join tblSupplier to tblOrderProduct.

thanx
but again, when i embedd this form into aonther and link relations e.g. Order_ID, the same thing hsppens again and fields disappear.
like in example below I cahnged as you said in frm line total purchase 2. When I embed it to All Supplier Oredrs , same happens. how to correct that plz.

 

[sibbbra]

IMHO it would also be better NOT to use Lookup fields in tables.
Suggest you also use a consistent naming convention for Access objects. Perhaps something like e.g.
tblPhoneBook rather than Phone_Book, tblOrdProd rather than tblorderproduct, frmCust rather than Customers, and frmProds rather than Products.
Also, consider if your db is properly "normalized". Tables with nearly 100 fields, with names like Bank6_in and Bank6_out, etc, would suggest that it is not.

thanx, I ll consider it

 

[bob fitz]

thanx, I ll consider it

Please do.
The use of lookups is widely acknowledged as being a bad practice that will cause you problems. See: http://access.mvps.org/access/lookupfields.htm
The lack of a naming convention will cause you headaches at some point when writing code because it will not be clear what the actual object you referencing is.
A db that is not normalized will cause you problems.

 

[Pat Hartman]

There is a lot wrong with this database starting with the schema. You have repeating groups in the Orders table ( I didn't look at any table not related to the problem so there may be more) You are using table level lookups but not using combos on forms!!! Makes no sense. I only looked at All Supplier Orders so I don't know where else you have made this mistake. It is definitely caused by confusion regarding the table level lookups. The subform "description" field needs to be a combo bound to ProductID, NOT a text field bound to Product.

I removed the table level lookups for the Order table but I didn't fix the form. Removing the table level lookups doesn't resolve the problem but it should help to clarify your understanding of how relationships work. You are also using poor naming discipline. Standards are your friend.

BUT, what fixed the data entry problem, was removing extraneous indexes on the Order table. I didn't look for or fix anything else. You have some relationship lines drawn in the Relationship window. They are not enforcing RI but they should be. Even though they are not enforcing RI, they are causing Access to create hidden indexes on the FK fields. You also manually created indexes on all the FK fields. Having two indexes on each of these fields was preventing the form from being updateable. When I removed the extraneous indexes, the subform became visible and was updateable.

What YOU need to do.

1. Go through EVERY table and create the proper relationships (there was none from tblOrderProduct to Product but there needs to be one).
2. Use Left joins instead of inner joins for ANY FK that is optional. If an FK is NOT optional, then fix the table definition to make it required.
3. Change ALL defaults for FK fields from 0 to Null regardless of whether they are required or not. Zero is never a valid value so it should not be the default. This isn't your fault. It is the fault of MS for making long integers default to 0 rather than null. This data type is rarely, if ever used for arithmetic. Their primary use is for FK's so the default should be Null rather than zero.
4. Remove all the table level lookups because they are confusing you and causing you to not make proper joins or bind the correct fields and use combos on forms. I'm not going to be wishy-washy about this. They are causing problems for you. Get rid of them. They are a crutch for people who don't know how to create a query. Once you can create a query, they do nothing for you but obfuscate actual data and confuse you because when you look at a field you don't see the actual stored value. NEVER format data at the table level. You always want to see the raw data when you open the table. Do your formatting on forms and reports and the occasional query that you have to export.
5. Go through every table and check the indexes. If you have manually created an index on ANY FK field, REMOVE it.
6. Go through every form and make sure you have bound the lookup fields to the FK field. You can remove the lookup table from the query unless you need it for other data items. In that case, look at all controls bound to the lookup and lock all of them EXCEPT for the combo bound to the FK.

Then you should get rid of the repeating groups and make them separate tables as they should be. Whenever you have more then 1 of something, you have many and many requires a second table.

I'm not posting a "fixed" database because technically it isn't fixed. You have a lot of clean up to do. I gave you the answer to why the form was acting the way it was but it is a very easy fix. Just do all the stuff in the steps.

 

[sibbbra]

There is a lot wrong with this database starting with the schema. You have repeating groups in the Orders table ( I didn't look at any table not related to the problem so there may be more) You are using table level lookups but not using combos on forms!!! Makes no sense. I only looked at All Supplier Orders so I don't know where else you have made this mistake. It is definitely caused by confusion regarding the table level lookups. The subform "description" field needs to be a combo bound to ProductID, NOT a text field bound to Product.

I removed the table level lookups for the Order table but I didn't fix the form. Removing the table level lookups doesn't resolve the problem but it should help to clarify your understanding of how relationships work. You are also using poor naming discipline. Standards are your friend.

BUT, what fixed the data entry problem, was removing extraneous indexes on the Order table. I didn't look for or fix anything else. You have some relationship lines drawn in the Relationship window. They are not enforcing RI but they should be. Even though they are not enforcing RI, they are causing Access to create hidden indexes on the FK fields. You also manually created indexes on all the FK fields. Having two indexes on each of these fields was preventing the form from being updateable. When I removed the extraneous indexes, the subform became visible and was updateable.

What YOU need to do.

1. Go through EVERY table and create the proper relationships (there was none from tblOrderProduct to Product but there needs to be one).
2. Use Left joins instead of inner joins for ANY FK that is optional. If an FK is NOT optional, then fix the table definition to make it required.
3. Change ALL defaults for FK fields from 0 to Null regardless of whether they are required or not. Zero is never a valid value so it should not be the default. This isn't your fault. It is the fault of MS for making long integers default to 0 rather than null. This data type is rarely, if ever used for arithmetic. Their primary use is for FK's so the default should be Null rather than zero.
4. Remove all the table level lookups because they are confusing you and causing you to not make proper joins or bind the correct fields and use combos on forms. I'm not going to be wishy-washy about this. They are causing problems for you. Get rid of them. They are a crutch for people who don't know how to create a query. Once you can create a query, they do nothing for you but obfuscate actual data and confuse you because when you look at a field you don't see the actual stored value. NEVER format data at the table level. You always want to see the raw data when you open the table. Do your formatting on forms and reports and the occasional query that you have to export.
5. Go through every table and check the indexes. If you have manually created an index on ANY FK field, REMOVE it.
6. Go through every form and make sure you have bound the lookup fields to the FK field. You can remove the lookup table from the query unless you need it for other data items. In that case, look at all controls bound to the lookup and lock all of them EXCEPT for the combo bound to the FK.

Then you should get rid of the repeating groups and make them separate tables as they should be. Whenever you have more then 1 of something, you have many and many requires a second table.

I'm not posting a "fixed" database because technically it isn't fixed. You have a lot of clean up to do. I gave you the answer to why the form was acting the way it was but it is a very easy fix. Just do all the stuff in the steps.

Thank You so much

Pat Hartman , I have now words to express my feelings, you got time for me . Do I owe you anything? what can I do for you plz. ​

 

[Pat Hartman]

@sibbbra All the experts who post here are volunteers. We help people because we like to give back to the community. We expect no payment except that you "pay it forward". You help someone else when you can

 

[sibbbra]

@sibbbra All the experts who post here are volunteers. We help people because we like to give back to the community. We expect no payment except that you "pay it forward". You help someone else when you can

thanx

 

[Pat Hartman]

You're welcome