Solved Linking 2 continous forms (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:38
Joined
Feb 19, 2002
Messages
42,981
Arne,
Where did you put the code to filter the second subform?
Pat
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,175
here are some changes (from the last db i post).
 

Attachments

  • MIS_fe_23Jun2020 (2).zip
    448.3 KB · Views: 91

AnilBagga

Member
Local time
Today, 15:08
Joined
Apr 9, 2020
Messages
223
here are some changes (from the last db i post).
Yes. That works. Thank you.

How does one replicate this? Where is the code etc. I need to replicate this in the prod DB and a better understanding will help in making the changes
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,175
i added recordsource of MainForm same as subform to the Left.
added those 3 textbox from the recordsource.

on the right subform, i added code (see the BeforeUpdate/BeforeInsert event of the subform i cannot remember the fieldnames).
 

AnilBagga

Member
Local time
Today, 15:08
Joined
Apr 9, 2020
Messages
223
I thought the main point was for you to be able to edit the three fields from the new table (as the pending details should be edited in their respective tables). Please have a look at the new datasheet form frmTempPendingOrders which is based on the design I suggested earlier, every time you open it it refreshes the tempPending table.

Cheers,
Vlad,

Yes that works.. I see the code used in the frmTempPendingOrders

DoCmd.Echo False
CurrentDb.Execute "DELETE tempPending.* FROM tempPending;", dbFailOnError
CurrentDb.Execute "qryAddPendingOrderqry", dbFailOnError
Me.Requery
DoCmd.Echo True

I need to productionize this after a few changes. Please correct my understanding which is as below:

1. The "AddPendingOrderqry", replaces the select query "PendingOrderqry" and this select query created earlier can be deleted
2. The append query only updates the common fields of the Query and the Temptable - The Query has more fields than the Table
3. The Temptable is created by a Query. Correct. Which query?
4. With the QryTempPendingOrder we are linking the tempTable and the additional fields - dates and the Yes/No field of CustpendingTable
5. We display the results of this query "QryTempPendingOrder" in the "frmTemppendingOrders" where we edit the dates and the Yes/No field
 

AnilBagga

Member
Local time
Today, 15:08
Joined
Apr 9, 2020
Messages
223
i added recordsource of MainForm same as subform to the Left.
added those 3 textbox from the recordsource.

on the right subform, i added code (see the BeforeUpdate/BeforeInsert event of the subform i cannot remember the fieldnames).
Let me check
 

bastanu

AWF VIP
Local time
Today, 02:38
Joined
Apr 13, 2010
Messages
1,401
Here are the answers:
1.Yes (the append query started as a make-table to initially create tempPending table which I then modified to add the composite primary key)
2. The append query doesn't "update"; it is a full refresh of the table as it is emptied by the first SQL statement. TempPending table has all the fields in the original PendingQry Select query.
3. See 1.
4.Yes, and it is now editable.
5. Yes.

Cheers,
Vlad
 

AnilBagga

Member
Local time
Today, 15:08
Joined
Apr 9, 2020
Messages
223
Here are the answers:
1.Yes (the append query started as a make-table to initially create tempPending table which I then modified to add the composite primary key)
2. The append query doesn't "update"; it is a full refresh of the table as it is emptied by the first SQL statement. TempPending table has all the fields in the original PendingQry Select query.
3. See 1.
4.Yes, and it is now editable.
5. Yes.

Cheers,
Vlad
Vlad,
The process is clear now. Based on this understanding I wanted to make some changes as below:

1. The value ShortClose needs to be saved for future and hence I added this field to the "CustPendingOrdertbl" . I modified the Qry (qryTempPendingOrder) accordingly but all the records which are not in the table naturally do not have any value in the shortclose field and show as black squares in the Qry. Can we add some code that the default value is No for such records and of course if the value exists as Yes, then it should be unchanged- DB enclosed

2. Instead of creating a new table "CustPendingOrdertbl" to hold the editable values, I plan to modify the table CustSOdtls which has both the key fields - SOHdrID and ErpFGCode on which the relationship is based in the final Qry "QryTempPendingOrder" If this is possible then Point 1 will not be needed as all records will always exist in this table - it is the source data. I changed the table design to include these fields.

3. I copied the Qry to replace the "CustPendingOrdertbl" with CustSODtls and the relationship is same as the earlier Qry - See qry qryTempPendingOrder2 and form frmTempPendingOrder2 . The data is not editable in the form for all the fields of the 2 tables - Why?

4. I copied the qryTempPendingOrder3 and form frmTempPendingOrder3 and deleted the table "CustPendingOrdertbl" . The data is updating now in the form but is not saved in the table CustSODtls! I dont know why?

5. We are not using the concatenated fields ComboCode. Can we delete this from tables and queries now?
 

Attachments

  • MIS_fe_01July-Vlad-mod.zip
    485.9 KB · Views: 75

bastanu

AWF VIP
Local time
Today, 02:38
Joined
Apr 13, 2010
Messages
1,401
Have a look, I added an index to the ItemCode field in the CustSODtlstbl table.

1.Not needed if you go with 2.
2. OK
3. The query shouldn't have both tables for the editable fields, you already addressed this in 4.
4. I tried (after adding the index) and it seems to save OK to the table
5. Yes, you should remove it.

Cheers,
 

Attachments

  • MIS_fe_01July-Vlad-mod_ver2.zip
    494.5 KB · Views: 90

bastanu

AWF VIP
Local time
Today, 02:38
Joined
Apr 13, 2010
Messages
1,401
You are very welcome, that is why we are here!
Good luck with you project!
Vlad
 

Users who are viewing this thread

Top Bottom