Solved

Object Required Error

Posted on 2013-06-01
8
358 Views
Last Modified: 2013-06-01
Hi,

I have attached an excel 'Forecast-Tool' and it has the form named 'LoginFrm' created. When I open this excel the 'LoginFrm' would popup to ask for login credentials. I will give user name as 'Prashanth' and the password is 'X'. Once, I click on Login button then I get the error as 'Object Required' This workbook will be connected to 'ForecastToolDatabase.mdb' file which is also attached.

I am not able to identify where is the problem. It would be of great help if you could do the needful. Thanks for your time and support.

Regards,
Prashanth
Forecast-Tool.xlsm
ForecastToolDatabase.mdb
0
Comment
Question by:pg1533
  • 5
  • 3
8 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39212622
Hi Prashanth,

I changed the modLoginForm code module to read:

Option Explicit                                                             ' *** Added

Public clsADODB                                         As New clsADODB     ' *** Added
Sub btn_Login_Click()
On Error GoTo ErrorHandler
    Dim strUsername As String, strPassword As String
    Dim strQuery As String
    Dim objRSet                                         As Recordset        ' *** Added
    
    If Trim(LoginFrm.LoginFrmUserText.Text) = "" Or Trim(LoginFrm.LoginFrmPassTxt.Text) = "" Then
        MsgBox "Please fill all login credentials", vbExclamation, "Login Fail"
    Else
        'WSLogin.lblError.Caption = ""
        Call clsADODB.ConnecttoDatabase
        Set objRSet = New Recordset                                         ' *** Added
        objRSet.CursorType = adOpenStatic
        objRSet.Open "SELECT UserDetails.UserName, UserDetails.FullName, UserDetails.UserCat, UserDetails.Password FROM UserDetails WHERE (((UserDetails.UserName)='" & Trim(LoginFrm.LoginFrmUserText.Text) & "') AND ((tblUser.Password)='" & Trim(LoginFrm.LoginFrmPassTxt) & "'));", clsADODB.objCon
        If objRSet.RecordCount <> 0 Then
            If Trim(LoginFrm.LoginFrmUserText.Text) = objRSet.Fields("UserName") _
            And Trim(LoginFrm.LoginFrmPassTxt) = objRSet.Fields("Password") Then
                
                MsgBox "Success", vbInformation
                
            Else
            
                MsgBox "Fail", vbCritical
            
            End If
        
        Else
                
            MsgBox "Fail", vbCritical
            
        End If

    End If
    
If clsADODB.objCon.State = adStateOpen Then clsADODB.objCon.Close
Exit Sub
    
ErrorHandler:
        MsgBox Err.Description, vbInformation, "Error Information"
End Sub

Open in new window



I also changed the clsADODB.ConnecttoDatabase() subroutine.

The previous line:
strConn = "ODBC;DBQ=" & ActiveWorkbook.Path & "\ForecastToolDatabase.mdb;Driver={Microsoft Access Driver (*.mdb)}"

Open in new window


Now reads:
strConn = "ODBC;DBQ=" & ThisWorkbook.Path & "\ForecastToolDatabase.mdb;Driver={Microsoft Access Driver (*.mdb)}"

Open in new window


(ActiveWorkbook becomes ThisWorkbook)

The code now progresses to the SQL statement within the modLoginForm.btn_Login_Click() subroutine:

objRSet.Open "SELECT UserDetails.UserName, UserDetails.FullName, UserDetails.UserCat, UserDetails.Password FROM UserDetails WHERE (((UserDetails.UserName)='" & Trim(LoginFrm.LoginFrmUserText.Text) & "') AND ((tblUser.Password)='" & Trim(LoginFrm.LoginFrmPassTxt) & "'));", clsADODB.objCon

Open in new window


However, this fails with the error message:
"[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2."

Do you need this problem resolving or are you capable of looking at this yourself?

BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39212633
PS. The reason for the change that was necessary within the clsADODB.ConnecttoDatabase() subroutine was due to the incomplete advice you were offered here:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28144627.html#a39212381 ]
0
 

Author Comment

by:pg1533
ID: 39212684
It would be of great help and I really appreciate, if you could resolve this problem also. Thank you so much for your time, patience and support.

Regards,
Prashanth
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39212739
Hi again,

Change this line:
objRSet.Open "SELECT UserDetails.UserName, UserDetails.FullName, UserDetails.UserCat, UserDetails.Password FROM UserDetails WHERE (((UserDetails.UserName)='" & Trim(LoginFrm.LoginFrmUserText.Text) & "') AND ((tblUser.Password)='" & Trim(LoginFrm.LoginFrmPassTxt) & "'));", clsADODB.objCon

Open in new window


To read like this:
objRSet.Open "SELECT UserName, FullName, User_Cat, Password FROM UserDetails WHERE UserName='" & Trim(LoginFrm.LoginFrmUserText.Text) & "' AND Password='" & Trim(LoginFrm.LoginFrmPassTxt) & "'", clsADODB.objCon

Open in new window


You should then see the "Success" (information) MessageBox.

BFN,

fp.
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39212744
PS. I would also consider removing the first line from the ConnecttoDatabase() subroutine within the clsADODB class module:

Application.ScreenUpdating = False

Open in new window


Good luck with the rest of your project.
0
 

Author Comment

by:pg1533
ID: 39212912
Hi Fanpages,

You are Genius. Thank you so much for all your support and time. I would continue to work on the project. I would definitely raise another question if I get any doubt.

Once again thank you so much. Take care.

Regards,
Prashanth
0
 

Author Closing Comment

by:pg1533
ID: 39212914
Thank you
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39212932
You are very welcome, Prashanth.

If I spot your further question(s), & nobody else is assisting you, I will try to contribute if I am able to.

BFN,

fp.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question