Need help asap, huge thxs!!! (1 Viewer)

[ccxxxu]

I have several tables with same column A, the column A in the parent table is a primary key,
I checked casade update option in the relationship, they are the same value type.

But each time I add a new value to the parent table, the child table is not adding any new records....

pls help
THANKS!

 

[MajP]

What you describe is not a normal database function and is not related to cascade updates.

 

[ccxxxu]

What you describe is not a normal database function and is not related to cascade updates.

I thought that was a super basic function... cuz people cannot mannual add same records to all datatables

still thanks!!
but do u know any method that can realize that function?

each time I add a record in parent datatable, all child datatable automatically added

 

[June7]

This calls for code (macro or VBA) running an INSERT action SQL or VBA adding data to a recordset object. The only coding that can be done behind tables is with DataMacros, which I've never used.

Why can't people do normal data entry? Users should only interact with forms and reports, not tables and queries.

How many "all datatables". I suspect data structure is not optimized nor normalized. Could provide db for analysis. Follow instructions at bottom of my post.

 

[ccxxxu]

This calls for code (macro or VBA) running an INSERT action SQL or VBA adding data to a recordset object. The only coding that can be done behind tables is with DataMacros, which I've never used.

Why can't people do normal data entry? Users should only interact with forms and reports, not tables and queries.

thanks for the macro advice.
but id like to explain myself,
for example, the two datatables first called "recognize demand", the key column is "item name"
then the second one is "market research", also has the foreign key "item name"
they are connected via the key

user A first fill the form "recognize demand" input what item they want/need,
and the other user B fill the form "market research", conduct research on THAT specific item.

so the user B has to know what exactly the user A want, right? you cannt expect user B memorizes the item name that user A want.
what if user A want a ”gigantic huge horse shirt computer“
user B cannot manually input that thing,,, he has to select from the existing records.

 

[The_Doc_Man]

You have a table that you referred to as a parent table, which has a field A and in that table, A is the primary key. OK, although I am not clear why you also have a field named ID in the table that you think of as your parent table and yet it ISN'T the PK.

You have other tables which also have a field A and you want these other tables to contain data related to whatever is in the parent table. In those tables we would call A a "foreign key" (FK) and if you had a formal relationship between the parent and these other tables, it would be based on common values in field A in each table.

You enter data to the parent table. Now you have a parent record. But Access still needs to know what you want to appear in the other tables. This is because despite the linkage, it doesn't know what ELSE goes there. Further, your setup doesn't account for the fact that you can have a relationship which is one to one or one to many (where many INCLUDES none). It is legal. If you like the parent/child analogy, a parent doesn't have to have a child but a child ALWAYS has a parent.

Your viewpoint of relational integrity (RI) is backwards in your expectation. RI doesn't immediately spawn children of a record. It PREVENTS you from spawning a child without a parent.

IF you are attempting data entry directly from the table view, you are doing something that ONLY a developer should do to "seed" the table with viable data. Normally, what you do is create a FORM to enter data, and if you have potential child records, there is a thing called a SUBFORM that facilitates the process. There, if you attempted to create a child record and had the form/subform set up correctly, the moment you started to enter data to the subform, the A field from the parent would populate the child's FK field. But until you told Access there was something to be stored in the child record, there would be no child record.

 

[The_Doc_Man]

Your reply and mine crossed in writing. What you are describing can be managed by something called a COMBO BOX that allows you to point to a table with a list of things and select the one you want. Then you can store the selection PK in the record you are building. Combo boxes work on forms, so that would be the preferred way to do this.

 

[ccxxxu]

You have a table that you referred to as a parent table, which has a field A and in that table, A is the primary key. OK, although I am not clear why you also have a field named ID in the table that you think of as your parent table and yet it ISN'T the PK.

You have other tables which also have a field A and you want these other tables to contain data related to whatever is in the parent table. In those tables we would call A a "foreign key" (FK) and if you had a formal relationship between the parent and these other tables, it would be based on common values in field A in each table.

You enter data to the parent table. Now you have a parent record. But Access still needs to know what you want to appear in the other tables. This is because despite the linkage, it doesn't know what ELSE goes there. Further, your setup doesn't account for the fact that you can have a relationship which is one to one or one to many (where many INCLUDES none). It is legal. If you like the parent/child analogy, a parent doesn't have to have a child but a child ALWAYS has a parent.

Your viewpoint of relational integrity (RI) is backwards in your expectation. RI doesn't immediately spawn children of a record. It PREVENTS you from spawning a child without a parent.

IF you are attempting data entry directly from the table view, you are doing something that ONLY a developer should do to "seed" the table with viable data. Normally, what you do is create a FORM to enter data, and if you have potential child records, there is a thing called a SUBFORM that facilitates the process. There, if you attempted to create a child record and had the form/subform set up correctly, the moment you started to enter data to the subform, the A field from the parent would populate the child's FK field. But until you told Access there was something to be stored in the child record, there would be no child record.

thank you for your explanation,
im not local speaker, i may not explained myself clearly,
but Id like to explain myself further.

the "tables" or "form" doesnt matter i think,
even it is very not convenient for the admin user to mannualy copy&paste all the names to another table.

just like my example above:
"
for example, the two datatables first called "recognize demand", the key column is "item name"
then the second one is "market research", also has the foreign key "item name"
they are connected via the key

user A first fill the form "recognize demand" input what item they want/need,
and the other user B fill the form "market research", conduct research on THAT specific item.

so the user B has to know what exactly the user A want, right? you cannt expect user B memorizes the item name that user A want.
what if user A want a ”gigantic huge horse shirt computer“
user B cannot manually input that thing,,, he has to select from the existing records.
"

same for admin user, he cannot also memorize the "gigantic huge horse shirt computer" no matter in the "form" view or "table" view.

 

[ccxxxu]

Your reply and mine crossed in writing. What you are describing can be managed by something called a COMBO BOX that allows you to point to a table with a list of things and select the one you want. Then you can store the selection PK in the record you are building. Combo boxes work on forms, so that would be the preferred way to do this.

thanks!!
can the "combo box" captures all the record from another datatable?

 

[The_Doc_Man]

See my post #7 - because your "User B cannot manually input..." is solved by a combo box or list box as a means of selection.

Since you are not an English speaker, I understand there can be a problem of understanding. May I suggest you look into Google Translate as a way to translate sentences that give you trouble?

 

[The_Doc_Man]

thanks!!
can the "combo box" captures all the record from another datatable?

That "data is in another table" option is one of the options you can select when defining a combo box.

ComboBox object (Access)

Office VBA reference topic

learn.microsoft.com

 

[gemma-the-husky]

Cascade update doesn't mean your system automatically loads new data.

Cascade update changes data to keep records in your system in order. If you have US states coded with numeric identifiers, so Texas is number 34, and throughout your database, everywhere you have a state field, number 34 represents Texas. Now you want to change Texas from 34 to TX. A cascade update automatically changes all the 34 values to TX because you have defined in your system the way the state value is managed between tables, and it therefore knows which values need changing.