Access 2007 - Main Form for changes; subform to show all history of Job
Hi all,
I am attempting something that won't work in present form.
I have two queries of Job Statuses by Customer to get:
a)all job status history (qryJCJobStatusHistory) and
b) the last history status per job for each job (qryJCJobStatusCurrent
Both queries are based upon the table tblJCJobStatusHistory
Below is the code to get only the last status change for each job.
******************
What I am attempting to do:
I wanted to create a split form or some other style form where on One sheet the User will have the most recent job status for the particular job and then in the split window or at the bottom of the form it will show all of the history for that job.
My split form works to show the info but It is working in reverse to what I want. The user can add a new Job Status to the bottom split form but I want that form to be view only (not editable) and the main form to be where they "add" a new status.
I have attached a stripped out db. It's Access 2007
Any comments/suggestions etc.. are appreciated even if it's "You can't do that" which keeps me from hammering at this for hours when it simply can't be done.
What occurs presently in the form I have it is the main form info is i get "This Recordset is Not Updateable"
THANKS IN ADVANCE!!!!
I am attempting something that won't work in present form.
I have two queries of Job Statuses by Customer to get:
a)all job status history (qryJCJobStatusHistory) and
b) the last history status per job for each job (qryJCJobStatusCurrent
Both queries are based upon the table tblJCJobStatusHistory
Below is the code to get only the last status change for each job.
******************
What I am attempting to do:
I wanted to create a split form or some other style form where on One sheet the User will have the most recent job status for the particular job and then in the split window or at the bottom of the form it will show all of the history for that job.
My split form works to show the info but It is working in reverse to what I want. The user can add a new Job Status to the bottom split form but I want that form to be view only (not editable) and the main form to be where they "add" a new status.
I have attached a stripped out db. It's Access 2007
Any comments/suggestions etc.. are appreciated even if it's "You can't do that" which keeps me from hammering at this for hours when it simply can't be done.
What occurs presently in the form I have it is the main form info is i get "This Recordset is Not Updateable"
THANKS IN ADVANCE!!!!
The query for qryJCJobStatusCurrent sql stmt is as follows:
SELECT *
FROM qryJCJobStatusHistory AS tblTmpJCLastJobStatus INNER JOIN (SELECT RefNum, max(StatusDate) AS LastStatusdate FROM qryJCJobStatusHistory GROUP BY RefNum) AS qTmpJob ON (tblTmpJCLastJobStatus.RefNum=qTmpJob.RefNum) AND (tblTmpJCLastJobStatus.StatusDate=qTmpJob.LastStatusdate);
***************************ASKER
So this small db doesn't look as ominus as it did when it had 3 db's I edited the original question to remove the db that posted 3 times and here it is as a separate posting JobStatusMostCurrent.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for looking at. I will download your example and see what you are suggesting. As just an FYI the two tables Customer and CustomerNames are a function of the way Quickbooks stores Customers and Customer Jobs. I don't think I have used CustomerNames table yet in any of my queries or forms.
Essentially QB keeps all the names in one file. It adds a colon between the after what essentially would be the core customer to indicate Jobs. The RefNum of CustomerNames will most likely be renamed but the numbers Do infact come from the original export of the Customer table from QB.
Again, thank you for looking at this. It has me stumped. But that isn't difficult to do to me with Access.
Essentially QB keeps all the names in one file. It adds a colon between the after what essentially would be the core customer to indicate Jobs. The RefNum of CustomerNames will most likely be renamed but the numbers Do infact come from the original export of the Customer table from QB.
Again, thank you for looking at this. It has me stumped. But that isn't difficult to do to me with Access.
ASKER
Als,
That looks like what I was after. Only correction is down on the bottom sub form the Status is showing the wrong column. For re-open and re-close it is showing just the "open" or "closed" status. No big deal. Thanks.
That looks like what I was after. Only correction is down on the bottom sub form the Status is showing the wrong column. For re-open and re-close it is showing just the "open" or "closed" status. No big deal. Thanks.
ASKER
Worked like a charm
ASKER