Petrol
Last Activity: 3 weeks ago
Joined: Oct 13 '16
Location: Brisbane, Queensland, Australia
-
Thanks, Vetana. That's helpful. -
Is the restriction something that should be brought to Microsoft's attention? If not to fix it, at least to document it to save others wasting a week as I have.Leave a comment:
-
Even that isn't as simple as it may appear, NeoPa, because it still leaves me with the problem of getting the TempVar into the table.
However, I solved the problem by using the TempVar in a wrapper function and calling that in the SQL.
I'm interested in your comment that you generally avoid TempVars. I knew nothing about them until last year, when as you'll recall, somebody (Crystal?) in the SF Bay Area user group suggested using...Leave a comment:
-
Further investigation discloses a surprising and apparently undocumented limitation. It appears that you can use TempVars as criteria in a query ... but only if you open the query from the GUI. If you open it in VBA, you get the "Too few parameters" error (3061).
Is this a known limitation? Is it documented anywhere? I couldn't find any such suggestion in Microsoft Learn.
Instance:...Code:SELECT Walks.WalkNumbe r, Walks.StartDate
Leave a comment:
-
OK, I'll bear that in mind in future. Thanks.Leave a comment:
-
Hmm, new information has just come to light.
I ran the same test (Open from GUI, open in vba as recordset) on the antecedent query, 'qry_LOI', and the vba failed in the same way ('Too few parameters'). So then I repeated the process on the three antecedents of qry_LOI, and one of them failed. It was qry_OfficeBeare r, whose SQL code is
...Code:SELECT Positions.Position_Title, People.[Name_FN-SN] AS Name_FnSn, People.First_name, Positions.Description
Leave a comment:
-
Thanks for the suggestion. I made that change, but unfortunately it didn't help.
A quick scan of my project reveals that I have used the 'CurrentDb.' syntax scores of times, and also the 'Set db=CurrentDb()' syntax in scores of other instances. All of them seem to work well and have been working well for years. While I take your point about best practice for the future, do you think it would be worth the effort of changing all the former...Leave a comment:
-
Unable to open a query as a recordset
I have a query 'qry_LOI2' whose SQL is as follows:(It did have a lot more fields, but I have removed most of them during debugging to try to isolate the problem). It opens successfully by double-clicking it in the the GUI Navigation pane, but when I try to open it as a recordset in VBA it fails with Error 3061...Code:SELECT qry_LOI.*, "Para 5" AS Para5 FROM qry_LOI INNER JOIN LOIPayerText ON qry_LOI.WhoPays = LOIPayerText.Wh oPays;
-
Thanks again, gentlemen.
NeoPa - Point taken, I'll remember that in future - but I did say in the OP "VarType(TempVa rs!CommunityCod e) = 8".
Nauticalagent - I'm not up with the jargon. What's 'PFM' mean?
If anybody comes up with an explanation of why Dynaset was more acceptable than ForwardOnly, let me know - if only for my general education and future understanding. Are there any general principles for when to...Leave a comment:
-
Ok, mystery solved. Well no, the mystery isn't solved, but I've got the thing running. It seems Access took exception to my opening the recordset as dbOpenForwardOn ly. When I changed it to dbOpenDynaset, it all ran smoothly.
Sorry to have troubled you. If anyone can explain why ForwardOnly didn't work I'll be grateful.Leave a comment:
-
Thank you both.
Nauticalagent, yes, I had verified CommunityCode. As you'll see, there is a debug.print in TestSub, and if I remove the WHERE clause in the query the sub runs fine and prints out the values of PersonID (=627) and TempVars!Commun ityCode (=BS). And yes, the query runs fine outside the sub, and results in a datasheet with many rows having different PersonIDs and all with the correct Community code of 'BS'.
I must...Leave a comment:
-
Sure, Nauticalagent. The SQL for TestQuery is the first code segment in the OP above, except that in the process of copying it into PostBytes some spurious spaces get inserted that aren't there in the ooriginal. The query runs without error when the third line (WHERE clause) is removed. I presume the capitalisation in CommunityCode makes no difference.Leave a comment:
-
g a TempVar in an Access SQL query
I was under the impression that TempVars could be used in SQL queries, including in the Criteria field, but this seems not to be the case.
I wrote the following test query:
and tested...Code:SELECT People.PersonID , Board_service_h istory.Communit yCode FROM People INNER JOIN Board_service_h istory ON People.PersonID = Board_service_h istory.PersonID WHERE (((Board_servic e_history.Commu nityCode)=[TempVars]![communitycode]));
-
Oh, I see. I had looked up Outlook.MailIte m, but I didn't see the Properties way down at the bottom of the page! Thank you. I'll try that.Leave a comment:
-
Outlook automation options
When I create an email directly in Outlook, it gives me options to select the email account to use (the 'From' dropdown) and the address to direct replies to ('Delivery options'). I can't find any way to implement these options uning Mailitem.Send. Is it possible? -
Yes, thanks, I've found a soluton. I wasn't particularly happy with it, but ... c'est la vie.
Also, I used the optional argument to suppress the message when called before update. (The message has to be produced in the FixPhoneNum procedure because it is conditioned on the given input - specifically, whether an area code is included or not).
One last mystery: Your comment "Do be aware that when you update the value in the...Leave a comment:
-
Thanks, NeoPa. Yes, I did originally have the code in BeforeUpdate, and of course you are quite right, it works perfectly there for illegal entries; when the FixPhoneNum function returns "Invalid" the BeforeUpdate procedure sets Cancel=True and the focus stays squarely on the same control.
The only problem is that when a valid phone mumber is entered, it doesn't get saved to the record. What is saved is exactly what the user entered,...Leave a comment:
-
Why doesn't my SetFocus work?
I have several forms containing bound controls in which a phone number is to be entered. Because I want the saved text string to include spaces here and there (e.g. 07 1234 5678 for land lines, or 0412 345 678 for mobiles), I have a function which reformats the given text string. Since the control is bound to the requisite control source, the reformatted phone number is automatically stored in the record. The function is called from the AfterUpdate... -
Well, as foreshadowed in Post #8, I have again reconstructed the database by copying all objects in the navigation pane into a fresh empty database, and it now works - that is, I can run a Compact and Repair without getting error messages, and the copy itself opens and runs OK. I can only assume that in the previous copy I inadvertantly copied whatever was causing the error, and this time I didn't. This is rather frustrating (I would prefer to...Leave a comment:
-
I meant I initiate the Decompile and hold Shift down when I enter the password, so that the Autoexec and startup procedure are not executed and are included in the decompile and recompiloe.Leave a comment:
No activity results to display
Leave a comment: