Continuous Forms subform referenced by subform

  • Thread starter Thread starter h2fcell
  • Start date Start date
H

h2fcell

Hello,
I have a form, frmRates with a list box and two subforms, subRatesA and
subRatesB. The first subform, subRatesA has a default view property of
“Continuous Formsâ€. It gets its data using a query Record Source that
filters based on the list box pick.

SELECT tblAcct5_HotelRatesLookUp.hotel_id,
tblAcct5_HotelRatesLookUp.rate_code, tblAcct5_HotelRatesLookUp.hotel_status,
tblAcct5_HotelRatesLookUp.include_exclude_flag,
tblAcct5_HotelRatesLookUp.FromDate, tblAcct5_HotelRatesLookUp.ToDate,
tblAcct5_HotelRatesLookUp.Rate, tblAcct5_HotelRatesLookUp.country,
tblAcct5_HotelRatesLookUp.city, tblAcct5_HotelRatesLookUp.hotel_name,
tblAcct5_HotelRatesLookUp.supplier_id, tblAcct5_HotelRatesLookUp.description
FROM tblAcct5_HotelRatesLookUp
WHERE
((tblAcct5_HotelRatesLookUp.rate_code)=[Forms]![frmRates]![lboRoomTypes])
ORDER BY tblAcct5_HotelRatesLookUp.FromDate;

I’d like to have the second form, subRatesB populate base on three fields
[FromDate], [ToDate] and [hotel_name] of the selected record in subRatesA.
The first thing I need to know is what event should I use to requery
subRatesB every time the user selects a different record in subRatesA. The
second thing I need to know is how to reference the three fields from the
record selected in subRatesA in the query for subRatesB.

I tried the below but get a prompt for
[Forms]![frmRates]![Form]![subRoomRates]![hotel_name] when frmRates first
opens.

SELECT tblAcct5_SupplierID.s_contract_id, tblAcct5_SupplierID.product_set,
tblAcct5_SupplierID.s_product_desc, tblAcct5_SupplierID.hotel_id,
tblAcct5_SupplierID.hotel_name, tblAcct5_SupplierID.FromDate,
tblAcct5_SupplierID.ToDate
FROM tblAcct5_SupplierID
WHERE
(((tblAcct5_SupplierID.hotel_name)=[Forms]![frmRates]![Form]![subRoomRates]![hotel_name])
AND
((tblAcct5_SupplierID.FromDate)=[Forms]![frmRates]![Form]![subRoomRates]![FromDate])
AND
((tblAcct5_SupplierID.ToDate)=[Forms]![frmRates]![Form]![subRoomRates]![ToDate]));

Thanks in advance for your help.
 
Two things;

1) You said you have two subforms, subRatesA and subRatesB,
but you are appear to be trying to reference a subform control
named subRoomRates.

2) Your syntax is wrong for the way you are trying to reference the
control on the subform. Assuming the subform control is named
subRatesA, then the syntax would be;

Forms!frmRates!subRatesA.Form![hotel_name]

Keep in mind that the name of the *subform control* (the window
that holds the subform) may or may not be the same as the name
of the subform itself. You need to reference the name of the
subform control, followed by the .Form property, followed by
the name of the control (text box, etc.) on the subform.

As far as requerying the second subform when the user changes
records in the first subform, use the Current event of the first subform.

Private Sub Form_Current ()

Me.Parent!subRatesB.Form.Requery

End Sub

--
_________

Sean Bailey


h2fcell said:
Hello,
I have a form, frmRates with a list box and two subforms, subRatesA and
subRatesB. The first subform, subRatesA has a default view property of
“Continuous Formsâ€. It gets its data using a query Record Source that
filters based on the list box pick.

SELECT tblAcct5_HotelRatesLookUp.hotel_id,
tblAcct5_HotelRatesLookUp.rate_code, tblAcct5_HotelRatesLookUp.hotel_status,
tblAcct5_HotelRatesLookUp.include_exclude_flag,
tblAcct5_HotelRatesLookUp.FromDate, tblAcct5_HotelRatesLookUp.ToDate,
tblAcct5_HotelRatesLookUp.Rate, tblAcct5_HotelRatesLookUp.country,
tblAcct5_HotelRatesLookUp.city, tblAcct5_HotelRatesLookUp.hotel_name,
tblAcct5_HotelRatesLookUp.supplier_id, tblAcct5_HotelRatesLookUp.description
FROM tblAcct5_HotelRatesLookUp
WHERE
((tblAcct5_HotelRatesLookUp.rate_code)=[Forms]![frmRates]![lboRoomTypes])
ORDER BY tblAcct5_HotelRatesLookUp.FromDate;

I’d like to have the second form, subRatesB populate base on three fields
[FromDate], [ToDate] and [hotel_name] of the selected record in subRatesA.
The first thing I need to know is what event should I use to requery
subRatesB every time the user selects a different record in subRatesA. The
second thing I need to know is how to reference the three fields from the
record selected in subRatesA in the query for subRatesB.

I tried the below but get a prompt for
[Forms]![frmRates]![Form]![subRoomRates]![hotel_name] when frmRates first
opens.

