Replies

As a further thought to the ‘signing macro’ question – when I last looked Verisign wanted $400 USD annually to provide authentication services, and it looked like they were asking for the equivalent of and SEC 10K or something to even consider providing it. After I e-mailed them to say that this was an astoundingly high charge they called me back to say that they consider that they are providing a guarantee that I will not be writing malicious code, and there is a high cost of providing that assurance to the user.

This is nonsense, as all Verisign provides is assurance that if the code was signed by “D Cardno” that it actually came from D Cardno and has not been altered. Anyone who chooses to run code from D Cardno on their computer is taking their chances based on what they know of me (heh heh heh….) – the signature just reduces the risk that they are getting damaged (or deliberately altered) goods in the process. The nice lady from Verisign didn’t seem to understand that concept, and in our litigious age, perhaps they are providing a warranty that they don’t intend to, but will be forced on them by the courts – I doubt it, but you never know.

In any event, if you make your living by selling VBA or VB code, then it is probably worth it to get the signature system set up – but then you probably have the developer’s edition anyway. If, like me, you sell the occasional bit of code in connection with analysis of a much larger problem (I do financial and economic analyses of pipelines and power plants – every now and then it is helpful to automate some of the iterative calculations) then it is not worth it.

My clients just have to trust me to write code that won’t screw up anything on their computers (just like they would if I used Verisign anyway). For integrity of code between me and them, I encrypt e-mails with PGP: if they don’t have it I recommend that they get it and use it. If they don’t I tell them that I will not be responsible for loss of confidentiality or errors in transmission due to insecure e-mail communication, and suggest that we just exchange floppies. Inevitably, they accept the e-mail risk in the interests of speedy, convenient communication.

People can be swept into mass hysteria by the Kournikova virus, and practically wet themselves at the suggestion that a ‘hacker’ could break into their data files on a reasonably secure server that no one has any interest in, but they will not take the most basic easily-implemented steps to protect against an insecure communications channel open to anyone who is curious!

I had to do something very similar to this to enter four formulas per row on a datafile imported from a G/L package monthly. I write the formulas in once and then select the range they are in and copy / paste it down the used range in the s/sheet instead of iterating from the starting row to the last used row in the range, typically about row 1500-1700 or so. I never thought of stepping through row by row – but on a big s/sheet I suspect copy / paste will be faster than iteration.

John – I just tried it and it worked fine for me. I cut & pasted the VBA from your post (other than removing “private” keyword). It cleared all the “” cells in the selected range.
Is there something odd about your workbook or your IF statements? – do some of them yield “_” rather than “”?

Could you post a copy of a s/sheet where the procedure isn’t working?

Thanks Patt and Charlotte, for your suggestions. I have not had a chance to do much with them today, but will look at the relationships table tomorrow, and also just try hacking away at the SQL to see what the results are like.

Jack –

I’ll take help from wherever I can get it! Thanks, that was a good suggestion and I will keep it in mind.

BINGO!

Thanks, Francois – works like a charm.

It boggles my mind sometimes: – ask a question in Vancouver, Canada, go to lunch; and get an answer from Belgium!

If the value in [FCO] is a boolean Yes/No value, then you don’t need the quotation marks when you test for it – that is confusing Access, since it is trying to comapare a boolean with a string.

Your expression should read:

Void: IIf([Invoice]![FCO]=No,[Tot_Inv],[Tot_Inv]*-1).

How about searching against:

iif(isnull(returndate),{today / today + 1 / dec 2020 / etc}, returndate) 

That way if you want to exclude keys not returned from this or another query you can use a default return date
of tomorrow or sometime far in the future…

Charlotte
“If you replicated the back end and placed a copy of it on each machine, I could understand it.”

Obviously my explanation was not very clear, since that is what I am thinking of doing: placing a replicated back-end on each user machine which they would use as a data source, and forcing synchronization with a network “master” periodically. The existing back end is replicated because that let me change table layouts and introduce new tables during a period when users were inputting lots of data. In retrospect, that replication was probably not needed – but it will live on like the human appendix. Linking to the back end on a network drive is getting to be slow – I will have to do more work to determine whether this is a consistent problem or I just hit it at a bad moment

The question now is whether to distribute replicas to user machines and if so, how to manage the synchronization. I could allow users to synchronize at their convenience by way of a command button or force a synchronization before printing or running certain queries – but I am worried that the user who has not synchronized may have input relevant information.

