Re: How to return a result of a SQL query within an Event Procedure (&what am I doing wrong here)?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks, I figured out a workaround, see below.

RL


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Text6_Click() 'Some textbox that you want to run the SQL
query when you click on it

Dim TempVar As Variant
Dim Str01 As String
Str01 = Str001 'global string Str001 set to local string Str01

Dim Str02 As String

Str02 = "SELECT Count(" + Str01 + ") AS Expr1 FROM Table1;" 'combines
two strings, Str02 and Str01, as one string, with Str02 having the
query and the parameter being supplied by the Str01 string, which is
what you want, note how the apostrophes are used

TempVar = GetResult(Str02) ' the result of the SQL query, usually a
scalar number for simple queries

Dim TempText As String

TempText = CStr(TempVar) 'convert number to string

Text6.Value = TempText 'output the number, as a string, to your
textbox Text6

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' put this code in your Global area of your [Event Procedure]
namespace, outside any Sub

Dim Str001 As String 'global variable Str001

Public Function GetResult(sSQL As String) As Variant

On Error GoTo err01 'add error trap
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)
If Not rs.EOF Then
GetResult = rs(0)
rs.Close
End If

db.Close

Set rs = Nothing
Set db = Nothing

GoTo line001XYZ 'you need this I've found to avoid running the next
few lines, for some strange reason

err01: Debug.Print "GetResult error: SQL=" & sSQL & ". Error: " & Error
$
'Debug prints to debug window (useful when debugging, after the
program has run)


line001XYZ: 'skips line err01 and the ones after, if no mistake at
GoTo err001

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Jan 1, 5:45 pm, Marshall Barton <marshbar...@xxxxxxxxxx> wrote:

I suggest that you remove the parameter from the query and
just use the DCount function:

Private Sub OUTPUT_Click()
Dim TempVar As Long
TempVar = DCount("*", "Query1", "[some field] = 'a' ")
        . . .

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: Playing AVI and MPEG using MCI
    ... "mciSendStringA" (ByVal lpstrCommand As String, ... Dim mlRet As Long ... Private Sub CenterObject ... If mlRet 0 Then ...
    (microsoft.public.vb.controls)
  • Change this Program
    ... Private sPathFrom As String ... Private Sub enableControl ... Dim oColor As OLE_COLOR ... Dim sPathTo As String ...
    (microsoft.public.vb.general.discussion)
  • Re: Error when running vb app with FlexGrid control
    ... Private Sub cmdNetChange_Click ... On Error GoTo Command1_Click_Error ... Private Sub fnGetData(strParam0 As String, strParam1 As String, FLX As ... Dim rsADOObject As Recordset ...
    (microsoft.public.vb.general.discussion)
  • RE: Multi select List box to filter query for editing
    ... Dim mFilter As String ... ' Complete string for filter to apply to query ...
    (microsoft.public.access.formscoding)
  • Sum in a dynamic query
    ... Private Sub Form_Open ... Dim db As Database, Tbl As TableDef ... Dim db As Database, qd As QueryDef, ctl As Control, s As String, Item As ...
    (comp.databases.ms-access)