Solved

Run Time Error 91

Posted on 2004-08-19
43
330 Views
Last Modified: 2008-01-16
I am trying to write a simple database program. My problem is when one of the forms loads I get a run time error 91. Here is a copy of the code from this form. Debug highlights my error at UserInfo.Recordset.AddNew. I tried removing that line of code and get the same error code, only then it points to UserInfo.Recordset.Fields("username") = txtName.Text. I have a connection to the DB "UserInfo" and the field names are correct. Any help would be greatly appreciated.

----------------------------------------------------------
Option Explicit

Private Sub cmdCancel_Click()
End
End Sub

Private Sub cmdClear_Click()
txtName.Text = ""
txtPassword.Text = ""
End Sub

Private Sub cmdConfirm_Click()
UserInfo.Recordset.Fields("username") = txtName.Text
UserInfo.Recordset.Fields("password") = txtPassword.Text
UserInfo.Recordset.Update
Register.Hide
Login_form.Show
End Sub



Private Sub Form_Load()
UserInfo.Recordset.AddNew
End Sub

0
Comment
Question by:Here2Domin8
  • 18
  • 11
  • 5
  • +3
43 Comments
 
LVL 6

Expert Comment

by:bkthompson2112
ID: 11841949
Hi Here2Domin8,

How is UserInfo declared?

bkt
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11842863
You say you've connected to the DB. Have you also populated the recordset ?
0
 

Author Comment

by:Here2Domin8
ID: 11842876
Hi bkt,

UserInfo is the ADO controller name, it is not declared anywhee in the code. This is a program I found on the web and am trying to get working so I can modify it for my own needs. Here is where I found it: <a href="http://www.vbtutor.net/lesson22.html">here</a>
I tried adding the line
Dim UserInfo As New ADODB.Connection
but then I get a compiler error saying "Member already exists in a object module from which this object module derives"
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11842886
Also you may want to try moving your AddNew to the start of cmdConfirm_Click rather than having it in the Load event;

i.e.

Private Sub cmdConfirm_Click()
UserInfo.AddNew
UserInfo.Recordset.Fields("username") = txtName.Text
UserInfo.Recordset.Fields("password") = txtPassword.Text
UserInfo.Recordset.Update
Register.Hide
Login_form.Show
End Sub
0
 

Author Comment

by:Here2Domin8
ID: 11842939
carl,

moving AddNew allows the form to load but when I click confirm I get a compiler error saying "Method or data member not found"
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11843009
Where is UserInfo defined.
What is it?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11843213
Sorry, that should have been:

  UserInfo.Recordset.AddNew
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 11843276
did you

Set UserInfo = New Adodb.recordset

??
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 11843282
Where did you open the recordset?
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11843374
I think Mikal613 might have it with
Set UserInfo = New Adodb.recordset
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11843584
But according to the code snippet posted by Here2Domin8, UserInfo isn't a recordset itself, its an object that contains a recordset.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 11843605
ok so

Set Userinfo = CreateObject("Wahtevr")
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11843624
According to the website that Here2Domin8 mentioned. UserInfo is an ADO control.

Here2Domin8 have you set the "recordsource" property on the ADO Control ?
0
 

Author Comment

by:Here2Domin8
ID: 11843641
moving UserInfo.Recordset.AddNew to cmdConfirm produces same error 91 when clicked

Set UserInfo = New Adodb.Recordset produces a run time error'438': "object does not support this property or method"

I get the same message when I attempt to adda new record to the library from the library form which I can get into from the login form with no problems now that I moved AddNew to the cmdConfirm_Click
0
 

Author Comment

by:Here2Domin8
ID: 11843656
carl,

record source is correct
command type 2
table UserInfo
0
 
LVL 18

Expert Comment

by:JR2003
ID: 11843675
What is UserInfo?
You need a connection object and a recordset. That's all

Public MyDb As ADODB.Connection
Dim MySet as ADODB.Recodset

set MyDb = New ADODB.Connection
set MySet = New ADODB.Recodset

0
 

Author Comment

by:Here2Domin8
ID: 11843686
I know the DB info is correct because I can log into the library using my username and password
0
 

Author Comment

by:Here2Domin8
ID: 11843734
I just have the problem when I try to register a new user or try to add an entry to the library
0
 

Author Comment

by:Here2Domin8
ID: 11843812
JR2003,

I added
Public MyDb As ADODB.Connection
Dim MySet as ADODB.Recodset

set MyDb = New ADODB.Connection
set MySet = New ADODB.Recodset
but still get same error
0
 

Author Comment

by:Here2Domin8
ID: 11844031
Has every one given up on this question?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 11844066
ok one last time please post ALL your code well start from scratch
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:Prestaul
ID: 11844090
The problem is that there is no recordset method or property for a recordset object!

