conflict between save and search adodb

[cavern]

hello ive problem which is every time i click search(by name) n then the next thing i click save, (after save and update my data), the button previous,next,first,last doenst work, the save button function also doesnt work.

but when i click search(the name), n then the next i click the button button previous,next,first,last and it works. n after that, i click save(update my data) it also works.

pls help me check the coding
here is my coding:

'Save button
Private Sub cmdSave_Click()
On Error Resume Next
rs(0) = cmbReg.Text
rs(1) = txtDate.Text
rs(2) = cmbTitle.Text
rs(3) = txtName.Text

rs.Update
 MsgBox "The record has been saved successfully.", , "ADD"

end sub

'search button
Private Sub cmdSearch_Click()
On Error Resume Next
Dim key As String, str As String
    key = InputBox("Enter Name ")
    Set rs = Nothing
    str = "select * from Owner where Name='" & key & "'"
    
    rs.Open str, adoconn, adOpenForwardOnly, adLockReadOnly
    
    If rs.BOF = True Or rs.EOF = True Then
    MsgBox "No Student found with that name."
    
    Exit Sub
    Else
        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)

     
        
    Set rs = Nothing
    str = "select * from Owner"
    rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
    End If

end sub

'next button
Private Sub cmdNext_Click()
On Error Resume Next

rs.MovePrevious
    If rs.BOF = True Then
        MsgBox "This is the first record.", vbExclamation, "Note it..."
        rs.MoveFirst
    End If
        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)

end sub

'previous button
Private Sub cmdPrevious_Click()
On Error Resume Next

rs.MoveNext
    If rs.EOF = True Then
        MsgBox "This is the last record.", vbExclamation, "Note it..."
        rs.MoveLast
    End If
        
        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)
end sub

'first button
Private Sub cmdFirst_Click()
On Error Resume Next

 rs.MoveFirst

        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)
end sub

'last button
Private Sub cmdLast_Click()
On Error Resume Next
rs.MoveLast
       
        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)
end sub

'delete button
Private Sub cmdDelete_Click()
On Error Resume Next

Dim ans As String, str As String
    ans = MsgBox("Do you really want to delete the current record?", vbExclamation + vbYesNo, "DELETE")
    If ans = vbYes Then
        adoconn.Execute ("delete from Owner where Registrationnumber=" & cmbReg.Text)
        MsgBox ("The record has been deleted successfully.")
        Set rs = Nothing
        str = "select * from Owner"
        rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
        rs.MoveFirst
        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)

end if
end sub

here is my form load coding :

Private Sub Form_Load()
On Error Resume Next

Dim str As String
    Me.Caption = "HOUSE RENTAL MANAGEMENT SYSTEM"
    Set adoconn = Nothing
    adoconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\sistempengurusanrumahsewa.mdb;Persist Security Info=False"
    str = "select * from Owner"
    rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
    rs.MoveFirst
        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)

end sub

pls help me check the coding :(

[jhai_salvador]

First thing is, remove the "On Error resume Next" so you could see error prompts, and then you can come back here and show us the error description.

[cavern]

it shows
run time error '424'
object required

on the search button

'search button
Private Sub cmdSearch_Click()
On Error Resume Next
Dim key As String, str As String
    key = InputBox("Enter Name ")
    Set rs = Nothing
    str = "select * from Owner where Name='" & key & "'"
 
    rs.Open str, adoconn, adOpenForwardOnly, adLockReadOnly
 
    If rs.BOF = True Or rs.EOF = True Then
    MsgBox "No Student found with that name."
 
    Exit Sub
    Else
'error is here
        cmbReg.Text = rs(0)

        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)
 
 
 
    Set rs = Nothing
    str = "select * from Owner"
    rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
    End If
 
end sub

[jhai_salvador]

Please remove all your "On Error Resume Next" and come back here again.

Make sure all your objects (buttons, textboxes, combo boxes, etc..) exist in the form.

