Displaying data with a one-to-many relationship on a form

[Paul7905]

This may be a particularly stupid question however I need to ask it.

I have two tables in which there is a one to many relationship. I established the relationship ok.

When i view the table on the one side of the relationship in datasheet mode by clicking on the table name, I see, on the left side of the table (first column in the display) a minus sign (-). When i click on this, access automatically displays the data from the table with the "many" relationship.

Essentially i think this is pretty cool however the feature only seems useful to me from the standpoint of being able to use it to verify data while i am designing my application.

What I am interested in finding out is whether there is a way to easily utilize this "drill down" feature on a form for the user to use.

For example, i would like to be able to display the table with the "one" relationship and allow the user to click on the minus sign so as to display the "many" side table rows.

Is there any way to take advantage of the one to many relationship for client or user benefit on a form?

thanks
Paul

 

[jebry]

Hi!

The easiest way in a form is to make a subform in the main form to display the information from the many side of the relationship. Access will automatically dectect the relationship and build it in the subform and form through the wizard. Then the information will be displayed automatically also. Now if you want the information displayed only on the click of a button, that will take more doing, but I can walk you through it if you would like.

hth
Jeff Bridgham

 

[Paul7905]

Thanks Jeff,

If you can give me some pointers as to how to make this work with a button I'd be most grateful. Basically, I'd like the user to be able to select an item from the "one" side of the relationship, click a button to display the "many" side. (similar to how it works when you click the "minus" sign on the left side of the "one" side table)

thanks,
Paul

 

[jebry]

Hi Paul!

First, set the visible property of the subform to false. Add a command button to the form and call it cmdDetail and set it's caption property to Show Detail. Now, in the click event procedure of the command button use this code:

If cmdDetail.Caption = "Show Detail" Then
Me!YourSubform.Visible = True
cmdDetail.Caption = "Hide Detail"
Else
Me!YourSubform.Visible = False
cmdDetail.Caption = "Show Detail"
End If

That code will toggle the visible property of your subform and the caption of the command button.

hth
Jeff Bridgham

 

[Paul7905]

Jeff,

Still having problems here.

I have looked at the northwind database and the Customer Orders form which works like i want (with the button as you outlined) however i am unable to get my form to behave similar to the northwind example to begin with.

here is what i did, perhaps you can find a flaw here.

1. I defined the relationship between my two tables (one to many).

2. I created a blank Form.

3. I created a subform on the blank form for the "Batch" table (the one side of the relationship).

4. I created another subform on the blank form for the "Detail" table (the many side of the relationship).

the subforms display the data just fine, however, when i clik on a row in the "batch" subform, nothing happens in the "details" subform.

I must be doing something wrong which is causing access not to "automatically" build the relationship between the two subforms.

What am i doing wrong ?

thanks
Paul

 

[jebry]

Hi Paul!

I think that the main form should be created with its record source being the table on the one side of the relationship. Then you will need a subform only for the table on the many side of the relationship. As long as you have already established the relationship in Access, then the subform wizard will choose the correct parent/child linking fields from the two tables.

hth
Jeff Bridgham