asked on

Use code behind command button in subform to change recordsource for sub-subform

Good Day All, I am working on a form that keeps track of estimates for a service company. There is an initial estimate and then the final estimate. The subform identifies certain dates - Estimate Date, Start Date, etc, along with the first estimate total and final estimate total. Then there is a sub-subform (form in a subform) that allows the user to enter the service items that make up the estimate and there is a yes/no field that identifies if service item is for first estimate. Once the final estimate has been entered into the sub-subform (it may or may not be the same as the first estimate), I want the user to be able to click a button on the subform to determine which estimate is reflected in the sub-subform.

I guess I need to create code that will change the record source for the underlying query in the sub-subform. It will either be for all records where FirstEstimate = Yes or FirstEstimate = No. I have created two command buttons - "Click to View First Estimate" and "Click to View Final Estimate". I want to be able to place code behind these buttons to alter the recordsource for the sub-subform that shows the service items. Any suggestions will be greatly appreciated.

Here is the current SQL that shows all of the records:

SELECT tblServOrderEstimate.ServOrderEstimateID, tblServOrderEstimate.ServOrderID, tblServOrderEstimate.Amount, tblServOrderEstimate.TreeTypeID, tblTreeType.TreeType, tblServiceType.ServiceType, tblServOrderEstimate.ServiceTypeID, tblServOrderEstimate.InitialEstimate
FROM tblTreeType INNER JOIN (tblServiceType INNER JOIN (tblServOrder INNER JOIN tblServOrderEstimate ON tblServOrder.ServOrderID = tblServOrderEstimate.ServOrderID) ON tblServiceType.ServiceTypeID = tblServOrderEstimate.ServiceTypeID) ON tblTreeType.TreeTypeID = tblServOrderEstimate.TreeTypeID;

FYI, I am running XP and Access 2002 in 2000 mode.

Thanks in advance,

David

Jim Horn

Private Sub YourButton_Click

Me.YourSubSubformControlName.Form.RecordSource = "SELECT blah blah blah FROM blah"
Me.YourSubSubformControlName.Form.Requery '<--- You may or may not need this, you'll have to test

End Sub

jefftwilley

I don't see the field FirstEstimate in your SQL

yddadsjd95

ASKER

Thanks Guys,

Jim, does the Select statement include the SQL with a where statement at the end. I assumed that I would select the same fields form the original SQL and add the where statement, so I tried this:

Me.frmServOrderEstimates.Form.RecordSource = "SELECT tblServOrderEstimate.ServOrderEstimateID, tblServOrderEstimate.ServOrderID, tblServOrderEstimate.Amount, tblServOrderEstimate.TreeTypeID, tblTreeType.TreeType, tblServiceType.ServiceType, tblServOrderEstimate.ServiceTypeID, tblServOrderEstimate.InitialEstimate _
FROM tblTreeType INNER JOIN (tblServiceType INNER JOIN (tblServOrder INNER JOIN tblServOrderEstimate ON tblServOrder.ServOrderID = tblServOrderEstimate.ServOrderID) ON tblServiceType.ServiceTypeID = tblServOrderEstimate.ServiceTypeID) ON tblTreeType.TreeTypeID = tblServOrderEstimate.TreeTypeID where tblServOrderEstimate.InitialEstimate = -1;"
------------^SQL for 1st or initial estimate

But Access gave me the compile error - Expected: end of statement - and it highlighted FROM in the SQL.

Jeff, I am sorry, the yes/no field for the first estimate is named InitialEstimate.

Further guidance will be appreciated.

David

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yddadsjd95

ASKER

Thanks Jim, sometimes all I can do is laugh when I learn something new in Access. I had quite a few compile errors trying make certain that all of the tables were identified correctly, but in the end, it did what you told me it would do. Plus I did not need the requery function.

Thanks again and have blessed weekend as you have blessed my day,

David

jefftwilley

Nice Jim..

An alternative..lol..late in the game I know.

if the recordsource of your form shows all records then a filter would have worked as well

Me.Filter = "[InitialEstimate] = -1"
Me.FilterOn = True

Me.Filter = "[InitialEstimate] = 0"
Me.FilterOn = True

to clear
me.FilterOn = False

Enjoy!! the weekend.
J