Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, IN
    Posts
    5

    Lightbulb Unanswered: A SendKeys Alternative

    I used SendKeys in the past to send variable criteria and an {ENTER} to the buffer, and a subsequent query would pick up the selection when it ran. I typically allow the user to select the criteria from a pull-down list (combo box). Often a query is used to select a subset of a table for a report, transfer spreadsheet or just another query function. Lately, the SendKeys has become unreliable and only works “most of the time.” When the query is executed it prompts you for the criteria as though the SendKeys had never been done. Here’s an alternative for this type situation.

    Create a global variable in a Module, and a Public function that returns the global variable:

    Option Compare Database
    Option Explicit
    Public pstrCustomer As String
    _________________________________

    Public Function GetCustomer()
    GetCustomer = pstrCustomer
    End Function

    Typical subform popup allows the user to select criteria (customer name in this example) from a combo box (based on a table or query) then press an OK button. Button code:

    Private Sub Command3_Click()
    Dim strUsrSelection As String

    strUsrSelection = Nz(Me![Combo1], "")
    If strUsrSelection = "" Then
    MsgBox "Select a Customer Name, the pres OK"
    Else
    pstrCustomer = strUsrSelection '< - - set global var

    DoCmd.Close acForm, "sfrmCustSelect"
    DoCmd.OpenReport "rptSelectedCust", acViewPreview
    End If
    End Sub

    The rptSelectedCust is based on a query. In the query design, the function GetCustomer() would be put in the Criteria field for the CustName column. The SQL would look something like this:

    SELECT CustName, CustAddr, CustEtc FROM tblCustomer
    WHERE ((tblCustomer.CustName)=GetCustomer());

    The only exposure I can think of is that if you run the query out of context it will pick up the current value of the global variable. But I always provide a switchboard from which users typically run all the provided functions.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Have you tried using parameters instead of functions?

    I routinely put a variable into an invisible text box on my menu and would use this query:

    SELECT CustName, CustAddr, CustEtc FROM tblCustomer
    WHERE ((tblCustomer.CustName)=[Forms]![Menu]![GetCustomer])

    The variable would have to be set before running the report. I had trouble with send keys years back in a terminal server environment and never went back to them. If you need to open the query in vba, you could do the following:

    dim qdf as querydef, GetCustomer

    GetCustomer = ...

    set qdf = currentdb().querydefs("QueryName")
    qdf![Forms!Menu!GetCustomer] = GetCustomer

    you can then use a qdf.execute to run the query or set rs=qdf.openrecordset and loop through the recordset.

Posting Permissions

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