[cavern]

yes i've removed all "on error resume next"
the object all existed in the form.

the problem only occurred when i click search->save->previous,next,first,last

it will fine when i click search->previous,next,first,last->save

:(

[jhai_salvador]

What is the error prompt? What line # did it occur?..

[cavern]

after i search and click the save button,
it shows:
Run-time error '-2147217887(88040e21)':
the changes you requested to the table were not successful because they would create duplicate values in the index , primary key, or relationship. change the data in the field or fields contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

[cavern]

okay ive removed the primary key in my database table.

and now when i click delete it shows:
run time error '-2147217900(80040e14)':
syntax error (missing operator) in query expression 'Registrationnumber='.

adoconn.Execute ("delete from Owner where Registrationnumber=" & cmbReg.Text)

[AndreRet]

You have not set a reference to rs or your connection "adoconn"

Dim adoconn As ADODB.Connection
Dim rs As ADODB.RecordSet

Set adoConn = New ADODB.Connection
Set rs = New ADODB.RecordSet

[cavern]

Private Sub cmdDelete_Click()

Dim adoconn As ADODB.Connection
Dim rs As ADODB.Recordset, ans As string, str As string

 
Set adoconn = New ADODB.Connection
Set rs = New ADODB.Recordset

    ans = MsgBox("Do you really want to delete the current record?", vbExclamation + vbYesNo, "DELETE")
    If ans = vbYes Then
        adoconn.Execute ("delete from Owner where Registrationnumber=" & cmbReg.Text)
        MsgBox ("The record has been deleted successfully.")
        Set rs = Nothing
        str = "select * from Owner"
        rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
        rs.MoveFirst
        cmbReg.Text = rs(0)
        txtDate.Text = rs(1)
        cmbTitle.Text = rs(2)
        txtName.Text = rs(3)
        txtNationality.Text = rs(4)
        txtIc.Text = rs(5)
        txtAddress.Text = rs(6)
        txtTel.Text = rs(7)
        txtHp.Text = rs(8)
        txtEmail.Text = rs(9)
        txtAddress1.Text = rs(10)
        txtCity.Text = rs(11)
        txtState.Text = rs(12)
        cmbResidential.Text = rs(13)
        cmbGender.Text = rs(14)
        cmbNumber.Text = rs(15)
        txtRental.Text = rs(16)
        
        
        End If

end sub

sir, it still shows the error.

:(

[AndreRet]

adoconn.Execute ("delete from Owner where Registrationnumber=" & cmbReg.Text)

Change this to -

adoconn.Execute ("delete from Owner where Registrationnumber = " & "'" & cmbReg.Text & "'")

[jhai_salvador]

In your code, you are still using the rs object, but you've destroyed it before it was use using this code.
Set rs = nothing
You must only destroy objects when you are not going to use it anymore.

[AndreRet]

@Jhai, please read his code carefully. He is closing the recordset and then he is opening it again to read from another table!:)

[jhai_salvador]

@andre, so doing something like this will make a good practice?

Set rs = New Adodb.Recordset

Set rs = Nothing

rs.open ....

I think not.

Don't get me wrong because I am just giving advice.

[AndreRet]

Ok, bad day at the office. He used adoconn to execute the delete and not rs... my bad.:)

@Cavern, use the following -

Set adoconn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
    ans = MsgBox("Do you really want to delete the current record?", vbExclamation + vbYesNo, "DELETE")
    If ans = vbYes Then
rs.Open "SELECT * FROM Owner WHERE Registrationnumber = " & "'" & cmbReg.Text & "'", adoconn, adOpenStatic, adLockOptimistic

rs.Delete        
        MsgBox ("The record has been deleted successfully.")
rs.Close        
        str = "select * from Owner"
        rs.Open str, adoconn, adOpenDynamic, adLockPessimistic

[cavern]

thanks. solved.

[AndreRet]

It was a pleasure.:)

Happy coding...