Hi all,

A bit of background, i have inherited an Access 2007 database and the person who created it has now left the company unfortunately. I've been asked to make some updates with which i am struggling with. I'll try make my explanation as simple as possible:

I have a subform, within an overall form used to create a new document. This subform was initially a way of creating a new work package which underlies the overall document. Since modifying it to show the status of eack work package under each document (Using calculated fields) i cannot add a new record. Before adding the status to each work package in the form, it was possible for a new line to be added (Record) whereby the work package ID was an Autonumber from tbl_WorkPackage, the title was free text and a drop down box was available to select which department the work package belongs to. After this a button would be clicked which would open the work package creation form and auto populate the parent document (ORD Reference) using a Dlookup (unfortunately, the guy that built the database left before i got involved hence why i am struggling). I have attached some images of the forms to make it clearer.

Image 1: Original form and subform, before adding work package status.
Image 2: New form and subform with status added (Calculated fields).
Image 3: Related record to populate the remaining information for the Work Package.

I hope i've explained things properly.

Can you post the sql view of the recordsource for the subform - both the original version and the current version, please.

Hi Peter,

Thank you very much for your help. The record source for the new subform (Including current status) is:
SELECT tbl_WorkPackage.[Internal ID], "AIMS" & Format([Internal ID],"00000") AS WPID, tbl_WorkPackage.[ORD Reference], tbl_WorkPackage.Title, tbl_WorkPackage.Fleet, qry_WPHistoryTable.[MaxOfStatus Date], qry_WPHistoryTable.Description

The original recordsource from prior to these changes is:
SELECT tbl_WorkPackage.[Internal ID], "AIMS" & Format([Internal ID],"00000") AS WPID, tbl_WorkPackage.[ORD Reference], tbl_WorkPackage.Title, tbl_WorkPackage.Fleet FROM tbl_WorkPackage;

I think im going to be frequenting this forum alot, as there appear to be alot of changes required to the DB...

Regards

Donna

You haven't included the whole of the new recordsource sql, but I can guess from this bit..

qry_WPHistoryTable.[MaxOfStatus Date], qry_WPHistoryTable.Description

I take it this query (qry_WPHistoryTable) is a groupBy/Totals query.

I'm afraid that makes the new recordsource for the form not-updateable. Any use of groupby/max/min etc makes a query non-updateable.
It is not reasonable, I know, but that's the way it it is.

You need to remove qry_WPHistoryTable from the recordsource.
You should be able to change the recordsource to use a DMax() function on the original table that holds the status date, although I can't provide any further detail without seeing the tables.

Hi Peter,

you are correct it does use calculated fields, as i couldnt figure out another way to return just the latest record for each work package (I was getting a record for every status). I did intial try and create a query that would update a table with just the Latest status and date, however again it was using a calculated field so i couldnt use an update query. I did try and combine a delete and append query to repopulate the table but that just caused all sorts of problems. I'm just googling Dmax functions now, but its all very confusing. I have updates to this database coming out of my ears at the moment....i think i'll have to get myself on a course or something.

Apologies about not posting the full SQL for the new form, its below in case that helps.

SELECT [tbl_WorkPackage].[Internal ID], [tbl_WorkPackage].[Title], [tbl_WorkPackage].[Fleet], [qry_WPHistoryTable].[MaxOfStatus Date], [qry_WPHistoryTable].[Description] FROM ([tbl_WorkPackage] INNER JOIN [qry_WPHistoryTable] ON [tbl_WorkPackage].[Internal ID] =[qry_WPHistoryTable].[WP Reference])

The table im trying to pull the data from contains all status's and dates for every work package. I have included an export, and from what i can see WP Reference, Status and Status owner are all lookup fields. Should i create a new table linked to this one to show only the latest status using a DMax funtion or is it just amatter of utilising the function in the SQL? Apologies for all the questions, and thank you once again for your help.

Regards

Donna

I'm having to guess at your table and field names - so you need to check & correct this- but see if this works. It assumes that Internal Id is a number.


