Getting a subform to automatically display info from a table

[dread]

tblBuyers: BuyID (PK), FirstName, LastName
tblModels: ModID (PK), ModelName
tblRooms: RoomID (PK), ModID (FK), RoomName
tblFloors: FloorID (PK), FloorType
tblOrders: OrdID (PK), BuyID (FK), ModID (FK)
tblOrderDetails: OrdDetID (PK), OrdID (FK), BuyID (FK), ModID (FK), RoomID
(FK), FloorID (FK)

(There are many other fields in these tables but I’ve only listed the major
fields I think are relevant to my problem)

I am using tblOrder to store the Buyers and the Models (tblModels) that they
purchase.
I am using tblOrderDetail to store the flooring options (tblFloors) that a
Buyer selects for each Room (tblRooms) in the Model.

Each Model has more than one Room in tblRoom

I have a main form (frmBuyer) for Buyer information.
I have a subform (fsubOrder) (attached to the Buyer form) for Order
information (Models purchased by Buyer).
I have a subform (fsubDetail) (attached to fsubOrder) for Order Detail
information (Flooring Options for each Room in each Model).

My problem is that I want the Order Detail subform (fsubDetail) to
automatically display the list of Rooms associated with the Model(s) that the
Buyer has purchased (from the fsubOrder subform) and everything I have tried
so far does not work. I have tried to use examples given in some of the
messages posted here but none of them have worked. I am using Access 2002.

I have been trying to resolve this for over a week and I’m ready to scream.
Any help would be GREATLY appreciated.

Thank you.

 

[Mr B]

You don't say what you have your Link Child and Link Master properties for
your sub foms set to.

If you get these properties set to equal values for the parent form and the
child form your data should be displayed exactly as your describe

 

[Jeff Boyce]

How is the subsubform connected to the subform? How does it "know" which
Order it belongs to? Which Parent/Child field(s) do you have connecting
subform to mainform, and subsubform to subform?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sprinks]

Dread,

I think you need to insert the appropriate records from tblRooms based on
the model and order number of the current record, then requery the
sub-subform. This could be attached to the AfterUpdate event of your model
combo box. You may wish to add a confirming prompt or other code to delete
the records should the user choose the wrong model. If the RoomID control in
the sub-subform is a combo box of the ID and room name, with the ColumnWidth
of the ID set to 0", it will display the room names.

Dim strSQL As String

‘ Turn warnings off to eliminate prompt dialog box
DoCmd.SetWarnings False

' Set string and run insert query
strSQL = "INSERT INTO tblOrderDetails ( RoomID, OrdID ) " & _
" SELECT tblRooms.RoomID, tblOrders.OrdID " & _
" FROM (tblRooms INNER JOIN tblModels ON tblRooms.ModID = tblModels.ModID) "
& _
"INNER JOIN tblOrders ON tblModels.ModID = tblOrders.ModID " & _
"WHERE (((tblModels.ModID)=[Forms]![frmBuyer].[fsubOrder].[Form]![ModID])
AND ((tblOrders.OrdID)=[Forms]![frmBuyer].[fsubOrder].[Form]![OrdID]));"

DoCmd.RunSQL strSQL

' Requery subform
Me!fsubOrder.Form!fsubOrderDetail.Form.Requery

‘ Turn warnings back on
DoCmd.SetWarnings True

Also, because BuyID and ModID are defined in tblOrders, you don’t need them
in tblOrderDetails.

Hope that helps.
Sprinks

 

[dread]

LinkChild and LinkMaster are BuyID

You don't say what you have your Link Child and Link Master properties for
your sub foms set to.

If you get these properties set to equal values for the parent form and the
child form your data should be displayed exactly as your describe

 

[Sprinks]

Dread,

Setting the links between your subform and its sub-subform to ModID would
display the rooms automatically only if the RecordSource of the sub-subform
were tblRooms or a query based on it.

But to associate the floor finish with *this* model purchased by *this*
buyer, you need to insert new tblOrderDetails records, based on the room
definitions in tblRooms. See my other post.

Sprinks

 

[dread]

Hi Sprinks,

Does all the coding you listed go in the cboModel_AfterUpdate() event on the
fsubOrder subform? That is where I put it, but I am not getting all the
rooms displayed automatically. If I click on the arrow for the combo box it
displays a list of ALL the rooms in the room table instead of just the rooms
that relate to the model for that order.

I have the cboRoom control in fsubDetail pulling in the ID and name.

I also removed BuyID and ModID from tblOrderDetails.

I don't know if it matters, but I am using queries as the records source for
my forms.

Thanks for your help.

Diane

Dread,

I think you need to insert the appropriate records from tblRooms based on
the model and order number of the current record, then requery the
sub-subform. This could be attached to the AfterUpdate event of your model
combo box. You may wish to add a confirming prompt or other code to delete
the records should the user choose the wrong model. If the RoomID control in
the sub-subform is a combo box of the ID and room name, with the ColumnWidth
of the ID set to 0", it will display the room names.

Dim strSQL As String

‘ Turn warnings off to eliminate prompt dialog box
DoCmd.SetWarnings False

' Set string and run insert query
strSQL = "INSERT INTO tblOrderDetails ( RoomID, OrdID ) " & _
" SELECT tblRooms.RoomID, tblOrders.OrdID " & _
" FROM (tblRooms INNER JOIN tblModels ON tblRooms.ModID = tblModels.ModID) "
& _
"INNER JOIN tblOrders ON tblModels.ModID = tblOrders.ModID " & _
"WHERE (((tblModels.ModID)=[Forms]![frmBuyer].[fsubOrder].[Form]![ModID])
AND ((tblOrders.OrdID)=[Forms]![frmBuyer].[fsubOrder].[Form]![OrdID]));"

DoCmd.RunSQL strSQL

' Requery subform
Me!fsubOrder.Form!fsubOrderDetail.Form.Requery

‘ Turn warnings back on
DoCmd.SetWarnings True

Also, because BuyID and ModID are defined in tblOrders, you don’t need them
in tblOrderDetails.

Hope that helps.
Sprinks

tblBuyers: BuyID (PK), FirstName, LastName
tblModels: ModID (PK), ModelName
tblRooms: RoomID (PK), ModID (FK), RoomName
tblFloors: FloorID (PK), FloorType
tblOrders: OrdID (PK), BuyID (FK), ModID (FK)
tblOrderDetails: OrdDetID (PK), OrdID (FK), BuyID (FK), ModID (FK), RoomID
(FK), FloorID (FK)

(There are many other fields in these tables but I’ve only listed the major
fields I think are relevant to my problem)

I am using tblOrder to store the Buyers and the Models (tblModels) that they
purchase.
I am using tblOrderDetail to store the flooring options (tblFloors) that a
Buyer selects for each Room (tblRooms) in the Model.

Each Model has more than one Room in tblRoom

I have a main form (frmBuyer) for Buyer information.
I have a subform (fsubOrder) (attached to the Buyer form) for Order
information (Models purchased by Buyer).
I have a subform (fsubDetail) (attached to fsubOrder) for Order Detail
information (Flooring Options for each Room in each Model).

My problem is that I want the Order Detail subform (fsubDetail) to
automatically display the list of Rooms associated with the Model(s) that the
Buyer has purchased (from the fsubOrder subform) and everything I have tried
so far does not work. I have tried to use examples given in some of the
messages posted here but none of them have worked. I am using Access 2002.

I have been trying to resolve this for over a week and I’m ready to scream.
Any help would be GREATLY appreciated.

Thank you.