iam facing a problem ,i want to add a form to my project from another project,when i add the form and try to run it give me error,i dont know what is the problem i have added its module and database also but still giving me (ERROR:Run time error 3709 ,The connection cannot be use to perform this operation,Either it is closed or invalid..in this context.).
here is the form which i want to add ,iam mentioning the error..also..

Dim RsCr As New ADODB.Recordset
Dim RsDr As New ADODB.Recordset

Private Sub setGridStatusCr()
    With grdCredit
        .Clear
        .Rows = 1
        .Cols = 3
    
        .TextMatrix(0, 0) = "Date"
        .ColWidth(0) = 1190
        
        .TextMatrix(0, 1) = "Particulars"
        .ColWidth(1) = 3485
        
        .TextMatrix(0, 2) = "Amount"
        .ColWidth(2) = 1445
    End With
End Sub

Private Sub displayCr()
    Call setGridStatusCr
    Dim X As Integer
    With grdCredit
        While Not RsCr.EOF
            X = X + 1
            .Rows = .Rows + 1
            
            .TextMatrix(X, 0) = Format(RsCr.Fields("TDate"), "dd-mmm-yyyy")
            .TextMatrix(X, 1) = RsCr.Fields("TDescription")
            .TextMatrix(X, 2) = IIf(RsCr.Fields("TAmount") <> 0, Format(RsCr.Fields("TAmount"), "#,##0.00"), "-")
  
            RsCr.MoveNext
        Wend
        RsCr.Close
    End With
End Sub

Public Sub FormtGrid()
    ff$ = "^Date|<Particulars|>Amount"
    grdCredit.FormatString = ff$
    grdDebit.FormatString = ff$
End Sub


Private Sub CmdAbout_Click()
    frmAbout.Show
    frmCashBook.Enabled = False
End Sub

Private Sub CmdDelete_Click()
    Dim Rsec As String
    If MsgBox("You cannot recover deleted data" & vbCrLf & vbCrLf & "Are you sure you want to Delete All Data", vbExclamation + vbYesNoCancel + vbDefaultButton2) = vbYes Then
    Rsec = InputBox("You must enter password to delete data", "Security")
    If Rsec = "CashBook" Then
    GConn.Execute "Delete * from Cashbook"
    MsgBox "All data deleted successfully", vbInformation
    Form_Load
    Else
    MsgBox "Invalid Password" & vbCrLf & vbCrLf & "You cannot delete data", vbExclamation
    End If
    End If
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then SendKeys "{tab}"
End Sub

Private Sub Form_Load()
    FormtGrid
    If RsCr.State = 1 Then RsCr.Close
    RsCr.Open "Select * from CashBook where TType='Credit'", GConn, 3, 4 >>>>>>>>>>ERROR
    displayCr
    If RsDr.State = 1 Then RsDr.Close
    RsDr.Open "Select * from CashBook where TType='Debit'", GConn, 3, 4
    displayDr
    GetTotal
End Sub

Private Sub setGridStatusDr()
    With grdDebit
        .Clear
        .Rows = 1
        .Cols = 3
    
        .TextMatrix(0, 0) = "Date"
        .ColWidth(0) = 1190
        
        .TextMatrix(0, 1) = "Particulars"
        .ColWidth(1) = 3485
        
        .TextMatrix(0, 2) = "Amount"
        .ColWidth(2) = 1445
    End With
End Sub

Private Sub displayDr()
    Call setGridStatusDr
    Dim X As Integer
    With grdDebit
        While Not RsDr.EOF
            X = X + 1
            .Rows = .Rows + 1
            
            .TextMatrix(X, 0) = Format(RsDr.Fields("TDate"), "dd-mmm-yyyy")
            .TextMatrix(X, 1) = RsDr.Fields("TDescription")
            .TextMatrix(X, 2) = IIf(RsDr.Fields("TAmount") <> 0, Format(RsDr.Fields("TAmount"), "#,##0.00"), "-")
            
            RsDr.MoveNext
        Wend
        RsDr.Close
    End With
End Sub