I am considering attaching an on-timer event to each form in the database that would check periodically whether the last synchronization had occurred less than a given period of time previously (I would have to create a local table to record the time of the last synch). If the last synch has been -say- more than an hour (or 20 minutes, or ???) previously it would write a new record in the ‘lastsynch’ table and then perform a synchronization. Because the forms will open and close during use of the database, I was thinking that the interval between testing the time from that last synch would have to be fairly short, or it would never be tested before the form closed (I assume that opening the form re-sets the timer). If it is possible to attach the on-timer event to the database as a whole rather than to particular forms then the arrangement can be simplified; rather than recording the last synch and testing the elapsed time the routine can simply synchronize every -say- 20 or 30 minutes.

The other alternative is to design a union query in Access that does the union you want, then a make-table query in Access that just takes the union query you just wrote in the query grid and the “*” for all fields and creates a new table – call this table “NewTable” and the query qryMkNewTable. You have to do this two-step approach since you can’t write a “Union Make Table” query (or maybe YOU can – I can’t ).

In Excel you open the querydefs collection and execute qryMkNewTable, and then run the parameter query against NewTable

Bill:

If you are looking for a good book on Excel and VBA you might check out John Walkenbach’s “Excel 2000: Power Programming with VBA” I have five or six Excel reference books, but that is the one I use most often. The discussion of topics is thorough and complete and the topic sequence is logical. There is some introductory user-oriented stuff at the start and after that it assumes a pretty good level of expertise, but is quite accessible for anyone who has been ‘hands-on’ with Excel for a while.

At the moment, I just wish John had written a similar book on Access!

Charlotte – I think I raised some red herrings in my previous messages. I am able to pop up the forms I want with some default values set. The subforms are displaying the right data. My question was more theoretical.

I can write a query that returns a number of records and use that query (or a base table) to supply data to a subform. Since I want the subform to only display records related to the currently active record on the main form, I would normally impose a Parent / Child link relationship so the subform is appropriately restricted.

Alternatively, I can write the query with a criterion set to match the current value of a control on the main form. In this case the query results are already restricted to those related to the active record on the main form, and I can feed results of the query to the subform without establishing the Parent / Child link. I am not sure if doing that will be faster or slower (or no different) than having an unrestrained query with the P/C link on the form. I am also not sure if one approach is inherently more robust than the other.

If the first approach is better, I would like to count (and display) the number of records in the query (or table) with a value in a particular field that match the value of the control in the main form, because the subform only displays a couple of records out of what may be a large number and my users have complained that they don’t know (in this case) how many projects a particular developer has submitted for consideration. I have patiently explained to them that this is a feature not a bug – but they complain anyhow.

I was wrestling with

Dcount(“*”, “qrydevelopProposal” , “[qryDevelopProposal].[company] = ” & forms!frmDeveloper!CompanyName)

and various iterations, but got nowhere.

Boy – are you trying to get LOGICAL on me?

It’s a good question, though. The subform reports insurance policies in force for projects we have dealing with, but only displays some of the information – the carrier and the expiry date as a quick “heads up” that there is a policy in force.

We actually want to track a lot more – the policy limits, date issued, date in force, broker, etc. The idea is that people will be looking at the main form with the subform in it and that will trigger them to enter any new policy documents received, or they will see that the last policy has expired and chase it with the appropriate owner – and then enter the details when available. When opening the data-entry form it defaults to in-force and expiry dates being one year after the most recent policy, coverage amount being the same, and so on….

The University of Bristol has a pretty good set of Access on-line tutorials to teach students. In particular for the Access dialect of SQL I would try:

http://www.cse.bris.ac.uk/~ccmjs/acc97-r5.htm%5B/url%5D

…but the rest of the materials are worth looking at, too. The document referenced uses an example database that you can also download. Good Luck!

Canadian mortgages are traditionally compounded semi-annually. There used to be a requirement in the Bank Act that interest could only be “compounded semi-annually, not in advance” – I *think* that requirement is gone now, but the tradition lives on.

On a blended (re)payment loan, every payment creates a new implicit compounding period. The problem is that when you set up a loan with a face rate of 12% by charging 1% per month the effective rate is ~13% (1% compounded 12 times = (1+1%)^12-1 = 12.683%). A Canadian mortgage with a face rate of 12% woudl have an effective annual rate of 12.36% (1+6%)^2-1 = 12.36%. The required monthly charge is the twelfth root of that rate or (1.236)^(1/12)-1 = 0.976%. Since the term “12.36%” was determined by compounding the (half-yearly) nominal rate twice, the equivalent is to take the sixth root (representing half a year or six months) of the nominal semi-annual rate, or (1+6%)^(1/6)-1.

If you charge a monthly rate (assuming monthly payments) equal to the sixth-root of one-half the nominal annual rate you will have the correct Canadian monthly interest factor – don’t forget that the mortgage will likely be paid in advance, so the monthy payments will be (very slightly) smaller than if you calculate them as payments in arrears. Drop me an e-mail or post your s/sheet if you want more clarification.