Solved Linking 2 continous forms (1 Viewer)

[AnilBagga]

I have a query where I need to add a few columns and create a report or query with the additional columns. The output after linkage needs to be in continuous form (with query columns followed by the additional columns of the table) as the rows of data will be large and the report is needed for a weekly discussion.

I created a table for the additional columns and linked it to the query in a sub form basis, with query being the main form. This works but can only be on a single form basis. The link has to be on 2 fields - the ID and one more field. Multiple field links are not a problem in the above process

If I combine the query and form into a new continuous form, the additional fields of the new table become non editable!

How can I link the query and the table?

 

[Rene vK]

a query is just a view of a table... try a new query and show that in the new continous form. (by the way, a hard question to read)

 

[AnilBagga]

try a new query and show that in the new continous form. - I had created a new continuous form using the existing query as record input but that wdid not help

(by the way, a hard question to read) - should I try to rephrase the problem?

 

[Rene vK]

You have a table "A", and create a query from it, which shows on a Form. In the subForm you link to table "B". After adding info to table "B" you want to open a new form which shows the combination of "A" & "B"..... I guess you should just create a new query with the linked records from "A" & "B" to show your info!

 

[AnilBagga]

Yes this is possible. The problem is that to add data in Table B, I need data of Table A and B to be viewable in a continuous form , side by side with Table A data as non editable and Table B data to editable. This is the reason I thought that if Table A and B are continuous and we find a way to link them, we will get a continuous entity as desired

 

[Rene vK]

Sorry, reading your question again, I think we are on different tracks.... As I am trying your steps I can not think of a solution! As far as my knowledge feeds me it will only be possible if you create a new form with two subForms. First subForm shows table "A" as continuous form and the second subform shows tabel "B" linked via your related fields.

 

[AnilBagga]

In other words, Data for Table B will be seen one record at a time, perhaps based on which record in Table A is selected

 

[Rene vK]

yes, that will be the case. You will only see more from "B" when you have multiple records linked to a single record in table "A"

 

[bastanu]

If it is the pending orders query that we discussed in another thread earlier you will not be able to add the new table to the form and have the fields editable. One solution would be to use a local temporary table instead of the query itself. Basically change the original query from a Select query to a make table query, run it to create your local table, make sure the table has the primary key (composite - your two fields) then change the query to an append query (to add the records to this new local table) and save it. In the code used to open your form add two lines of code before opening the (continuous) form:

CurrentDb.Execute "DELETE tempPending.* FROM tempPEnding;",dbFailOnError  'to empty the new temp table

CurrentDb.Execute "qryAppendPending",dbFailOnError 'populate the temp table by running append query based on old select query

Docmd.OpenForm "frmYourContinuousForm"

The form should be based on a select query between the new temp table and your other one with the extra fields and it should now be editable.

Cheers,

 

[Pat Hartman]

Please post the tables and forms you are trying to create. Not all of us have read your other threads. It is quite possible to have two continuous subforms on an unbound main form and when you click on frmA, your code can filter formB to show only the records related to the selected record in frmA. Is that what you want?

 

[AnilBagga]

Please post the tables and forms you are trying to create. Not all of us have read your other threads. It is quite possible to have two continuous subforms on an unbound main form and when you click on frmA, your code can filter formB to show only the records related to the selected record in frmA. Is that what you want?

I created a continuous form using the query as the source, say Form A. Then I created a continuous form for the new fields needed - say Form B linked to Table B. I also created a conjugated field in the tables for the 2 key fields I want to link - the query has the conjugated field and also Table and FormB. The linkage is possible and if I click on any record in Form A, the linked record is displayed in form B. However the data Form B is not editable!

If Form A is a single form, then there is no issue and Form B is editable