Private Sub TxtCAmount_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
    If TxtCDate.Text = "__/__/____" Or TxtCParticulars.Text = "" Or TxtCAmount.Text = "" Then
    MsgBox "Please enter required data", vbInformation
    TxtCDate.SetFocus
    Else
    Dim RsGC As New ADODB.Recordset
    If RsGC.State = 1 Then RsGC.Close
    RsGC.Open "Select Max(SrNO) From CashBook", GConn, adOpenKeyset, adLockReadOnly
    If RsGC.EOF = False Then
        TxtCSrNo.Text = IIf(IsNull(RsGC(0)), 1, RsGC(0) + 1)
    End If
    STR1 = "Insert Into CashBook(SrNo,TType,TDate,TDescription,TAmount) values(" & Val(TxtCSrNo.Text) & ",'Credit','" & TxtCDate.Text & "','" & TxtCParticulars.Text & "','" & TxtCAmount.Text & "')"
    GConn.Execute STR1
    BlankFields
    GetTotal
    Form_Load
    End If
    End If
End Sub

Public Sub BlankFields()
    TxtCDate.Text = "__/__/____"
    TxtDDate.Text = "__/__/____"
    TxtCParticulars.Text = ""
    TxtDParticulars.Text = ""
    TxtCAmount.Text = ""
    TxtDAmount.Text = ""
End Sub

Private Sub TxtDAmount_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
    If TxtDDate.Text = "__/__/____" Or TxtDParticulars.Text = "" Or TxtDAmount.Text = "" Then
    MsgBox "Please enter required data", vbInformation
    TxtDDate.SetFocus
    Else
    Dim RsGD As New ADODB.Recordset
    If RsGD.State = 1 Then RsGD.Close
    RsGD.Open "Select Max(SrNO) From CashBook", GConn, adOpenKeyset, adLockReadOnly
    If RsGD.EOF = False Then
        TxtCSrNo.Text = IIf(IsNull(RsGD(0)), 1, RsGD(0) + 1)
    End If
    STR2 = "Insert Into CashBook(SrNo,TType,TDate,TDescription,TAmount) values(" & Val(TxtCSrNo.Text) & ",'Debit','" & TxtDDate.Text & "','" & TxtDParticulars.Text & "','" & TxtDAmount.Text & "')"
    GConn.Execute STR2
    BlankFields
    GetTotal
    Form_Load
    End If
    End If
End Sub

Public Sub GetCrTotal()
    Dim CrT As New ADODB.Recordset
    If CrT.State = 1 Then CrT.Close
    CrT.Open "Select sum(TAmount) as CRTotal from CashBook where TType='Credit'", GConn, 3, 4
    LblCreditTotal.Caption = IIf(IsNull(CrT.Fields("CRTotal").Value), "0.00", CrT.Fields("CRTotal").Value)
End Sub

Public Sub GetDrTotal()
    Dim DrT As New ADODB.Recordset
    If DrT.State = 1 Then DrT.Close
    DrT.Open "Select sum(TAmount) as DRTotal from CashBook where TType='Debit'", GConn, 3, 4
    LblDebitTotal.Caption = IIf(IsNull(DrT.Fields("DRTotal").Value), "0.00", DrT.Fields("DRTotal").Value)
End Sub

Public Sub GetTotal()
    GetCrTotal
    GetDrTotal
    LblTotal.Caption = Format(Val(LblCreditTotal.Caption) - Val(LblDebitTotal.Caption), "#,##0.00")
End Sub

Recommended Answers

All 3 Replies

open first your connection, before executing recordset commands such as Open.

GCon.Open "your connection string here"

and make sure that the GCon is a global variable so that other forms can access it, and also, make sure that your connection has been opened before your Forms perform recordset operations...

I would suggest that, declaring you GCon in a BAS Module, and then set your project's startup object to 'Sub Main'

Public GCon As New ADODB.Connection

Sub Main()
     GCon.Open "you connection string here"

     MainForm.Show   'show your main form, or whatever form you use.
End Sub

Hope this would help you, Good luck. :)

i have tried it ,but it is still giving me error,may be am doing in the wrong way,
can i attach the project? to make u understand what are the issues?

Be sure that in the project properties the main() procedure was set in the startup object so that the connection object opens first before the other forms.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.