[thefifthhjohn]

please help me I'm having error in rs.Open "SELECT * FROM [tbladmin] WHERE [username]= '" & txtUsername.Text & "'", con, adOpenStatic, adLockOptimistic I think my code is right I dunno where I went wrong.

Dim strUserName As String, strPassword As String
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cmdlogin_Click()
OpenCon
rs.Open "SELECT * FROM [tbladmin] WHERE [username]= '" & txtUsername.Text & "'", con, adOpenStatic, adLockOptimistic
        
        If rs.RecordCount <> 0 Then
            If rs![Password] = txtPassword.Text Then
                If rs![user_type] = "Principal" Then
                        
                    MsgBox "The Principal has login", vbOKOnly + vbInformation, "LOGIN"
                    
                        frmMain.Transaction.Visible = True
                        frmMain.Maintenance.Visible = True
                        frmMain.Reports.Visible = True
                        frmMain.Exit.Visible = True
                        frmMain.Others.Visible = True
                        frmMain.Files.Visible = True
                        Login1 = 1
                        'frmMain.Text3 = Text1.Text
                        'frmMain.Text4 = TimeValue(Now)
                    End If
           End If
      End If
End Sub

Attached image(s)

• 

[raziel_]

the error says all. either you dont open your connection or the string for the connection is invalid. if you could show us the code where you open the connection we may give you better help

[thefifthhjohn]

raziel_, on 21 August 2012 - 12:42 PM, said:

the error says all. either you dont open your connection or the string for the connection is invalid. if you could show us the code where you open the connection we may give you better help

Public con As New ADODB.Connection
Public Sub OpenCon()
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\database\enrollment.mdb"
End Sub

[maj3091]

Try checking if the connection is actually opened (think it's the State property).

[BobRodes]

This is a theory, not for sure. Try removing the "New" from your "As New" in your line 1. If it solves your problem, I'll tell you why I think so. In any case, it shouldn't be there, so remove it.

[leixnt]

the code should be like this to run normally

Dim strUserName As String, strPassword As String
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset


Public Sub OpenCon()
    if con.state = 0 then
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\database\enrollment.mdb"
    end if
End Sub

Private Sub cmdlogin_Click()
OpenCon
rs.Open "SELECT * FROM [tbladmin] WHERE [username]= '" & txtUsername.Text & "'", con, adOpenStatic, adLockOptimistic

        If rs.RecordCount <> 0 Then
            If rs![Password] = txtPassword.Text Then
                If rs![user_type] = "Principal" Then
                         
                    MsgBox "The Principal has login", vbOKOnly + vbInformation, "LOGIN"
                        frmMain.Transaction.Visible = True
                        frmMain.Maintenance.Visible = True
                        frmMain.Reports.Visible = True
                        frmMain.Exit.Visible = True
                        frmMain.Others.Visible = True
                        frmMain.Files.Visible = True
                        Login1 = 1
                        'frmMain.Text3 = Text1.Text
                        'frmMain.Text4 = TimeValue(Now)
                    End If
           End If
      End If
End Sub

[BobRodes]

leixnt, how is your code different from the OP's?

[Bertdecode]

Try This.

Dim strUserName As String, strPassword As String
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

 

 

Public Sub OpenCon()
    Set con=new ADODB.Connection
    if con.state = 0 then

    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\database\enrollment.mdb"

    end if

End Sub

 

Private Sub cmdlogin_Click()
Set rs=new ADODB.Recordset
OpenCon

rs.Open "SELECT * FROM [tbladmin] WHERE [username]= '" & txtUsername.Text & "'", con, adOpenStatic, adLockOptimistic

 

        If rs.RecordCount <> 0 Then

            If rs![Password] = txtPassword.Text Then

                If rs![user_type] = "Principal" Then

                          

                    MsgBox "The Principal has login", vbOKOnly + vbInformation, "LOGIN"

                        frmMain.Transaction.Visible = True

                        frmMain.Maintenance.Visible = True

                        frmMain.Reports.Visible = True

                        frmMain.Exit.Visible = True

                        frmMain.Others.Visible = True

                        frmMain.Files.Visible = True

                        Login1 = 1

                        'frmMain.Text3 = Text1.Text

                        'frmMain.Text4 = TimeValue(Now)

                    End If

           End If

      End If

End Sub

[BobRodes]

In other words, do as I suggested, or just copy Bert's code since he was kind enough to do it for you. If that doesn't work, we'll look at something else.