Replies

You don’t indicate what version of Access you are using, which may or may not have any bearing on the issue, but in general the more recent the version, the more picky the database engine is about what queries are updateable, and what are not. You could try setting the property of the query called Recordset Type to Dynaset (Inconsistent Updates), but I would be more inclined to make a temporary table as the original poster did, or take the VBA approach if you have a multi-user situation.

Apologies, it’s access 2007 office sp2
Again, with multi-users the temp table is fraught with sharing issues.

Following with interest….because I have same error reporting, so if I may.
one original table with data:
tbl_PO_Summary
Client_Ref – text
Purchase_Order – text
Tax_Year – number
Tax_Period – number
BillAmount – number

A query built on sql db table derives the current sum bill amount as posted in separate bill fields. Because the number of postings are many, then the billamount is summed over the grouped period
qry_PO_Details

The query and the table are brought together in an update query (but same error of “Operation must use an updateable query” occurs)

SQL for the update query is:
UPDATE DISTINCTROW qry_PO_Details INNER JOIN tbl_PO_Summary ON (qry_PO_Details.Tax_Period = tbl_PO_Summary.Tax_Period) AND (qry_PO_Details.Tax_Year = tbl_PO_Summary.Tax_Year) AND (qry_PO_Details.Purchase_Order = tbl_PO_Summary.Purchase_Order) AND (qry_PO_Details.Client_Ref = tbl_PO_Summary.Client_Ref) SET tbl_PO_Summary.BillAmount = ([qry_PO_Details].[billamount]);

If needed I’ll post new thread but seeing as it is practically identical error then I figured would be OK to post.
Any help much appreciated.
Thanks
Alan

Cheshire, cloudy and wet.

Thanks Hans.

Nope, just plain text when F2 selected.
Can I add the # as part of the update code? or is it as excel array formula and only relevant when entered correctly?

Thanks for the pointer Hans, just updated code and works a treat with # added as part of the sql insert.

Cheers
Alan

If you open the table, go to the FPPath field and press F2, what do you see? There should be # characters around the path/filename, e.g.

#s:mancpayrtido12.txt#

or

s:mancpayrtido12.txt#s:mancpayrtido12.txt#

Nope, just plain text when F2 selected.
Can I add the # as part of the update code? or is it as excel array formula and only relevant when entered correctly?

Bit more strangeness.
I added a new record manually and typed in the original file path/name and the record was not prevented from being added…even though the field is set to Primary-No Duplicates.
The hyperlink did work on this field though.

Threads beginning with [post=”745892″]Post 745892[/post] and [post=”736866″]Post 736866[/post] have quite a bit of code along these lines.

Thanks John, good starters there.

Thanks Wendell, I’ll look into this.

SP2 works a treat in restoring the export report to Excel option.

Initially, i tried a pivt table but it has some restriction and does not serve the purpose. Thanks for taking time.

Prasad, you’ll need to explain the summary requirements better than the summary table.
From what I can see of the summary table there does not appear to be one rule as to how it is derived.
The first entries are inflow, but then your llyod entries are inflow and outflow and missing some of your inflow.
Can you give definitive rules for the summary?

We had very similar experience where tabs appeared to be missing (mysteriously ‘stolen’ by the workstation bug).
For some reason the worksheet window had been ‘restored’, moved down the application window, and then ‘stretched’ back to fill the application window. So it looked quite normal apart from the tabs and scroll bars missing. The bit that gave it away was the close worksheet X being off screen to the right.
Very crafty and mysterious.

Could you give us an idea how the end result should look?

Possibly looking for pivot table type results? with bal per customer?

Sum of Amt.	
Cust	Total
sun	11482
Asso	19225
dave	24775
fransis	68496
Llyod	-44136
ultra	-25170
Grand Total	54672

Hi Alan

What file holds the macro which you are trying to disable? I am suspicious that the Personal file is immune from the Security settings.

Hmmm, good call.

The errant file contains a workbook open macro which then rippled through all of the worksheets in the collection to perform a brief check (and minor reformat). Unfortunately the minor reformat changed the amendments the user had already made before previously saving the workbook, so it was a tad annoying.

I have checked the personal file and all other xl start up file options but none appeared to contain any modules or macro code.
However, of the available settings the first option to “Disable all macros without notification” also includes the caveat that it will NOT block macros from files which are contained within your Trusted locations. OK……but other settings do not contain that caveat so I thought they would not be affected by it. This is incorrect.

To resolve..I moved the workbook to a non-trusted location and as soon as I opened the file it presented the ‘options’ conotrl indicating that a macro was present.

I’m not so sure I like this new security setting as it appears that if you save the file to any trusted location such as your network home drive then macros are run without prompting.
The original reason for making the location trusted was in response to an access database prompt, but it now applies to all other office files too.

Users may not always remember to enter the content as minutes.
Alternatively, you can built this in the formula which show the end result.

perhap this in C2 with Time formatting

=(A2/1440)*B2

A2 = 29
B2 = 5

And of course, converting 08:10 to decimal hours can be as simple as multiplying the cell by 24
=A1*24
where A1 contains 08:10 = 8.2

Excel uses 15 significant digits for calculations – it cannot handle numbers with 28 digits without losing a lot of information.
You’d have to store the IBAN numbers as text and program the calculations yourself, or use an add-in such as xlPrecision.

Many thanks for that Hans.
Until now it hasn’t been a problem as we use independent app for IBAN

Using the cell value have managed to run checksum using good ol’fashioned long division iteration.
myCheck = 97
mylen = Len(ActiveCell.Value)
For i = 1 To mylen
myrem = (myrem & Mid(ActiveCell.Value, i, 1)) Mod myCheck
Next i

‘use myRem to post any non 1 errors

Excellent Hans, staring me in the face all along.
Thanks
Alan