SELECT tblAcct5_SupplierID.s_contract_id, tblAcct5_SupplierID.product_set,
tblAcct5_SupplierID.s_product_desc, tblAcct5_SupplierID.hotel_id,
tblAcct5_SupplierID.hotel_name, tblAcct5_SupplierID.FromDate,
tblAcct5_SupplierID.ToDate
FROM tblAcct5_SupplierID
WHERE
(((tblAcct5_SupplierID.hotel_name)=[Forms]![frmRates]![Form]![subRoomRates]![hotel_name])
AND
((tblAcct5_SupplierID.FromDate)=[Forms]![frmRates]![Form]![subRoomRates]![FromDate])
AND
((tblAcct5_SupplierID.ToDate)=[Forms]![frmRates]![Form]![subRoomRates]![ToDate]));

Thanks in advance for your help.
Click to expand...
 
Hi Sean,
Thanks for your reply. I added the following to the “On Current “event of
the “Continuous Forms†sub form. But nothing happens when I select a record
on the “Continuous Forms†sub form. As if the event is not kicking off.

Private Sub Form_Current()
Me.Parent!SupplierIDsubform.Form.Requery
End Sub

And that query now looks like the below.

SELECT tblAcct5_SupplierID.s_contract_id, tblAcct5_SupplierID.product_set,
tblAcct5_SupplierID.s_product_desc, tblAcct5_SupplierID.hotel_id,
tblAcct5_SupplierID.hotel_name, tblAcct5_SupplierID.FromDate,
tblAcct5_SupplierID.ToDate
FROM tblAcct5_SupplierID
WHERE (((tblAcct5_SupplierID.hotel_name)=[Forms]![
frmRates]![SupplierIDsubform].[Form]![hotel_name]))
AND
((tblAcct5_SupplierID.FromDate)=[Forms]![frmRates]![SupplierIDsubform].[Form]![FromDate])
AND
((tblAcct5_SupplierID.ToDate)=[Forms]![frmRates]![SupplierIDsubform].[Form]!
[ToDate]));

Do you know of any example DB I could download that might have an example of
a “Continuous Forms†sub form driving the data appearing in a second sub
form? Thanks.


Beetle said:
Two things;

1) You said you have two subforms, subRatesA and subRatesB,
but you are appear to be trying to reference a subform control
named subRoomRates.

2) Your syntax is wrong for the way you are trying to reference the
control on the subform. Assuming the subform control is named
subRatesA, then the syntax would be;

Forms!frmRates!subRatesA.Form![hotel_name]

Keep in mind that the name of the *subform control* (the window
that holds the subform) may or may not be the same as the name
of the subform itself. You need to reference the name of the
subform control, followed by the .Form property, followed by
the name of the control (text box, etc.) on the subform.

As far as requerying the second subform when the user changes
records in the first subform, use the Current event of the first subform.

Private Sub Form_Current ()

Me.Parent!subRatesB.Form.Requery

End Sub

--
_________

Sean Bailey


h2fcell said:
Hello,
I have a form, frmRates with a list box and two subforms, subRatesA and
subRatesB. The first subform, subRatesA has a default view property of
“Continuous Formsâ€. It gets its data using a query Record Source that
filters based on the list box pick.

SELECT tblAcct5_HotelRatesLookUp.hotel_id,
tblAcct5_HotelRatesLookUp.rate_code, tblAcct5_HotelRatesLookUp.hotel_status,
tblAcct5_HotelRatesLookUp.include_exclude_flag,
tblAcct5_HotelRatesLookUp.FromDate, tblAcct5_HotelRatesLookUp.ToDate,
tblAcct5_HotelRatesLookUp.Rate, tblAcct5_HotelRatesLookUp.country,
tblAcct5_HotelRatesLookUp.city, tblAcct5_HotelRatesLookUp.hotel_name,
tblAcct5_HotelRatesLookUp.supplier_id, tblAcct5_HotelRatesLookUp.description
FROM tblAcct5_HotelRatesLookUp
WHERE
((tblAcct5_HotelRatesLookUp.rate_code)=[Forms]![frmRates]![lboRoomTypes])
ORDER BY tblAcct5_HotelRatesLookUp.FromDate;

I’d like to have the second form, subRatesB populate base on three fields
[FromDate], [ToDate] and [hotel_name] of the selected record in subRatesA.
The first thing I need to know is what event should I use to requery
subRatesB every time the user selects a different record in subRatesA. The
second thing I need to know is how to reference the three fields from the
record selected in subRatesA in the query for subRatesB.

I tried the below but get a prompt for
[Forms]![frmRates]![Form]![subRoomRates]![hotel_name] when frmRates first
opens.

SELECT tblAcct5_SupplierID.s_contract_id, tblAcct5_SupplierID.product_set,
tblAcct5_SupplierID.s_product_desc, tblAcct5_SupplierID.hotel_id,
tblAcct5_SupplierID.hotel_name, tblAcct5_SupplierID.FromDate,
tblAcct5_SupplierID.ToDate
FROM tblAcct5_SupplierID
WHERE
(((tblAcct5_SupplierID.hotel_name)=[Forms]![frmRates]![Form]![subRoomRates]![hotel_name])
AND
((tblAcct5_SupplierID.FromDate)=[Forms]![frmRates]![Form]![subRoomRates]![FromDate])
AND
((tblAcct5_SupplierID.ToDate)=[Forms]![frmRates]![Form]![subRoomRates]![ToDate]));

Thanks in advance for your help.
Click to expand...
Click to expand...
 
Back
Top