Just remove the word "recordset" from everywhere that it occurs in that form's code.  I am assuming that the problem is that the code you looked at had a recordset object named recordset and you have one named UserInfo.  What you want is:

Private Sub cmdConfirm_Click()
UserInfo.AddNew
UserInfo.Fields("username") = txtName.Text
UserInfo.Fields("password") = txtPassword.Text
UserInfo.Update
Register.Hide
Login_form.Show
End Sub
0
 

Author Comment

by:Here2Domin8
ID: 11844136
form name: main_menu
Option Explicit

Private Sub cmdCancel_Click()
End
End Sub

Private Sub cmdLogin_Click()
main_menu.Hide
Login_form.Show
End Sub

Private Sub cmdRegister_Click()
main_menu.Hide
Register.Show
End Sub

-----------------------------------------------------------------------------------------------------------------
form name: Register
Option Explicit
Public MyDb As ADODB.Connection
Dim MySet As ADODB.Recordset

Private Sub cmdCancel_Click()
End
End Sub

Private Sub cmdClear_Click()
txtName.Text = ""
txtPassword.Text = ""
End Sub

Private Sub cmdConfirm_Click()
Set MyDb = New ADODB.Connection
Set MySet = New ADODB.Recordset
UserInfo.Recordset.AddNew
UserInfo.Recordset.Fields("username") = txtName.Text
UserInfo.Recordset.Fields("password") = txtPassword.Text
UserInfo.Recordset.Update
Register.Hide
Login_form.Show
End Sub

-----------------------------------------------------------------------------------------------------------------
form name: Login_form
Option Explicit
Private Sub cmdLogin_Click()

Dim usrname As String
Dim psword As String
Dim usernam As String
Dim pssword As String
Dim Msg As String


Register.UserInfo.Refresh
usrname = txtName.Text
psword = txtPassword.Text


Do Until Register.UserInfo.Recordset.EOF
If Register.UserInfo.Recordset.Fields("username").Value = usrname And Register.UserInfo.Recordset.Fields("password").Value = psword Then
Login_form.Hide
frmLibrary.Show
Exit Sub

Else
Register.UserInfo.Recordset.MoveNext
End If

Loop

Msg = MsgBox("Invalid user name or password, try again!", vbOKCancel)
If (Msg = 1) Then
Login_form.Show
txtName.Text = ""
txtPassword = ""
txtName.SetFocus

Else
End
End If


End Sub

------------------------------------------------------------------------------------------------------------------
form name: frmLibrary
Option Explicit
Private Sub cmdCancel_Click()
txtTitle.Text = ""
txtAuthor.Text = ""
txtPublisher.Text = ""
txtYear.Text = ""
txtCategory.Text = ""
End Sub

Private Sub cmdDelete_Click()
Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If Confirm = vbYes Then
adoLibrary.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If

End Sub

Private Sub cmdExit_Click()
End
End Sub

Private Sub cmdNew_Click()
adoLibrary.Recordset.AddNew

End Sub

Private Sub cmdNext_Click()
If Not adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MoveNext
If adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MoveFirst
End If
End If
End Sub

Private Sub cmdPrevious_Click()
If Not adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MovePrevious
If adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MoveLast
End If
End If
End Sub

Private Sub cmdSave_Click()

adoLibrary.Recordset.Fields("Title").Value = txtTitle.Text
adoLibrary.Recordset.Fields("Author").Value = txtAuthor.Text
adoLibrary.Recordset.Update

End Sub

------------------------------------------------------------------------------------------------------------------

That is ALL the code... I set it up exact according to the website http://www.vbtutor.net/lesson22.html
0
 

Author Comment

by:Here2Domin8
ID: 11844181
Prestaul

UserInfo is the ADO control name and the DB name, not the recordset
0
 
LVL 4

Expert Comment

by:Prestaul
ID: 11844395
I'm sorry about that misunderstanding.  I can see that the same issue was discussed above before I posted.  I would again agree with JR2003 that you would probably do best to just replace the the ADO control with ADO recordsets and connections.  If you need to see how this is done then there are many tutorials and examples on the web.

If you posted the database information (type, filename, tables, fields) it only looks like two tables with only a few fields, then I'm sure that we could help with the connection string and code modification.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11844494
Just as a little test, change your click event code to the following:

Private Sub cmdConfirm_Click()
    UserInfo.MoveFirst
    MsgBox "username: " & UserInfo.Recordset.Fields("username")
    MsgBox "password: " & UserInfo.Recordset.Fields("password")
    Register.Hide
    Login_form.Show
End Sub


