HELP:Application defined or object

[tatendatiffany]

i am getting the error message in part of my code on the line in bold. any ideas?? so i cab successfully login it but it just doesnt seem to be running the queries because of that one error i thought i had added everything you need.HELP

Sub getresults()
 On Error GoTo errorhandler
  Dim r As Integer
    Dim c As Integer
    Dim str As String
    Dim SQL As String
    Dim rtnVal As String
    Dim WhichWorksheet As String
    Dim NamedRange As String
    Dim fldCount As String
    Dim rs As Recordset
    Dim cn As ADODB.Connection    'Your loop currently picks up the string from the cells and passes it to the Getrecordset function.
                                'It is in that function that you need to create the recordset
    
   Call CheckConnection
   
   Set MFrst = Nothing
    If MFcnn.State = 1 Then ' check connection to database
        For c = 31 To 35
            For r = 2 To 39
                If Cells(r, c).Value <> "" Then
                    str = Cells(r, c).Value
                    rtnVal = getrecordset(str)
                    Cells(r, c - 5).Value = rtnVal
                End If
            Next r
        Next c
        If MFcnn.State = adStateOpen Then MFcnn.Close
    End If
    
    Exit Sub
errorhandler:
    If MsgBox("An error occurred while performing action" & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Do you want to continue?", vbCritical + vbOKCancel, Err.Source) = vbOK Then
      [B]  rtnVal = vbNullString
[/B]        Resume Next
    End If
    If MFcnn.State = adStateOpen Then MFcnn.Close
End Sub

 

[Ruddles]

It's often the case that by trapping the error where it's actually occurring and sending the program off to some unrelated bit of code, the real problem is being masked.

Remove or comment out the On Error GoTo errorhandler directive and re-run the code, and tell us which line program execution stops on and what error number/message it gives..

 

[tatendatiffany]

hie,

Thank you for responding well i have done a set in through the code and i think i know what the problem is in my file there are two pages Index1 and index 2:
index 1 has the sql queries and index 2 has the login button.
so in the sql it prompts login twice so i need to create a login button that somehow runs the sql queries but i dont know how i would do that since its not hard coded

Any ideas??
I could provide more code for context

 

[Ruddles]

I'm afraid you've lost me. When you say "two pages" do you mean two worksheets? Creating a button and assigning a macro to it is fairly simple. What's "not hard coded"?

You didn't tell us which line program execution stops on and what error number/message it gives.

 

[tatendatiffany]

yes two worksheets but i have fixed that now the problem is i am not getting any errors but the sql query results are not being displayed:any ideas why?? it connects to the database when i click button to run the sql queries it just connects but doesnt return anything

Function getrecordset(str) As String
    'create recordset object
    Dim rs As New ADODB.Recordset
    
    If (MFcnn Is Nothing) Then
        MsgBox "Connection not open"
        LogStr = LogStr & "connection is nothing" & vbCrLf
    End If
    If (MFcnn.State = adStateClosed) Then
        MsgBox "Connection not open"
        LogStr = LogStr & "connection not opened" & vbCrLf
    End If
    
    rs.ActiveConnection = MFcnn
    
    If rs.State = adStateOpen Then rs.Close
    'using the str variable open the recordset
    rs.Source = str
    rs.Open
    'if recordset NOT NULL
    If Not (rs.BOF And rs.EOF) Then        'stores the sql query results
        getrecordset = rs(0)
        'getrecordset = Recordset
    Else
        'getrecordset = "No Result"
        getrecordset = vbNullString
    End If
                                'close and destroy recordset object
    If rs.State = adStateOpen Then rs.Close
    Set rs = Nothing
End Function
Sub getresults()
 On Error GoTo errorhandler
  Dim r As Integer
    Dim c As Integer
    Dim str As String
    Dim SQL As String
    Dim rtnVal As String
    Dim WhichWorksheet As String
    Dim NamedRange As String
    Dim fldCount As String
    Dim rs As Recordset
    Dim cn As ADODB.Connection    'Your loop currently picks up the string from the cells and passes it to the Getrecordset function.
                                'It is in that function that you need to create the recordset
    
    Call CheckConnection
   
    Set MFrst = Nothing
    If MFcnn.State = 1 Then ' check connection to database
        For c = 31 To 35
            For r = 2 To 39
                If Cells(r, c).Value <> "" Then
                    LogStr = LogStr & "------------------------------" & vbCrLf
                    LogStr = LogStr & " Row " & r & " Col " & c & vbCrLf
                    LogStr = LogStr & "------------------------------" & vbCrLf
                    str = Cells(r, c).Value
                    LogStr = LogStr & str & vbCrLf
                    rtnVal = getrecordset(str)
                    LogStr = LogStr & rtnVal & vbCrLf
                    Cells(r, c - 5).Value = rtnVal
                End If
            Next r
        Next c
        If MFcnn.State = adStateOpen Then MFcnn.Close
    Else
        MsgBox "Connection not open. Skipping all queries", vbExclamation
    End If
    
    GoTo ExitSub
errorhandler:
    LogStr = LogStr & "    ERROR OCCURRED :      " & Err.Description & vbCrLf
    If MsgBox("An error occuredd while performing action" & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Do you want to continue?", vbCritical + vbOKCancel, Err.Source) = vbOK Then
        rtnVal = vbNullString
        Resume Next
    End If
    If MFcnn.State = adStateOpen Then MFcnn.Close
    
ExitSub:
    
    Open ThisWorkbook.Path & IIf(Right(ThisWorkbook.Path, 1) = "\", "", "\") & "LOG.FILE" For Append As #99
    Print #99, "***********************************************************"
    Print #99, "****   LOG STATED AT " & Now & "   ********"
    Print #99, "***********************************************************"
    Print #99, "Conn Str : " & MFcnn.ConnectionString & vbCrLf
    Print #99, "***********************************************************"
    Print #99, LogStr
    Close #99
End Sub
Static Sub CheckConnection()
    MsgBox "Establishing connection"
    If MFcnn.State = 0 Then
        If Range("User").Value = "" Or Range("PWord").Value = "" Or Range("SavedEnv").Value = "" Then
            Login.Show
        Else
            Call ConnectToDB(Range("User").Value, Range("PWord").Value, Range("SavedEnv").Value)
        End If
    Else
    End If
    If MFcnn.State = adStateOpen Then
        MsgBox "Connection Established successfully!"
    End If
    
    Exit Sub
    
fErr:
Debug.Print Err.Description     'checks users login and stores the details'
If Range("CredsSaved").Value = "1" Then
    Call ConnectToDB(Range("User").Value, Range("PWord").Value, Range("SaveEnv").Value)
Else
    Login.Show
End If

End Sub

 

[Ruddles]

After the rs.Open, insert this:-

Debug.Print chr(34) & str & chr(34)
MsgBox rs.RecordCount & " records in recordset"

That will print the SQL string to the Immediate window (Ctrl-G) and pop up a message box telling you whether you managed to select any data.

Post the SQL here.

 

[tatendatiffany]

hi,

Thanks Ruddles for your response so i have added your code like you said and it is still not running or displaying anything and i cant find any error

 

[Ruddles]

Did it not print the contents of the variable str in the Immediate window (Ctrl-G)?

What is in str?

 

[tatendatiffany]

str is the string that contains the sql queries so it is going to call the sql queries on each and every line in the worksheet.

Can i also ask a quick question if i want to change, so it counts instead of having it fixed because rows can be added or deleted??

     For c = 31 To 35
            For r = 2 To 39

 

[Ruddles]

str is the string that contains the sql queries so it is going to call the sql queries on each and every line in the worksheet.

Yes, I realise that. I meant: what's in the SQL query? What is the actual query?

Can i also ask a quick question if i want to change, so it counts instead of having it fixed because rows can be added or deleted??

The VBA would need to work out which was the first and last row and the first and last column. Imagine someone miles away is looking at your worksheet on their computer and you're trying to explain to them over the telephone how to identify the first and last row and the first and last column. How would you do that? What would you say?

Before you can program the VBA to do anything, you have to be able to express it in words.