MONTHSSS (2 Viewers)

[I am alive]

I have two related tables let's call them
1. tblA.
2. tblB.

Is there any way such that if I write in a field in tblA to be populated automatically in tblB?

E.g if I write BANANAS in tblA then in tblB I will see BANANAS without me writing anything in tblB.

Thanks in advance.

 

[ebs17]

Is the shift key stuck?

That would be possible with a code solution or DataMacro or trigger - but what's the point of that?

Write your information where it is really needed. Distributing redundant data around the world is not a good measure.

 

[I am alive]

Is the shift key stuck?

That would be possible with a code solution or DataMacro or trigger - but what's the point of that?

Write your information where it is really needed. Distributing redundant data around the world is not a good measure.

Thanks for your reply. My database is designed in a way that there are some fields that are the same in tblA and tblB for the purpose of normalization tables. If you could help me on how if I write in a field in table A THEN the same information is populated in tblB I will be very grateful. Thanks in advance.

 

[jdraw]

Please tell us more about your tables and Normalization. Give us the 30,000 foot overview of what you are trying to achieve. Show us your table designs and relationships and tell us if there is something special about your PK and FK.

 

[I am alive]

Please tell us more about your tables and Normalization. Give us the 30,000 foot overview of what you are trying to achieve. Show us your table designs and relationships and tell us if there is something special about your PK and FK.

In tblA we have BANANASID and in tblB we have BANANASID. We have a field in tblA bananas and in tblB bananas. I don't want to keep repeating typing BANANAS in tblB after writing in tblA

 

[Minty]

As @jdraw has asked, please show us your tables and relationships, your explanation has made it even less obvious why you would need to repeat this data.

 

[I am alive]

As @jdraw has asked, please show us your tables and relationships, your explanation has made it even less obvious why you would need to repeat this data.

There is a need. Kindly assist me.

 

[ebs17]

Technical:

I write in a field in table

In the table by hand?
Don't hit anyone with the huge amount of your information.

You already have an answer in variants. You can choose something yourself.

 

[jdraw]

You could start with the standard
-what are you trying to achieve
-who would use this, when and why, and in what process(es)

Think of yourself in a fast-food line- you and the person near you start a conversation- eventually the conversation gets to - "So what are you working on??"
- and then you describe to this person,(who you do not know and who doesn't know you, your environment nor details of database), the issue for which you have created this thread.

What does TblA represent? What does TblB represent?...

 

[The_Doc_Man]

There is a need. Kindly assist me.

Your need, at least to us, appears to originate in a bad design, which is WHY you have to do what you are doing. And it is why we are asking about your table layout.

Duplicating data outright is something that is both wasteful of space and (as you already pointed out) wasteful of time to repetitively type something you already know. Proper normalization prevents this kind of waste.

 

[ebs17]

BANANASID
Duplicating a key (foreign key) will now not be seen as a normalization violation.

However, direct entries in a table should make you think. If you write to a bound form or via query/recordset, you can attach additional write actions. You just have to know beforehand what exactly you are doing and why you are doing something.

 

[I am alive]

BANANASID
Duplicating a key (foreign key) will now not be seen as a normalization violation.

However, direct entries in a table should make you think. If you write to a bound form or via query/recordset, you can attach additional write actions. You just have to know beforehand what exactly you are doing and why you are doing something.

My database works fine. I have very many tables. Now I have tblYEARMONTH to capture Years and months. I also have tblBANANASMANGOES and tblBANANASPINEAPPLES. With this BANANASID are PK and FK. I need a way when I write BANANAS in tblBananasMangoes it should be populated in tblBananasPineapples. I have tried update query but I am defeated.

 

[I am alive]

Is there a VBA code to run a query in tblB after updating the same query in tblA? I will really appreciate. Thanks in advance.

NB:- Query in tblA is same in tblB

 

[jdraw]

"My database works fine. I have very many tables."
I'm sure it does. But we have yet to see your tables and relationships.
How about addressing post #9? And telling us about your Normalization?

 

[I am alive]

"My database works fine. I have very many tables."
I'm sure it does. But we have yet to see your tables and relationships.
How about addressing post #9? And telling us about your Normalization?

Query after update in tblA or Form A to run the same query in tblB or Form B.

 

[jdraw]

?? Have you ever had to give instructions to someone else to perform/complete a task??

 

[Minty]

I think this is a classic case where you are obscuring your real table and field names, causing a massive disconnect between what you want and what we all think a good design should entail.

Tell us the real purpose of this and we might agree with your need to duplicate the data. Without a clear picture of the REAL purpose, no one here can help you do it properly or help you get around a complex and unusual problem.

Using your example table names you wouldn't need to store bananas as you already know it's a banana because it's in the Banana's table!

 

[The_Doc_Man]

OK, let's talk about what can and cannot be done.

You cannot write a single query to update two different tables simultaneously unless they are JOINed AND there is no ambiguity in what you are writing AND WHERE. The odds on this working are not good.

There ARE cases where you can run a data macro based on updating a table, but this is incredibly dangerous if there is more than one way to update the table as you might get "false updates" in that case.

If you manually run a query from the navigation pane, you have no way to automatically run something else without another click.

If you run the query based on a control click event on a form, then whatever you did on the form to run the query, have it run your second query before you dismiss the event.

If your query is actually just an update of a bound form, you can take extra actions in the form's AfterUpdate event.

If those are inventory tables of some sort, then you are being defeated by your design, not by Access, because you are making multiple tables to hold things that should be in a single table that includes a item type. One entry and done.

 

[I am alive]

OK, let's talk about what can and cannot be done.

You cannot write a single query to update two different tables simultaneously unless they are JOINed AND there is no ambiguity in what you are writing AND WHERE. The odds on this working are not good.

There ARE cases where you can run a data macro based on updating a table, but this is incredibly dangerous if there is more than one way to update the table as you might get "false updates" in that case.

If you manually run a query from the navigation pane, you have no way to automatically run something else without another click.

If you run the query based on a control click event on a form, then whatever you did on the form to run the query, have it run your second query before you dismiss the event.

If your query is actually just an update of a bound form, you can take extra actions in the form's AfterUpdate event.

If those are inventory tables of some sort, then you are being defeated by your design, not by Access, because you are making multiple tables to hold things that should be in a single table that includes a item type. One entry and done.

Please have a look at the attached database. The two queries qTermStream1 and qTermStream2 are the same. In my database I have several subforms which are linked together. In this case I have TermStreamsID. Instead of the user typing Selecting
TermStreamsID- 143
GRADE 1-SINGLE-(Term 2-2023) in a subform and then select
TermStreamsID-143
2023-Term 2 - (GRADE 1- SINGLE) in another subform, It is better if there was a way of populating the same TermStreamsID in another subform automatically. Thanks in advance.

 

[mike60smart]

Please have a look at the attached database. The two queries qTermStream1 and qTermStream2 are the same. In my database I have several subforms which are linked together. In this case I have TermStreamsID. Instead of the user typing Selecting
TermStreamsID- 143
GRADE 1-SINGLE-(Term 2-2023) in a subform and then select
TermStreamsID-143
2023-Term 2 - (GRADE 1- SINGLE) in another subform, It is better if there was a way of populating the same TermStreamsID in another subform automatically. Thanks in advance.

It would help if you uploaded a copy of the Database which includes your Forms