Sub-forms and continuous forms

[JGT]

With normalized tables one always runs into non-updatable queries that don’t let you put the data in the tables. To get around this, the table that needs to be updated is used as form source and sub-forms display the needed additional info of the tables that otherwise would lead to a blocking query. However you cannot use a sub-form in a continuous form, at least not anymore with Access 2003. Strange enough, this was no problem in earlier days with Access 2. You could list a table content in the center part of a form and synchronize several sub-queries (placed in the forms heading) with the selected register. Very nice for a time sheet for example. The Time Sheet table contains the hours, the employee id and project id. Sub-forms show employee and project info for the time register filled out.

It is too strange to me that this possibility simply doesn’t exist anymore. There must be an explanation for it though, and also, I suppose there will be an alternative way to do this kind of thing in a similar way. Any tips or suggestions?

Many thanks.

 

[HiTechCoach]

With normalized tables one always runs into non-updatable queries that don’t let you put the data in the tables. To get around this, the table that needs to be updated is used as form source and sub-forms display the needed additional info of the tables that otherwise would lead to a blocking query. However you cannot use a sub-form in a continuous form, at least not anymore with Access 2003. Strange enough, this was no problem in earlier days with Access 2. You could list a table content in the center part of a form and synchronize several sub-queries (placed in the forms heading) with the selected register. Very nice for a time sheet for example. The Time Sheet table contains the hours, the employee id and project id. Sub-forms show employee and project info for the time register filled out.

It is too strange to me that this possibility simply doesn’t exist anymore. There must be an explanation for it though, and also, I suppose there will be an alternative way to do this kind of thing in a similar way. Any tips or suggestions?

Many thanks.

With normalized tables one always runs into non-updatable queries that don’t let you put the data in the tables.

I almost never run into this problem with any version off Access ( 2.0 to 2007). I have seen it with other people's databases when their form's record source is not correctly designed for a form that will be editing data.

A form that is used for editing data should normally only have a single table in the record source. A sub form is used to edit the related data. I have found this to be true for ever version of Access I have used (Access 2.x/97/2000/2002/2003/2007 - I skipped 95 since it was so buggy.)

It is possible to put a sub form in the header or footer of a form that is in continuous mode. I do this now in my apps now in with Access 97 to 2003. I have not tried in 2007 yet, but I would be surprised if you can't.

I also use synchronized sub forms a lot.

 

[JGT]

Thanks for replying HiTechCoach

The problem with single tables is that they often don’t show all the info you need to edit the table data, each time you use a pointer the info is in another table. Depending on the design of the relationships, it is not always possible to construct an updatable query as form source.
That’s where the sub-forms enter to show this info, and I used them normally when I worked with Access 2 some time ago. It sounds like music to me that you are still able to use this combination. The question is why my Access 2003 automatically shows me an error message as soon as I put a sub-form in a continuous form, saying that as sub-forms in continuous forms are not allowed, Access will change the form to single – and in effect switches the mode, so one cannot even try.
Any clue?

 

[John Big Booty]

......... The question is why my Access 2003 automatically shows me an error message as soon as I put a sub-form in a continuous form, saying that as sub-forms in continuous forms are not allowed, Access will change the form to single – and in effect switches the mode, so one cannot even try.[/FONT]
Any clue?

You will always get that message, when ever you try putting a sub form on a continuous form, and you will never get a sub form in the Detail section of a continuous form. However, as HiTechCoach said, if you put the sub form in the header or footer section of the form, you will be able to switch it back to continuous view, despite the message.

 

[HiTechCoach]

Thanks for replying HiTechCoach

The problem with single tables is that they often don’t show all the info you need to edit the table data, each time you use a pointer the info is in another table. Depending on the design of the relationships, it is not always possible to construct an updatable query as form source.
That’s where the sub-forms enter to show this info, and I used them normally when I worked with Access 2 some time ago. It sounds like music to me that you are still able to use this combination. The question is why my Access 2003 automatically shows me an error message as soon as I put a sub-form in a continuous form, saying that as sub-forms in continuous forms are not allowed, Access will change the form to single – and in effect switches the mode, so one cannot even try.
Any clue?

As John pointed out, after Access switch the form from continuous view, you can switch it back with the sub form in the form's header or footer.

The problem with single tables is that they often don’t show all the info you need to edit the table data, each time you use a pointer the info is in another table.

Hummm... I have never run into that. I may not have a problem with this by using form controls (like list b box/combo box) to handle lookup tables.

 

[Khalid_Afridi]

I did not go through all your poroblem but what I gues is you want to create sub-form based on continous form or datasheet.
The easiest way to make sub-form based on continouse form or datasheet is, to create an unbound textbox control on the form header and assign the the ID/Primery Key of the record to it on run time, then link master and chiled field on the sub-form to that unbound textbox control.

Hope this will help.

Khalid Afridi

 

[gemma-the-husky]

i wouldnt say "always run into non-updateable queries"

i occasionally get this issue, sometimes there is a way round it, with modified design - sometimes i just couldnt see a way to do it, and have had to iterate a recordset, and run sql statements to achieve the same result.

i dont quite follow what you are saying with regard to using sub-subforms to show some data (cant you do this with comboboxes) - it smacks of some normalisation issue