SendKeys Alternative 1

[FRrogoy]

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 instead of prompting you. But I always provide a switchboard from which users typically run all the provided functions.


Frank

 

[hneal98]

You could also set up the Global variabel as a property.

Good job.