SELECT [tbl_WorkPackage].[Internal ID], [tbl_WorkPackage].[Title], [tbl_WorkPackage].[Fleet],
dmax("[Status date]", "WPHistoryTable", "[WP Reference]=" & [Internal ID]),
DLookup("[Description]", "[WPHistoryTable]", "[WP Reference]=" & [Internal ID])
FROM ([tbl_WorkPackage]

Hi Peter,
Apologies i thought i had attached an export of my table.

In your example does it assume that the status is stored in the WorkPackage table? As unfortunately the status date and description is only stored in the WorkPackageHistory table. The table headings are below:

tbl_WorkPackageHistory
ID WP Reference Status Status Date Status Owner Comments

tbl_WorkPackage

Internal ID Historic Number ORD Reference Title Description System Level 2 System Level 3 System Level 4 Customer Asset Description Fleet Requirments Responsible SOE Responsible PSE Responsible CRE Owner

I think im just getting myself confused here. Which isn't difficult these days.

I've tried modifying the query to:

SELECT tbl_WorkPackage.[Internal ID], "AIMS" & Format([Internal ID],"00000") AS WPID, tbl_WorkPackage.[ORD Reference], tbl_WorkPackage.[Title], tbl_WorkPackage.[Fleet]
dmax("tbl_WorkPackageHistory.[Status date]", "tbl_WorkPackageHistory.[Status]", "[WP Reference]=" & [Internal ID]),
DLookup("[Description]", "[tbl_WorkPackageHistory]", "[WP Reference]=" & [Internal ID])
FROM ([tbl_WorkPackage]

Im getting an invalid control source, due to an incorrect field list....Is it always this difficult? Lol.

Regards

Donna

You are doing too much ...
for a dmax() the essential structure is
Dmax("Whichfield", "whichtable", "searchcriteria")

Try...

SELECT [Internal ID], "AIMS" & Format([Internal ID],"00000") AS WPID, [ORD Reference], [Title], [Fleet],
dmax("[Status date]", "[tbl_WorkPackageHistory]","[WP Reference]=" & [Internal ID]) as StatusDate,
DLookup("[Description]", "[tbl_WorkPackageHistory]", "[WP Reference]=" & [Internal ID]) as PackageDescription
FROM [tbl_WorkPackage]

Woohoo, Thanks Peter, Seem to be making progress now, and learning along the way also.

I had to make a slight amendment to fit in with my tables, but so far so good. I just have what appears to be a couple of minor problems now:

The date format in my Status Date column on my form is returning as dd/mm/yyyy whereby in my table i have specified dd/mm/yyy hh:nn:ss. I have added this into the format on the form field, but it doesnt seem to be changing. I sthere anything else i am missing to get it to show with the time?

Also, and this is probably quite minor, the PackageDescription is showing as a number (the autonumber from the tblWPStatus table), should i link the status to this table instead of the tbl_WorkPackageHistory table to allow it to show the relative text field?

Regards

Donna

Assuming the data in tbl_WorkPackageHistory has non-zero time values then I can't explain at all why you would not see these using a format that includes time symbols.
If you can post a database showing the problem I'll look at it.


Are you saying that Description is itself defined as a lookup field in the table? If so then the results are as expected.
Yes you need to include the table that contains the real description data in your query - you can do this by joining the existing query to the relevant table in a new query.
Or you can change the table used in the dlookup to a query which includes the description field from its true source.

I understand the time issue, it is due to this being implemented as a counter measure to ensure we do not report duplicate status's for a work package. Whereby a status has been updated since this implementation, the time is now showing.

As for the description, yes unfortunately this is a lookup field in the table. However, im confused as to what you mean. Would i create a new standalone query (I.e. one that is unrelated to the sql statement posted above) and then link to that within the sql query? Or just include an additional Dlookup? If so would i need to include an additonal Dlookup function, or add it in to the existing one?

My preference would be to build a query to use in the Dlookup instead of tbl_WorkPackageHistory.
So use tbl_WorkPackageHistory and the table that contains the stored description. If you end up with two fields callled Desription in your query then rename one of then by prefixing it in the field box.
Description1:Description
This will avoid some messiness in references.

It woud be possible to use a Dlookup() around the existing dlookup() but I think that is ugly and more to the point I think would be imposing a greater performance penalty.

Thanks for all you help Peter,

It works like a charm