This will at least verify that your connection to the DB is ok and that you can read from the tables.
0
 

Author Comment

by:Here2Domin8
ID: 11844497
access 2000 database:
home_Library.mdb

tables:
Library
        fields:
        Title
        Author
        Publisher
        Year
        Category
UserInfo
        fields:
        username
        password

0
 
LVL 6

Expert Comment

by:bkthompson2112
ID: 11844517
Unless you've set them in the ADO Data control's properties window,
it looks like you haven't set the ConnectionString or the RecordSource.

See here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconusingadodatacontrol.asp
0
 

Author Comment

by:Here2Domin8
ID: 11844549
carl,

Compiler error "Method or data member not found"
0
 

Author Comment

by:Here2Domin8
ID: 11844581
connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Documents and Settings\ws-its\My Documents\greg's\VB\Final_Prog_Assign - ADO\home_Library.mdb";Persist Security Info=False

recordsource:
command: type 2 - adCmdTable
table: UserInfo
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11844584
Doh !! Missed Recordset again :o)

Should have been:

Private Sub cmdConfirm_Click()
    UserInfo.Recordset.MoveFirst
    MsgBox "username: " & UserInfo.Recordset.Fields("username")
    MsgBox "password: " & UserInfo.Recordset.Fields("password")
    Register.Hide
    Login_form.Show
End Sub
0
 

Author Comment

by:Here2Domin8
ID: 11844625
carl,
Same error 91
0
 

Author Comment

by:Here2Domin8
ID: 11844660
I know I can read from the table because like I said I can login to the library from the login form. Just cant add new user, or new library entry, or view and of the entries in the library, and now apparently I can't view the entries in my UserInfo table either.
0
 
LVL 4

Expert Comment

by:Prestaul
ID: 11844744
Just for kicks and giggles try replacing the click event with this:

Private Sub cmdConfirm_Click()
Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset

    Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Documents and Settings\ws-its\My Documents\greg's\VB\Final_Prog_Assign - ADO\home_Library.mdb;" & _
            "Persist Security Info=False"

    With RS
        .Open "SELECT * FROM UserInfo;", Con
        .AddNew
        !username = txtName.Text
        !password = txtPassword.Text
        .Update
        .Close
    End With
   
    Register.Hide
    Login_form.Show
   
    Con.Close
    Set Con = Nothing
    Set RS = Nothing
End Sub

I think that this is what you are going to want to do all throughout your code.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 125 total points
ID: 11844783
Found the problem!!!  You need to call the Refresh method on the ADODC control first.

So, having said that. Try changing your click event to:

Private Sub cmdConfirm_Click()
    UserInfo.Refresh
    UserInfo.Recordset.AddNew
    UserInfo.Recordset.Fields("username") = txtName.Text
    UserInfo.Recordset.Fields("password") = txtPassword.Text
    UserInfo.Recordset.Update
    Register.Hide
    Login_form.Show
End Sub
0
 

Author Comment

by:Here2Domin8
ID: 11844798
Runtime error '3251':

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.


highlighted >> .AddNew
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11844832
I had a feeling that might come next :o)

The default CursorType for the ADO control is adOpenStatic.  You will need to change it to adOpenDynamic.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11844864
Also make sure the locktype is set to adLockOptimistic
0
 

Author Comment

by:Here2Domin8
ID: 11844873
carl,
Great, that got it.. but I am still having a problems with the library DB.. I can add new records to it but I cannot navigate the records that are there.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 11844894
go carl
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 11844925
Any error message ?  Have you checked the settings for the ADO control ? Specifically LockType, CursorType and Mode.
0
 
LVL 4

Expert Comment

by:Prestaul
ID: 11845117
There is nothing in the code that you posted that would show data for the table as you navigate.  If you are not getting any errors then the code you have is probably fine but you need to add the code to display the values in the text boxes in the Form_Load event and the cmdNext and cmdPrevious click events:

Private Sub Form_Load()
UpdateFields
End Sub

Private Sub cmdNext_Click()
If Not adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MoveNext
If adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MoveFirst
End If
End If

UpdateFields

End Sub

Private Sub cmdPrevious_Click()
If Not adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MovePrevious
If adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MoveLast
End If
End If

UpdateFields

End Sub

Private Sub UpdateFields()
   With adoLibrary.Recordset
      txtTitle.Text = !Title
      txtAuthor.Text = !Author
      txtPublisher.Text = !Publisher
      txtYear.Text = !Year
      txtCategory.Text = !Category
   End With
End Sub
0
 

Author Comment

by:Here2Domin8
ID: 11848713
Thanks everyone for all your help. carl gets the points cuz he answered my initial question. I appreciate all the different insite into the problem though. Keep up the great work.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now