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...
Petrol
Last Activity: 1 week ago
Joined: Oct 13 '16
Location: Brisbane, Queensland, Australia
-
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:
-
Yes, the user will display the report on screen and then print it - either to PDF for email or to paper - at which time the On Format proc then adds the exrta line of text where necessary. Fortunately the person producing the report on screen doesn't need the full details, but sends the printed report to the person who needs the information with full details....Leave a comment:
-
No, it runs fine with no problems, except when I try C&R. As far as I can tell (and I hven't tested every form and every report) it operates perfectly normally.
Even if I put the Autoxec macro back in (or create a new one, to be precise) the database starts and runs OK. Unless I try C&R.Leave a comment:
-
Thanks for that advice, NeoPa (and others). Re form names - I take that point, and will avouid special characters in future databases. But this one has been in production for 6 or 7 years with about 60 forms and reports with names like nn:xxxxx, and nothing in that area chanegs about the time this C&R problem started, so it would be a massive job to change all the names and all the references to them and all the documentation, and as you said,...Leave a comment:
-
Yes, I've tried that. One of the first things I did was decompile (after starting with Shift to prevent Startup from running) and recompile, which it does without errors.
And if I reinstate Auroexec (with only a RunCode Startup() in it), the database opens and appears to do everything normally. The only issue is when I do a C&R....Leave a comment:
-
Thank you both.
Twinnyfo: When I was creating the database I(and learning about Access as I went) I tried several formats for the form nmes. Several were rejected, but the colon was allowed. support.microso ft.com says that "Names of fields, controls, and objects in Access desktop databases ... Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`),...Leave a comment:
-
Compact and Repair causing errors?
My Access database appears to run correctly, and it recompiles without error. However, if I run a Compact and Repair, when the database reopens I get the error message "Invalid databse object reference" - without, unfortunately, mentioning which object it is referring to. See attached screenshot. Then it says either "The expression contains an ambiguous name" or "The expression you entered has a function name that EmmausDBMS...Last edited by NeoPa; Nov 9 '25, 06:29 PM. -
Wow, that's fascinating! I would never have thought of doing that. I'll try it tomorrow, but I presume that will solve the problem. Thank you.
(Of course, it makes the ability to change the detail.Height setting fairly useless! If all you can do is add or remove empty space, I think you would probaly usually do it in the report design, not dynamically)Leave a comment:
-
So MS isn't interested in advice or suggestions from MVPs?Leave a comment:
No activity results to display
Leave a comment: