Access 2007 - Main Form for changes; subform to show all history of Job

Posted on 2011-05-01
Last Modified: 2012-05-11
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"


The query for qryJCJobStatusCurrent sql stmt is as follows:

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);


Open in new window

Question by:wlwebb

    Author Comment

    OOPS, I attached the file 3 x's.  They are all the same.

    Author Comment

    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
    LVL 39

    Accepted Solution

    I don't quite understand your data structure: Tables tblQBCustomer and tblQBCustomerNames have same key field REFNUM.
    In your frmJCJobStatusCurrent form was used group query as a recordsource. It leads to recordset is not updatable.
    May be my example could be a first step to your aim. I've changed recordsource to table. Form frmJCJobStatusCurrent is used only for additions. You can select customer and see tasks.

    Author Comment

    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.

    Author Comment

    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.

    Author Closing Comment

    Worked like a charm

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now