Setting OnDblClick on a subform problem

[johnbirt]

I have a form frmDisplayVars with a subform subfrmDisplayVars. The sub form initially has its RecordSource set to "" and is set dynamically from a maketable when needed.

In the OnLoad for the main form, the subform RecordSource is set to a table "Table.Test". This is so that the load routine can execute a maketable query to create the form "Test" in advance. This produces a table from a crosstab query. All this works OK so far.

However, I wish to be able to double click on the datasheet in the subform to then open another form with data related to the cell/contol that was clicked on. Essentially creating a room occupancy table for a range of dates and rooms. Rows are rooms and columns are dates.

I have a problem with setting the OnBblClick for the subform controls.
For example the 2 test code lines for the control "RoomName" illustrate.

Forms!frmDisplayVars!subfrmDisplayVars.Form!RoomName.Name
Forms!frmDisplayVars!subfrmDisplayVars.Form!RoomName.OnDblClick = "=TestMe()"

The first line works fine and returns as it should the name of the control "RoomName".

It is the second line which I need to set the event code for OnDblClick which fails with error no. 2455 which suggests wrong syntax for referencing a control on a subform. Since the control reference appears OK as suggested by the first line I am stumped as to what is wrong.

I can't set the event procedures manually as they are not known in advance until the table "Test" is made so need to be able to set the control events dynamically.

Would be most grateful for a solution otherwise the project is at an impasse!

Thanks in advance.

John B

 

[JHB]

If the code is in the main form then use Me. instead

Me.subfrmDisplayVars.Form!RoomName.OnDblClick = "=TestMe()"

I've made a small sample database for you, open "Form3" click on the button in it and then double click on the "column" status_time1.
Else post you database with some sample data in it, (zip it), + information which form to use.

 

[johnbirt]

If the code is in the main form then use Me. instead

Me.subfrmDisplayVars.Form!RoomName.OnDblClick = "=TestMe()"

I've made a small sample database for you, open "Form3" click on the button in it and then double click on the "column" status_time1.
Else post you database with some sample data in it, (zip it), + information which form to use.

Thanks for taking the trouble to provide an example db. Unfortunately it doesn't reflect my situation which needs to handle a dynamic table which will not always have the same number of columns/fields. Also Me. or Me! both should work here but neither do.

If I design the subform with a fixed table as in your example if the table is altered by changing a date value or other parameter elsewhere on the main form then an afterupdate to alter the subform recordsource is needed but then the original table display remains which is not compatible with the new data display and you will get a bunch of ??'s in the fields that aren't present.

That is why I setup the subform with no recordsource originally and then change it when necessary after a delete and new make table from the crosstab query. This provides a perfect fit of the subform datasheet to the 'new' table.

I have altered your database example to start off with no recordsource and just put a load sub for the main form. I haven't bothered with changing the table dynamically in any way and just left it as you provided.

Your command button which you designed to illustrate the syntax for OnDblClick now doesn't work. Obviously there is a difference brought about by the way I have designed the subform.

You will note via the msgbox output from the load sub for the main form that the control for the column seems to exist but as soon as an attempt to setup the OnDblClick it fails.

Since there is an unknown number of fields in my project it is necessary to set the events via looping through the controls and they can't be manually set. That's why I need to fix this.

Thanks for your interest and example. Any further thoughts are most welcome.

I may have to bite the bullet and try and make do with a fixed number of columns. I do think the above can be done as I have seen an example in action but not the code.

John B

 

[ChrisO]

John.

Could you please post a sample database in MDB format?

Chris.

 

[JHB]

Thanks for your interest and example. Any further thoughts are most welcome.

I may have to bite the bullet and try and make do with a fixed number of columns. I do think the above can be done as I have seen an example in action but not the code.

John B

The problem here is that a table is set as sub form's SourceObject, and table fields doesn't have any events like "On Dbl click". You can see it if you set the form in "Layout view" and show the property sheet, (picture below).

The way out of this is to create the subform on the fly when main form opens and the table is created, because a text control and a label control needs to be created according to the numbers of fields in the table.
I've have change the database to fulfil the above, (the "On Dbl click" event is set just after when the text control is created).

 

[johnbirt]

John.

Could you please post a sample database in MDB format?

Chris.

There is a link to a demo in previous post.
In fact JHB has now posted an update to that which I am now looking at.

John B

 

[johnbirt]

The problem here is that a table is set as sub form's SourceObject, and table fields doesn't have any events like "On Dbl click". You can see it if you set the form in "Layout view" and show the property sheet, (picture below).

The way out of this is to create the subform on the fly when main form opens and the table is created, because a text control and a label control needs to be created according to the numbers of fields in the table.
I've have change the database to fulfil the above, (the "On Dbl click" event is set just after when the text control is created).

Thanks very much JHB for your efforts. I also investigated further and found that the subform controls named after the table fields were 'non standard' and have limited properties i.e. as you say no events attached. In fact their control type is 115 which I cannot find reference to as a valid type, so this must be an internal control type just used for display of the datasheet.

In fact the subform also loses some properties as a result of the recordsource change. Settings related to the form display 'furniture' go to defaults so will have to be adjusted to suit my purposes. In fact when the form is loaded as you infer, design mode shows a blank subform!

I intended later to add text box controls on the fly with control sources related to the table fields and adjust the subform properties etc. but looks like you beat me too it! I have already altered the field captions and subform properties so just remains to fudge the controls.

Many thanks again and I'll report back when I've see how our methods compare.

John B

 

[johnbirt]

Thanks very much JHB for your efforts.
.......

Many thanks again and I'll report back when I've see how our methods compare.

John B

Hi JHB. Had a look at your solution which was spot on. I had pretty much the same idea about creating viable txtbox and label fields but had planned to do so directly on the subform.

Your method of creating a new form is better in that there isn't a blank datasheet to look at while the processing takes place. The use of a secondary table means the processing can be done in the background which is neater.

I have taken the liberty of re-arranging your code a little to take care of future use for variable column numbers and also added user definable column headers via label captions and have posted the result back. My Xtab of course produces headers which need to be processed a fair bit.

I could not find anything like this after considerable researching but did see a number of unfulfilled requests for somethig similar to handle variable column subforms nicely. This may well be of use to others. They would need to provide the main table as required from their data but the bare bones of a workable solution is there.

In my project the Load sub or equivalent will essentially be re-used when user intervention chooses a different number of columns and a different starting point for the table. My main table will hold a large number of hotel bookings and the user has the ability to choose a start date for the Xtab and a period of time to display e.g. a week, month etc..

Very many thanks for your efforts as they saved me a lot of time and gave me the idea of the additional table and the preferable use of a new subform.

John B

 

[JHB]

You're welcome, much luck with your project.

Ps. If you move the DoCmd.Echo to the form load, then the flickers stops. A form can be opened hidden in design view, (until all controls are in place).