Run Time Error 91

Hi Here2Domin8,

How is UserInfo declared?

bkt

You say you've connected to the DB. Have you also populated the recordset ?

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"

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

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"

Where is UserInfo defined.
What is it?

Sorry, that should have been:

  UserInfo.Recordset.AddNew

did you

Set UserInfo = New Adodb.recordset

??

Where did you open the recordset?

I think Mikal613 might have it with
Set UserInfo = New Adodb.recordset

But according to the code snippet posted by Here2Domin8, UserInfo isn't a recordset itself, its an object that contains a recordset.

ok so

Set Userinfo = CreateObject("Wahtevr")

According to the website that Here2Domin8 mentioned. UserInfo is an ADO control.

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

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

carl,

record source is correct
command type 2
table UserInfo

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

I know the DB info is correct because I can log into the library using my username and password

I just have the problem when I try to register a new user or try to add an entry to the library

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

Has every one given up on this question?

ok one last time please post ALL your code well start from scratch

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

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

Prestaul

UserInfo is the ADO control name and the DB name, not the recordset

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.

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.

access 2000 database:
home_Library.mdb

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

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

carl,

Compiler error "Method or data member not found"

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

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

carl,
Same error 91

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.

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.

Runtime error '3251':

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


highlighted >> .AddNew

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.

Also make sure the locktype is set to adLockOptimistic

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.

go carl

Any error message ?  Have you checked the settings for the ADO control ? Specifically LockType, CursorType and Mode.

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

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.