[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-05-01
Medium Priority
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
  • 5

Author Comment

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

Author Comment

ID: 35503076
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 40

Accepted Solution

als315 earned 2000 total points
ID: 35503878
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 35504522
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

ID: 35504891
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

ID: 35505250
Worked like a charm

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

829 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