How do you synchronize form / subform

[tjones]

I have a form (frmCourseTaken) form style and it contains a subform (subfrmCourseSummary) datasheet style which is based off a query.

The forms are working. The problem is that when you choose a record in the subform datasheet it does not synchronize the form to display and allow changes to the record.

All data is stored and pulled from the CourseTaken table. I have included the database pared down to minimal. View attachment studentinfoSUBFORMsm.accdb

I started out using a split form that sort of worked, the problem being with the cascading combo boxes the form was unstable and would drop information from the form (though still in the table) and eventually it would not longer display in either the form or datasheet though still stored in the table. So thought I would try something new to solve that problem.

 

[gemma-the-husky]

you are misunderstanding, maybe

a form is the master. for each item in the form, you will see a different number of related items in the subform. changing things in the subform should have no effect on the main form (parent)

if the subform is non-editable, it probably just means you are using a non-updateable query as a basis for the subform.

 

[tjones]

Maybe I did not state this correctly. Rright now the subform shows all records but only the first record in the subform displays in the main form.

What I would like to do is when you click on a record in the subform datasheet, then that record appears in the main form, the changes then can be made on the main form and saved to the table.

 

[jdraw]

I think Dave has said how a main form /sub form normally works.
Typically you have 2 tables related in a 1 to many relationship.
The 1 Table (or a query on same) is the recordsource of the mainForm; the many table is the recordsource of the sub form.
You identify which fields in the 2 tables are "Linked" and the subform is synced with the mainform.

Perhaps we are misunderstanding your specific requirement.

see https://www.youtube.com/watch?v=YZKN_-P6wck for form/subform

 

[tjones]

OK if you open the database View attachment studentinfoSUBFORMsm.accdb and select the Course Work button it takes you to the form / subform.

The main form (frmCourseTaken) is where the student course records are entered, stored in tblCourseTaken.

The subform (frmsubCourseSummary) is built off a query (qryCourseSumary) that pulls the information from the tblCourseTaken.

What I need to be able to do is select a record in the subform and have it load to the main form so I can edit the information.

 

[spikepl]

I get what you are trying to do - to use a subform as a selecting mechanism to pick a record, just like a split form. Your split form attempt most likely failed because of the way cascading combos behave in a datasheet/continuous form, so nothing to do with split-form "instability". You only have one rowsource for each, not depending on record, and that confuses most people and leads to data "disappereing" from the display.

As to your current db, it does not compile in my A2007, so either there are direct errors, or you are using some A2010 witchbrew, that my A2007 cannot digest.

 

[tjones]

That is exactly what I am trying to do. (Using Access 2010).

I want to use the cascading combo's to reduce potential errors but need to display all associated records (which I did get it to do) and yet be able to select a record in the subform and have it load to the main form (which I can not figure out how to do)

 

[tjones]

I know that I should use a List Box, but I can not get that to work either, it displays all course records not just the ones for a particular student. I do not care which way it is just as long as I can get it to work that selection in the MANY (either sub or list) opens the record in the ONE (main) for editing.

 

[jdraw]

I'm glad spikepl understands the issue, I don't. But then again I don't know split forms either.

What I do see on the form /sub form is that the subform shows it is UNBOUND.
Hopefully that will help spikepl in his review.

 

[spikepl]

I don't have 2010 here so... In any case, you can obtain the same functionality by not mixing forms together. If you have a form that has a datasheet (for picking one record), a doubleclick on one record could then open a form with the selected record for edit in single form view, where you could have your combos ad libitum.

 

[tjones]

Unfortunatly the boss want both views together (doesn't care how tho) hence trying the list box that i could not get to work as it displays every record (see above).

Would making the datasheet view the main form and the entry view the sub form work with what I need to accomplish?

 

[tjones]

I DID IT!!! Thanks to everyone for their help.

Solution:

Create a query based off the fields in the table of the form you want the subform to display. Be sure to include on both the main form and in the query:

1. The autonumber in the table (RecordID)
2. The information to limit what displays in the subform (StudentID)
​

Create the subform off the query and set it to datasheet/allow datasheet view=Yes (This will look like a listbox or the datasheet portion of a split form)

Once you have the subfom displaying the information the way you want.

In the auto RecordID field set format to Display as Hyperlink=Screen only

on the Form format on click enter the attached Macro (change the field name to match your entry)

Save the form and close the database then reopen - the form should work like a split form. You select the RecordID in the subform to load the record to the main form.