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 :(

Edited 6 Years Ago by cavern: n/a

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.

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

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.

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

:(

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.

Edited 6 Years Ago by cavern: n/a

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)

Edited 6 Years Ago by cavern: n/a

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
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.

:(

Edited 5 Years Ago by cavern: n/a

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

Change this to -

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

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.

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

@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.

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
This question has already been answered. Start a new discussion instead.