Form with subform

[Carl Foster]

Just wondering if it's possible to create a form with subform where their control sources are the same.

I would like to have the main form as like a single form with data entry, then the subform showing the same table data but in a datasheet format. If I select a record in the datasheet, then that record shows in the main form.

Is this possible? Would I have to use bound forms or can I use unbound?

 

[Dennisk]

use a listbox or combo box instead of a subform. Infact use the combo box wizard to place a combo box on the main form so you can find a record.

 

[Carl Foster]

Thanks Dennisk.

I could use a list box to do what I want I suppose, but the reason I wanted a datasheet subform is so the user can see a list of all the records and he can view a - z for different columns too. Then if he selects one the info appears on the main form

There just appears to be more verstility in using a subform - unless I'm missing something?

 

[qdogfball]

Thanks Dennisk.

I could use a list box to do what I want I suppose, but the reason I wanted a datasheet subform is so the user can see a list of all the records and he can view a - z for different columns too. Then if he selects one the info appears on the main form

There just appears to be more verstility in using a subform - unless I'm missing something?

One way might be to use a columnar form and a regular form.

On the Columnar form's double-click properties you will have the call to the "sub" form.

DoCmd.OpenForm "Frm Second Form"

Where the "frm Second Form" is your regular form, not a real sub form.

Just a thought!

A sub form should be reserved for use with a matching underlying need.

An employee database with attendance per employee in the subform for example.

But you would ALWAYS want the subform to use a different table that is linked to your main form's table.

(A LOT less programming that way )

 

[Simon_MT]

If you look at what you're trying to acheive is really being about a search process albeit selecting a single record. What happens if your put the list in front of the main form and once a record is selected then go to the main form. If users what to search for a record with a variety of criteria all this can be bundled up in the intermediate search form. I may want to see everything from a specific supplier or perhaps of that suppliers stock what is available?

Simon

 

[Carl Foster]

I don't know if any of you have Access 2007, but if you do, I'm trying to achieve similar functionality to the new Split Form style implemented.

I have managed to get some of it working. I inserted 2 subforms onto one form, both unbound but one in single form format and the other as a datasheet. Retrieving the data using ADO is easy enough but I've had some trouble with the linking.

I used the On Current event behind the datasheet subform to trigger a call to a procedure that retrieves the exact record thats been moved to in the datasheet and pulls it out in a recordset. Then I bind the recordset to the second subform.

It normally works except when the form is loaded. For some reason, when the code tries to bind the recordset to the second subform, I get an error saying that the object has disconnected from its client. I think it has something to do with the form not having started up properly or something like that but I'm not sure.

 

[Carl Foster]

Solved

Thanks for all the suggestions. I've managed to get a working solution.

Basically, I made the single form bound at design time and the datasheet I bound using ADO.

When the form first loads and the datasheet is bound programatically, this also triggers the on current event. In the on current event I placed a simple form filter command for the single form and filtered it to display whichever was the current record in the datasheet.

When a new record is added into the single form view, the database is queried to get up to date info into the datasheet view.

I tried to avoid using bound forms altogether but this turned out to be too difficult