Enter data in one and have another field update automatically - Data Entry Form (1 Viewer)

Abeetan

Member
Local time
Tomorrow, 01:01
Joined
Nov 30, 2021
Messages
39
Hello

I have two tables that are linked with a field called the "Purchase Order Number" (one-to-one Relationship). In one table, I have certain fields that describe the purchase order number and I have another table that has more fields that describe the purchase order number. Obviously, this was done due to the normalization of the tables.

Now here is what I am trying to achieve. I want to be Enter the purchase order number when the order is created (the record) and at the same time, I would like to create a record in the second table with the same order number automatically. How can I do that?

Please advise

TLDR: I want the purchase order number in a field in table 1 and have another field from table 2 update automatically at the same time in the same form.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:01
Joined
Jan 23, 2006
Messages
14,248
Obviously, this was done due to the normalization of the tables.
Could you please tell us more about your application and tables? 1 to 1 relationships are not all that common.
 

oleronesoftwares

Passionate Learner
Local time
Today, 15:01
Joined
Sep 22, 2014
Messages
1,113
I want the purchase order number in a field in table 1 and have another field from table 2 update automatically at the same time in the same form
Do send in images of the forms and tables.

You will likely use a combo box(to look up the fields) and place the code on the after update event to automatically enter a value in another field in same form
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:01
Joined
Feb 19, 2002
Messages
35,883
Obviously, this was done due to the normalization of the tables.
????
Now here is what I am trying to achieve. I want to be Enter the purchase order number when the order is created (the record) and at the same time, I would like to create a record in the second table with the same order number automatically. How can I do that?
my first inclination is - don't do that. But if you insist,
1. The "parent" table uses an autonumber as the PK
2. The "child" table uses a long integer which will be both the PK and the FK.
3. In the AfterInsert event of the form, run an append query to append a row to the child table.

If the BE is Jet/ACE, you can join the two tables and use that as the recordsource for the form. As long as some tbl2 field is populated both records will be added. However, you could have just used a single table.

I have no idea why you split the data. "Normalization" isn't the answer since splitting data that belongs together isn't normalization.
 
Last edited:

Abeetan

Member
Local time
Tomorrow, 01:01
Joined
Nov 30, 2021
Messages
39
Do send in images of the forms and tables.

You will likely use a combo box(to look up the fields) and place the code on the after update event to automatically enter a value in another field in same form
How can I do that?
 

Abeetan

Member
Local time
Tomorrow, 01:01
Joined
Nov 30, 2021
Messages
39
2022-01-03.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:01
Joined
Feb 19, 2002
Messages
35,883
You started a second thread on this same question. I locked the thread so no one would waste their time posting there. You will have your best success here in the forum if you don't create duplicate questions. That just wastes our time and confuses you. If there is something you don't understand about the answers you have here, we will be happy to explain them.

When I looked again today, I noticed that the two controls in your question are bound to fields named tablename.fieldname. That happens when you join the two tables in a query and select two columns of the same name but do not alias them.

Change your query to:

Select tblOutGoingPO.purchaseOrderNumber, tblArchivePO.purchaseOrderNumber as ArchivePONumber, ....

Once you alias (give an alternate unique name to) the purchaseOrderNumber from tblArchivePO, you will be able to reference the columns more easily. You might have to open the form in design view and change the "Name" property of the two controls and also change the bound field of the second control.

And I stand by my original assessment. This is NOT what you should be doing. If you are trying to create an archive copy of the PO so that you can track changes over time, you need to create a duplicate of the entire PO, not just create a row with only the one field being filled in.

Please tell us your business need and we'll help to solve the problem. You've given us a technical problem which we've "solved" for you but incorrectly because knowing how to make one field value equal to another is not the solution to any business problem.
 

Users who are viewing this thread

Top Bottom