Modify query to make a form updateable.

[iuianj07]

Hello guys,

I have a frmJobtracking which is bound to this query:

SELECT tblJobTracking.SitusID, tblJobTracking.WeekNumber, tblJobTracking.DealName, tblJobTracking.UBSOriginator, tblJobTracking.Broker, tblJobTracking.City, tblJobTracking.State, tblJobTracking.Region, tblJobTracking.AssetType, tblJobTracking.PropertyCount, tblJobTracking.Priority, tblJobTracking.PCComments, tblJobTracking.DocumentLinks, tblJobTracking.Update, tblJobTracking.Model, qryCurrentStatus.Status, qryCurrentStatus.Analyst, qryTAT.TAT, tblJobTracking.ConsolidatedFS, tblJobTracking.ConsolidatedRR
FROM ((tblJobTracking INNER JOIN qryLoggedIn ON tblJobTracking.SitusID = qryLoggedIn.SitusID) INNER JOIN qryCurrentStatus ON tblJobTracking.SitusID = qryCurrentStatus.SitusID) INNER JOIN qryTAT ON tblJobTracking.SitusID = qryTAT.SitusID;

Where qryTAT came from:

SELECT tblJobTracking.SitusID, tblDealStatus.StatusChangeID, tblDealStatus.StatusDate, [StatusDate]+1 AS TAT
FROM tblJobTracking INNER JOIN tblDealStatus ON tblJobTracking.SitusID = tblDealStatus.SitusID
WHERE (((tblDealStatus.StatusChangeID)=1));

qryCurrentStatus came from:

SELECT qryMaxStatusDate.SitusID, qryMaxStatusDate.WeekNumber, qryMaxStatusDate.DealName, tblStatusChange.Status, qryMaxStatusDate.MaxOfStatusDate, tblDealStatus.Analyst
FROM tblStatusChange INNER JOIN (qryMaxStatusDate INNER JOIN tblDealStatus ON (qryMaxStatusDate.MaxOfStatusDate = tblDealStatus.StatusDate) AND (qryMaxStatusDate.SitusID = tblDealStatus.SitusID)) ON tblStatusChange.StatusChangeID = tblDealStatus.StatusChangeID;

qryLoggedIn came from:

SELECT DISTINCT tblJobTracking.SitusID
FROM tblJobTracking LEFT JOIN tblDealStatus ON tblJobTracking.SitusID=tblDealStatus.SitusID
WHERE tblDealStatus.Analyst=getuserName();

this query returns the correct records we need, although this makes frmJobtracking not updateable... before this was okay... but then I was just told now that for the fields ConsolidatedFS and ConsolidatedRR need to be able to be updated by the user...

with how the RecordSource of frmJobTracking is setup, this is not possible...

Is there a way to modify the query, or anything so that I could make the form updateable?

Any help is greatly appreciated.

Thanks,

 

[dhookom]

qryMaxStatusDate sounds like it might be a totals query. If so, you can't make the query updateable if it contains the query.

I typically don't have more than a couple tables in my record sources. I use subforms for editing detailed information.

You might want to check out Using Updatable Queries White Paper.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

sorry I forgot to add qryMaxStatusDate:

SELECT tblJobTracking.SitusID, tblJobTracking.WeekNumber, tblJobTracking.DealName, Max(tblDealStatus.StatusDate) AS MaxOfStatusDate
FROM tblStatusChange INNER JOIN (tblJobTracking INNER JOIN tblDealStatus ON tblJobTracking.SitusID = tblDealStatus.SitusID) ON tblStatusChange.StatusChangeID = tblDealStatus.StatusChangeID
GROUP BY tblJobTracking.SitusID, tblJobTracking.WeekNumber, tblJobTracking.DealName;

Is this a totals query like you were talking about?

Thank you again...

 

[dhookom]

Yes, this is the totals query. You have to change you way of thinking about forms and record sources if you want to make sure all data is updateable.

Duane
Hook'D on Access
MS Access MVP