Solved Linking 2 continous forms

[Pat Hartman]

Arne,
Where did you put the code to filter the second subform?
Pat

 

[arnelgp]

here are some changes (from the last db i post).

 

[AnilBagga]

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]

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]

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]

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]

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]

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?

 

[bastanu]

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,

 

[AnilBagga]

Thank you Vlad. You made my day! I cannot thank you enough

 

[bastanu]

You are very welcome, that is why we are here!
Good luck with you project!
Vlad