Reverse Form/SubForm Parent/Child relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to build a form which is control by a subform in datasheet view. Basically, I want the subform to display all the data from a query/table and, when a record is selected, I'd like all the related data from other tables to appear on the rest of the form (and two additional subforms)

The principle is that I have a list of clients who each have separate Invoice and Service details, I want to be able to quickly scan the client list (in Datasheet view so that it can be sorted etc...) and then review the details of their Invoice and Services (but also details regarding their input and feedback - through subforms)

Can anyone help? I've checked books and numerous websites but haven't turned anything up..

Thanks.
 
The normal set up is to have the Clients as the RecordSource for the
Mainform with a ComboBox or ListBox for the user to select a particular
Client. When a Client is selected, the Client becomes the Current Record on
the Mainform and the rest will follow if you use linked Subforms.

Note that linked Subforms are designed for Tables in One-to-Many
relationship where the Mainform is bound to the "One" Table and the Subform
to the "Many" Table. Your arrangement seems to be the reverse of the
intended purpose of the Subforms.

My guess is that you can use unlinked Subform if you want to do it this way
but you will have to write VBA to do the Record navigation on the Mainform.

--
HTH
Van T. Dinh
MVP (Access)



Pete said:
I'm trying to build a form which is control by a subform in datasheet
Click to expand...
view. Basically, I want the subform to display all the data from a
query/table and, when a record is selected, I'd like all the related data
from other tables to appear on the rest of the form (and two additional
subforms).
The principle is that I have a list of clients who each have separate
Click to expand...
Invoice and Service details, I want to be able to quickly scan the client
list (in Datasheet view so that it can be sorted etc...) and then review the
details of their Invoice and Services (but also details regarding their
input and feedback - through subforms).
 
Cheers... I was slowly coming to this realisation myself

While I know a lot of Visual Basic, I'm not sure of how to put it into practice

Any hints?
 
Your friend will be the Requery Method if you want to do it this way. Check
Access VB Help on the above method.

However, I don't recommend doing this way. Form / Subform combination is
designed specifically for the One-to-Many relationship and I generally used
Subforms only for Tables in 1-to-M relationship.
 
Hi Pete
Did you solved the problem? If yes please let me know, becouse I need the same help. Please hel

Thank
Rez

----- Pete wrote: ----

I'm trying to build a form which is control by a subform in datasheet view. Basically, I want the subform to display all the data from a query/table and, when a record is selected, I'd like all the related data from other tables to appear on the rest of the form (and two additional subforms)

The principle is that I have a list of clients who each have separate Invoice and Service details, I want to be able to quickly scan the client list (in Datasheet view so that it can be sorted etc...) and then review the details of their Invoice and Services (but also details regarding their input and feedback - through subforms)

Can anyone help? I've checked books and numerous websites but haven't turned anything up..

Thanks.
 
Thanks for your help, I haven't cracked it yet but I'm beginning to feel like I'm getting somewhere

I tried this and used the following code

DoCmd.Requery (Form_frmMIS.ID.Value = Form_subfrmContactDetails.ID.Value

It seems to want to work but tells me "there is not a field named "0" in the current record" which is odd because I'm trying to call directly on the Values anyway... I've tried a couple of other ways and it informs me that "Recordset Is Not Updateable" too

Can you shed any light on what I'm doing wrong?
 
I couldn't figure it out in the end... I resorted to using Lookups and handing a SQL string through to another form... Or, at least, I'm in the process of doing that...

I bought "Beginning Access 2000 VBA" by Smith and Sussman on the cheap from Amazon. It's not great but it gets you through...
 
Just so that everyone knows, I gave up in the end and I'm using Lookups to find the customers in one form and handing an SQL string through into another form where Data matches are displayed...
 
Back
Top