Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Put a shortcut on a Command Button? (2000)

    Hans gave me a great shortcut, the Ctrl+' works great, but I would like to put it into a command button AND add a tab to it. Is that possible, and how do I?
    Second, am doing calculations with dates... need to check if Date2 is past [One month past the end of the month which Date1 shows]. ie.. if a reading was taken on 5 May (Date1)... is the archived date (Date2) after the last day of June?
    Thanks in advance... !!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    You could use the following code, but I haven't tested it exhaustively, so it may have unexpected side-effects (it uses Sendkeys, which is reputedly wacky). I suggest that you test it on non-critical data for a while to see if it does what you want.

    Put the following function in a standard module:

    Function CopyAndTab()
    Select Case Screen.PreviousControl.ControlType
    Case acTextBox, acComboBox, acCheckBox
    Screen.PreviousControl.SetFocus
    SendKeys "^'{tab}"
    End Select
    End Function

    For clarity, the Sendkeys line is<pre>SendKeys "^'{tab}"</pre>

    Call this function from the On Click event of your command button:

    Private Sub cmdCopy_Click()
    CopyAndTab
    End Sub

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    About the date calculations: you can compare Date2 to DateSerial(Year(Date1), Month(Date1) + 2, 0)
    This expression adds 2 to the month of Date1 (so May=5 becomes July=7), then takes the 0th day, i.e. the last day of the previous month (in this example, June). VBA date calculations allow you to do this, even if it may seem invalid - it works in November or December too.

  4. #4
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put a shortcut on a Command Button? (2000)

    Hans, I created a new module and copy/pasted the top segment which you sent... then created a command button and tried to get it to "call that 'function' " ... selected event procedure in the on click slot... then replaced all in that section with the second part you said. Cannot get it to work..... maybe I need just a little more indepth instruction about what goes where and what names to call it, etc.
    Sorry to be so thick-headed.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    OK, so you created a module and copied the function CopyAndTab into it.

    There are (at least) two ways you can let a command button on a form execute this function.

    1. Use an event procedure.

    Let's assume that your command button is named cmdSomething. In your own database, you must use the actual name, of course.

    Open the form in design view.
    Select the command button.
    Activate the Events tab of the Properties window.
    Click in the On Click event.
    Select Event Procedure from the dropdown list.
    Click the builder button - the three dots ... to the right.
    You will be taken to the Visual Basic Editor, with the "skeleton" of the event procedure already created for you:

    Private Sub cmdSomething_Click()

    End Sub

    (with the name of your command button instead of cmdSomething)

    Don't remove this "skeleton". The blinking cursor is in the empty line between Sub ... and End Sub.
    Press Tab once to indent the code you are going to enter.
    Type (or paste) the word CopyAndTab

    The procedure should now look like this:

    Private Sub cmdSomething_Click()
    CopyAndTab
    End Sub

    (with the name of your command button instead of cmdSomething)

    That's it!

    <img src=/w3timages/blueline.gif width=33% height=2>

    2. Use an expression.

    Open the form in design view.
    Select the command button.
    Activate the Events tab of the Properties window.
    Click in the On Click event.
    Clear the contents of the box, if necessary.
    Type =CopyAndTab()
    Press Enter.

    This method is shorter, but it has the disadvantage that you can't see what the command button does in the Visual Basic Editor. If you haven't worked on the database for a while, you might have forgotten that you used an expression. It's not immediately obvious how the command button works then.

  6. #6
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put a shortcut on a Command Button? (2000)

    Hans,
    Tried doing it the first way

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    You created a module that contains the function CopyAndTab. Did you happen to name the module CopyAndTab too? That would explain the first error (and perhaps the second one too). Using the same name for a function (or procedure) and for a module confuses Access.

    If this is the case, activate the Modules tab of the database window, click on the CopyAndTab module, then press F2 to rename it, for example to basCopyAndTab. Many developers prefix the names of database objects with a three letter code (in lower case) indicating the type of the object: tbl for tables. qry for queries, frm for forms, rpt for reports, mcr for macros and bas for modules - bas stands for (Visual) Basic.

    If this is not the case, open your module and check that the function has the correct name.

  8. #8
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put a shortcut on a Command Button? (2000)

    Hans,
    That did it ! Changed the name of the module... worked fine...

    Last Question of the day (from me)... What is the format ... to change the ^'{tab} function to a shift tab function... tried {shift+tab}... didn't work

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    In SendKeys, + stands for Shift, ^ stands for Ctrl and % stands for Alt. I had<pre>"^'{tab}"</pre>

    meaning Ctrl+' followed by Tab. If you want just Shift+Tab, use<pre>"+{tab}"</pre>

    HTH

  10. #10
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put a shortcut on a Command Button? (2000)

    Hans, I have another formatting problem... I think... tried using your date tip... gives me a "Data type mismatch in Criteria expression" error. My two dates are actually called: Jan date collected AND Jan archive/QC date . I am trying to check if the archived date is past the "end of the month... plus a month" date. I tried this expression in the criteria block under the 'Jan archive/QC date' field in the Query:

    >DateSerial(Year([Jan date collected]),Month([Jan date collected])+2,0)

    Did I leave out some quotation marks, or what?

    Cheers.............

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    Are "Jan date collected" and "Jan archive/QC date" both fields of type Date/Time? The expression works OK (with the appropriate field name substituted) in a test query I created.

  12. #12
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put a shortcut on a Command Button? (2000)

    Yes, both of those are fields of the type Date/Time... and I copied that expression directly from the Criteria box under the "Jan date collected" box in my query............ >DateSerial(Year("[Jan date collected]"),Month("[Jan date collected]")+2,0) The program adds the quotation marks when I exit the query... but won't run the query... just gives the same error message.....data mismatch....

    Format on the fields is "Short Date" and Input Mask is "99/99/00;0;_"

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    I assume that you mean <font face="Georgia">under the "Jan archive/QC date" box</font face=georgia> instead of <font face="Georgia">under the "Jan date collected" box</font face=georgia>, but that is not the issue. There should be no quotes around [Jan date collected], and I don't understand why Access would add them. AFAIK, the Format and Input Mask are not relevant here

  14. #14
    New Lounger
    Join Date
    Jun 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put a shortcut on a Command Button? (2000)

    Hans, yes, under the "Jan archive/QC date" box. Still doesn't work... am attaching sample to send. Pulled the 3 files from my database.

    It is the "Jan Out of Limits Query" which is giving me the problems.
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put a shortcut on a Command Button? (2000)

    I'll have a look at it later - have to run now.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •