Click event code takes 3 clicks to run 
Author Message
 Click event code takes 3 clicks to run

I built a Query Input form that sends the user input from
the form to a pass-thru query on SQL Server and returns
the dataset to an Excel spreadsheet. The problem I'm
having is that I have to click the command button about
three times to get it to run the query and produce the
output. I would appreciate any help with the following

Private Sub RunQryButton_Click()
Dim C1 As Date, C2 As Date
Dim CID As String, Anl As String
Dim Sheet As Object, xlWrksht As Object
Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef
Dim DateIDAnl As QueryDef
Dim strXLPath As String

'populate start date from ActiveX calendar
C1 = Me.Cal1
'populate end date from ActiveX calendar
C2 = Me.Cal2
'populate client id selected in combo box
CID = Me.cboClientID
'populate analyte selected in combo box
Anl = Me.cboAnalyte

strSQL = "SELECT * FROM Customer WHERE SampDate >= '" & _
C1 & "' and SampDate <= '" & C2 & "' and ClientID = '" & _
CID & "' and Sinonym = '" & Anl & "';"

On Error Resume Next

' Open a database where QueryDef objects can be created.

Set dbsCurrent = OpenDatabase("LabCustomer.mdb")

' Create a pass-through query to retrieve data from a
Microsoft SQL Server database.

Set qdfPassThrough = dbsCurrent.CreateQueryDef

qdfPassThrough.Connect= "ODBC;DSN=STARCustomer;Description=
Starlims Customer
Database;UID=limssuper;PWD=*****;DATABASE=Lab Customer"

qdfPassThrough.SQL = strSQL
qdfPassThrough.ReturnsRecords = True
Set DateIDAnl = qdfPassThrough

' Start Microsoft Excel and create a new Worksheet object.
strXLPath = "c:\temp\LIMS.xls"
Set xlWrksht = CreateObject("Excel.Sheet")
DoCmd.OutputTo acOutputQuery, "DateIDAnl", acFormatXLS,
strXLPath, True

Set Sheet = xlWrksht.workbooks.Open(strXLPath).sheets(1)

' Make Excel visible
xlWrksht.Visible = True

' Delete new pass-through query
dbsCurrent.QueryDefs.Delete "DateIDAnl"

End Sub

Thank you in advance for your help.
Al Komarek

Sun, 19 Sep 2004 04:22:25 GMT  
 [ 1 post ] 

 Relevant Pages 

1. On Click event: no response from first click

2. Click, click, click OL2000

3. Change the state of a toggle button without running the click event

4. Repost: (workaround no good) Change the state of a toggle button without running the click event

5. Run-time error '2185 in checkbox click event

6. Append Query Running from a button click event

7. VB:Running "On Click" Event

8. Adding Click Event code to a button thru VBA

9. Pause Code During Click Event

10. Adding Code to Click Event at Runtime!

Powered by phpBB® Forum Software © phpBB Group