Synchronize combobox on subform

[Brian Snelling]

Hi,
I'm re-posting and rephrasing my question.

Using Access97 and WinXP I'm trying to control the drop-down list shown in a
subform combobox from the selection made in a combobox on the main form.


The main form is "frmDailyRecords"
The main form combobox is "cbxProjects"
The subform is "fsubDailyRecordDetails"
The subform combobox is "cbxBlocks"

tblProjects has one-to-many relation to tblDailyRecords
tblProjects has one-to-many relation to tblBlocks
tblDailyRecords has a one-to-many relation to tblDailyRecordDetails
tblDailyRecordDetails has a one-to-many relation to tblBlocks

I've tried to use the technique from the MS knowledge base #98660 but have
some problems. Contents of the
dropdown list do not change with a change in Project unless I close and
reopen frmDailyRecords- if I change project
and then close and reopen the form the list will change but records already
saved will only show a block if it's a member of the now current list for
cbxBlocks.

Hope I've been clear enough here. Any help very welcome.

Brian S.

 

[boblarson]

You need to set the criteria of the underlying query of the combo box on the
subform to the combo on the main form and in the AFTER UPDATE event of the
combo on the main form, you need to add a Requery to the subform combo:

Me.YourSubformContainerNameHere.Form.YourSubformComboNameHere.Requery

*Note = where it says YourSubformContainerNameHere is the control on the
main form that houses the subform, not the subform. It can be the same name
as the subform but it may not be and if that's the case you need to use the
control name and not the subform name.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[Brian Snelling]

Hello Bob,

Thanks very much for the answer. I'm still having trouble with this .
cbxBlocks now shows the correct blocks for whichever project is selected but
previously saved records from a different project show blank for block (they
are there when I check the table) Can I modify to show everything in saved
records but still limit the combo box? Perhaps if I add more info.

This is my Row Source for cbxBlock:

SELECT tblDailyRecordDetails.BlockID, tblBlocks.BlockNumber,
tblBlocks.Location1, tblBlocks.Location2, tblBlocks.SU, tblBlocks.ProjectID
FROM tblBlocks INNER JOIN tblDailyRecordDetails ON tblBlocks.BlockID =
tblDailyRecordDetails.BlockID WHERE
(((tblBlocks.ProjectID)=[Forms]![frmDailyRecords]![cbxProjectID]));

This is my Row Source for cbxProject:

SELECT DISTINCTROW tblProjects.ProjectID, tblProjects.ProjectName,
tblProjects.ContractNumber, tblCustomers.CustName, tblCustomers.CustomerID,
tblProjects.ProjectYear FROM tblCustomers INNER JOIN tblProjects ON
tblCustomers.CustomerID = tblProjects.CustomerID;



This is my query for main form: frmDailyRecords

SELECT DISTINCTROW tblDailyRecords.DailyRecordID, tblDailyRecords.ProjectID,
tblDailyRecords.Date, tblDailyRecords.Name, tblDailyRecords.ExtraPay,
tblDailyRecords.ExtraPayFor, tblDailyRecords.DayEIHours,
tblDailyRecords.RWS, tblDailyRecords.CampCost, tblDailyRecords.CashAdvance,
tblDailyRecords.ChequeAdvance, tblDailyRecords.OtherCosts,
tblDailyRecords.Notes, tblDailyRecords.EmployeeID, tblProjects.ProjectName,
tblProjects.CustomerID, tblProjects.ContractNumber, tblProjects.ProjectYear,
tblCustomers.CustName, tblCustomers.CustomerID, Weekday([date]) AS Expr1
FROM (tblCustomers INNER JOIN tblProjects ON tblCustomers.CustomerID =
tblProjects.CustomerID) INNER JOIN tblDailyRecords ON tblProjects.ProjectID
= tblDailyRecords.ProjectID ORDER BY tblDailyRecords.Date;