inI have been using a procedure call with a procedure to execute the sql
connection steps defined a the getdata procedure but I noticed today when the
cmd_go_click procedure gets to the getdata statement, it terminates when it
should begin running the statements in the getdata procedure. Any ideas why
this would terminate?

Here is a sample:

Public sub cmd_go_click ()
'Get data - run ADO connection module
getdata
end sub

Public Sub getdata()
stcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\itcfile02\FCDAttachment\PMNDocs\PMN.mdb;" 'ACCESS
2000
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strsql = "SELECT AddresseeCity, AddresseeState, Mailingcity,
mailingstate, country, addresseename, addresseeaddress, " & _
"addresseezip, labeldate, labelnumber, mailservice,
typenarcotic, totalpostage, originzip, " & _
"WgtPkginLbs, ozs, flatrate, dimensions FROM " & tbl & " WHERE
" & _
"labeldate =#" & beg & "# and labeldate <=#" & fin & "#;"
cnn.Open stcon
rst.Open strsql, cnn, adOpenForwardOnly, adLockReadOnly
recs = rst.RecordCount
If recs = 0 Then
Exit Sub
MsgBox "A search of the available data returned " & recs & "
record(s)." & vbCrLf & _
"Please run the report again with different selection
criteria.", vbInformation, "Data"
End If
Worksheets("Data").Select
i = 1
For Each fld In rst.Fields
ActiveSheet.Cells(1, i).Value = fld.Name
i = i + 1
Next fld
'On Error Resume Next
Worksheets("Data").Range("A2").CopyFromRecordset rst
cnn.Close
Set rst = Nothing
end sub

put
OPTION EXPLICIT at the start (ie the very firat line) of your code module,
then DIM each variable. use DEBUG/COMPILE to check this

then step (F8) through the code. Check that the connection opens and that if
it does, that the reciordset populates correctly.



"Mike" wrote:

inI have been using a procedure call with a procedure to execute the sql
connection steps defined a the getdata procedure but I noticed today when the
cmd_go_click procedure gets to the getdata statement, it terminates when it
should begin running the statements in the getdata procedure. Any ideas why
this would terminate?

Here is a sample:

Public sub cmd_go_click ()
'Get data - run ADO connection module
getdata
end sub

Public Sub getdata()
stcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\itcfile02\FCDAttachment\PMNDocs\PMN.mdb;" 'ACCESS
2000
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strsql = "SELECT AddresseeCity, AddresseeState, Mailingcity,
mailingstate, country, addresseename, addresseeaddress, " & _
"addresseezip, labeldate, labelnumber, mailservice,
typenarcotic, totalpostage, originzip, " & _
"WgtPkginLbs, ozs, flatrate, dimensions FROM " & tbl & " WHERE
" & _
"labeldate =#" & beg & "# and labeldate <=#" & fin & "#;"
cnn.Open stcon
rst.Open strsql, cnn, adOpenForwardOnly, adLockReadOnly
recs = rst.RecordCount
If recs = 0 Then
Exit Sub
MsgBox "A search of the available data returned " & recs & "
record(s)." & vbCrLf & _
"Please run the report again with different selection
criteria.", vbInformation, "Data"
End If
Worksheets("Data").Select
i = 1
For Each fld In rst.Fields
ActiveSheet.Cells(1, i).Value = fld.Name
i = i + 1
Next fld
'On Error Resume Next
Worksheets("Data").Range("A2").CopyFromRecordset rst
cnn.Close
Set rst = Nothing
end sub

I have option explicit and dim my variables at the beginning.

"Patrick Molloy" wrote:

put
OPTION EXPLICIT at the start (ie the very firat line) of your code module,
then DIM each variable. use DEBUG/COMPILE to check this

then step (F8) through the code. Check that the connection opens and that if
it does, that the reciordset populates correctly.



"Mike" wrote:

inI have been using a procedure call with a procedure to execute the sql
connection steps defined a the getdata procedure but I noticed today when the
cmd_go_click procedure gets to the getdata statement, it terminates when it
should begin running the statements in the getdata procedure. Any ideas why
this would terminate?

Here is a sample:

Public sub cmd_go_click ()
'Get data - run ADO connection module
getdata
end sub

Public Sub getdata()
stcon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\itcfile02\FCDAttachment\PMNDocs\PMN.mdb;" 'ACCESS
2000
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strsql = "SELECT AddresseeCity, AddresseeState, Mailingcity,
mailingstate, country, addresseename, addresseeaddress, " & _
"addresseezip, labeldate, labelnumber, mailservice,
typenarcotic, totalpostage, originzip, " & _
"WgtPkginLbs, ozs, flatrate, dimensions FROM " & tbl & " WHERE
" & _
"labeldate =#" & beg & "# and labeldate <=#" & fin & "#;"
cnn.Open stcon
rst.Open strsql, cnn, adOpenForwardOnly, adLockReadOnly
recs = rst.RecordCount
If recs = 0 Then
Exit Sub
MsgBox "A search of the available data returned " & recs & "
record(s)." & vbCrLf & _
"Please run the report again with different selection
criteria.", vbInformation, "Data"
End If
Worksheets("Data").Select
i = 1
For Each fld In rst.Fields
ActiveSheet.Cells(1, i).Value = fld.Name
i = i + 1
Next fld
'On Error Resume Next
Worksheets("Data").Range("A2").CopyFromRecordset rst
cnn.Close
Set rst = Nothing
end sub