DB enclosed
Unbound form to link the 2 forms is : Ex1
Query name : PendingOrderqry
FormA above is PendingOrderfrm
FormB is PendingOrderSubfrm
Table B above is :CustPendindingOrdertbl
Conjugated field is called "ComboCode" and "ComboCodeP" in Tables CustSODtlstbl and CustPendindingOrdertbl

 

[arnelgp]

here check and test.

 

[AnilBagga]

here check and test.

I opened the Ex1 form which has both the query and the editable form. The fields are not editable. Is this the correct form?

 

[AnilBagga]

If it is the pending orders query that we discussed in another thread earlier you will not be able to add the new table to the form and have the fields editable. One solution would be to use a local temporary table instead of the query itself. Basically change the original query from a Select query to a make table query, run it to create your local table, make sure the table has the primary key (composite - your two fields) then change the query to an append query (to add the records to this new local table) and save it. In the code used to open your form add two lines of code before opening the (continuous) form:

CurrentDb.Execute "DELETE tempPending.* FROM tempPEnding;",dbFailOnError  'to empty the new temp table

CurrentDb.Execute "qryAppendPending",dbFailOnError 'populate the temp table by running append query based on old select query

Docmd.OpenForm "frmYourContinuousForm"

The form should be based on a select query between the new temp table and your other one with the extra fields and it should now be editable.

Cheers,

Thank you Vlad for remembering the earlier post!

I did the following steps
1.The Select qry name is PendingOrderQry
2. I changed the qry to Make table qry and table created from it is called Temptbl. It has a primary key which is combined key of 2 fields, called ComboCode
3. I changed the Qry design to AppendQry to append data in the Temptbl. On execution the table has the data of the qry
4. I created a continuous form, from the Temptbl called Tempfrm

The Db is called MIS-fe29Jun2020Vlad.

Would the code be as below ? Where do I add this code in the form? I tried it 'Onload' and "Current events" and get errors

CurrentDb.Execute "DELETE Temptbl.* FROM Temptbl;",dbFailOnError
CurrentDb.Execute "PendingOrderqry",dbFailOnError
Docmd.OpenForm "Tempfrm"

Where do I add this code in the form? I tried it 'Onload' and Current events. Each cell displays #Deleted but data in the table is intact!

Is there anyway that the 2 tables are merged as one in a table?

 

[arnelgp]

sorry worked on wrong form, see this.

 

[AnilBagga]

sorry worked on wrong form, see this.

Yes it Works. Wow! Many Thanks

Can you tell what was done for learning from this exercise. Also I need to modify the query and the appended table going forward

 

[arnelgp]

combocode field on both tables are Calculated field, therefore you cannot set an Index to it.
i make the combocode field in CustSODtlsTbl as shortText (anyway i had to make sure, and i was
correct that it hold Unique record).
after changing to text i added index to the field.

don't worry about the calculation, i made a AfterInsert and AfterUpdate Data macro on the table that will
automatically update the field.

 

[AnilBagga]

Mr Arnel

Thanks. It works beautifully. A couple of questions:
1. Can I modify the Form Ex1 by adding more fields form the query?
2. Can I rename this form? If yes where do I change the Macro?
3. Can I modify the table where I am adding data to add more fields?
4. The combo code in CustSODtls table is concatenated in the Macro possibly. Correct?
5. The combo code is a calculated field in the New table. That is not an issue obviously!

 

[AnilBagga]

combocode field on both tables are Calculated field, therefore you cannot set an Index to it.
i make the combocode field in CustSODtlsTbl as shortText (anyway i had to make sure, and i was
correct that it hold Unique record).
after changing to text i added index to the field.

don't worry about the calculation, i made a AfterInsert and AfterUpdate Data macro on the table that will
automatically update the field.

I tried editing and saving the data. The data in the Subform when edited and saved, is NOT saved in the Table. The subform is actually querying data from 2 tables. One table which has the Combocode and shortclose field is updated but the other fields of the other table are not updated

Meanwhile I tried adding fields on both the forms - that is not an issue!

 

[bastanu]

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,