User Profile

Collapse

Profile Sidebar

Collapse
Petrol
Petrol
Last Activity: 1 week ago
Joined: Oct 13 '16
Location: Brisbane, Queensland, Australia
  •  
  • Time
  • Show
  • Source
Clear All
new posts
  • Petrol
    replied to Using TempVars in field definitions in SQL queries
    in Access
    Thank you, NauticalAgent and NeoPa, for your help.

    NeoPa, thanks for the tip about pasting images; I wasn't aware of the <IMG> tag; I have now used it below.
    NauticalAgent, I ran your untested code and it worked! So we're on the right track.

    Unfortunately, I had oversimplified my query in an attempt to make it usable in my post, and I am struggling to apply your principles to the actual case. I have now generated...
    See more | Go to post

      Leave a comment:

    • Petrol
      started a topic Using TempVars in field definitions in SQL queries
      in Access

      Using TempVars in field definitions in SQL queries

      Is there anything intrinsically wrong in using a TempVar in a field definition of an Access SQL query?
      As a simplified test I have witten a simple query qry_LOI3 thus:
      Code:
       SELECT EventTypes.EventCode, [TempVars]![Drv] AS Expr1
FROM EventTypes;
      The datasheet it generates is shown in the attached screenshot.
      I then try to open it as a recordset, thus:
      Code:
       Public Sub TestProc()
Dim db As Database, rs As Recordset
Set db
      ...
      See more | Go to post
      • Petrol
        replied to g a TempVar in an Access SQL query
        in Access
        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...
        See more | Go to post

          Leave a comment:

        • Petrol
          replied to g a TempVar in an Access SQL query
          in Access
          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.
          See more | Go to post

            Leave a comment:

          • Petrol
            replied to g a TempVar in an Access SQL query
            in Access
            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...
            See more | Go to post

              Leave a comment:

            • Petrol
              replied to g a TempVar in an Access SQL query
              in Access
              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.
              See more | Go to post

                Leave a comment:

              • Petrol
                started a topic g a TempVar in an Access SQL query
                in Access

                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:
                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]));
                and tested...
                See more | Go to post
                • Petrol
                  replied to Outlook automation options
                  in Access
                  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.
                  See more | Go to post

                    Leave a comment:

                  • Petrol
                    started a topic Outlook automation options
                    in Access

                    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?
                    See more | Go to post
                    • Petrol
                      replied to Why doesn't my SetFocus work?
                      in Access
                      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...
                      See more | Go to post

                        Leave a comment:

                      • Petrol
                        replied to Why doesn't my SetFocus work?
                        in Access
                        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,...
                        See more | Go to post

                          Leave a comment:

                        • Petrol
                          started a topic Why doesn't my SetFocus work?
                          in Access

                          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...
                          See more | Go to post
                          • Petrol
                            replied to Compact and Repair causing errors?
                            in Access
                            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...
                            See more | Go to post
                              👍 1

                              Leave a comment:

                            • Petrol
                              replied to Compact and Repair causing errors?
                              in Access
                              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.
                              See more | Go to post

                                Leave a comment:

                              • Petrol
                                replied to How to trigger the Format event for a report section
                                in Access
                                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....
                                See more | Go to post

                                  Leave a comment:

                                • Petrol
                                  replied to Compact and Repair causing errors?
                                  in Access
                                  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.
                                  See more | Go to post

                                    Leave a comment:

                                  • Petrol
                                    replied to Compact and Repair causing errors?
                                    in Access
                                    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,...
                                    See more | Go to post

                                      Leave a comment:

                                    • Petrol
                                      replied to Compact and Repair causing errors?
                                      in Access
                                      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....
                                      See more | Go to post

                                        Leave a comment:

                                      • Petrol
                                        replied to Compact and Repair causing errors?
                                        in Access
                                        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 (`),...
                                        See more | Go to post

                                          Leave a comment:

                                        • Petrol
                                          started a topic Compact and Repair causing errors?
                                          in Access

                                          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...
                                          See more | Go to post
                                          Last edited by NeoPa; Nov 9 '25, 06:29 PM.
                                          No activity results to display
                                          Show More
                                          Working...