coding error

[shaz123]

With the coding below i am trying to input a cylinder number into the tblupdate table, when the cylinder number inputed matches the cylinder number within the tblupdate table the cylinder status is set to returned, however if the cylinder number inputed does not match the cylinder number inputed then a new record should be added to the table with the cylinder number and status of the cylinder. However when running this code it works when the cylinder number matches the cylinder number in the table that record is edited in the table, but when the cylinder number does not match, i get the follwong error "run-time error 3021, no current record". How do i overcome this??


My Code:
Private Sub Command22_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQl As String
Dim String3 As Integer
Dim Stringy4 As Date

Set db = CurrentDb
Stringy3 = InputBox("Are There Any Returns", "Please Scan/Enter Cylinder Serial Number")
Stringy4 = InputBox("Please Enter the Cylinder Return Date")

Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = '" & Stringy3 & "'")


If rs![Cylinder Number] = Stringy3 Then
rs.Edit
rs![R Status] = "Returned"
rs![Date of R Status] = Stringy4
rs.Update

If Stringy3 = rs![Cylinder Number] Then
rs.AddNew
rs![Cylinder Number] = Stringy3
rs!Status = "Returned"
rs!CustNo = Me!CustNo
rs.Update

End If
End If

rs.Close
db.Close

End Sub

 

[rich.barry]

After your Set rs=...... line

If rs.recordcount>0 then
put your block of code for what to do if the cylinder exists
else
put your block of code for what to do if the cylinder does not exist
end if

 

[Uncle Gizmo]

one way of tackling a problem like this is to introduce some code for trapping the error something similar to this:

Private Sub btnStartDate_Click()
On Error GoTo Err_ErrorHandler

'Your Code Here
'Your Code Here
'Your Code Here

Exit_ErrorHandler:
    Exit Sub      

Err_ErrorHandler:

    Select Case Err
        Case 3021 ' no relevant records 
        MsgBox "there is no record for the ID entered " 
         Resume Exit_ErrorHandler
        Case Else
         MsgBox "Error From --- btnStartDate_Click --- Error Number >>>  " & Err.Number & "  <<< Error " & _
         "Description >>  " & Err.Description
    End Select
    
    Resume Exit_ErrorHandler
End Sub      'btnStartDate_Click

 

[shaz123]

Any ideas

Thnxs for your response, I have tried adding

If rs.recordcount>0 then
put your block of code for what to do if the cylinder exists
else
put your block of code for what to do if the cylinder does not exist
end if

To my code, the error message does not appear now, however when i type in a cylinder number which is already in the table, the record is edited and updated. But if i type in a cylinder number not in the table the table isnot updated with this information.


My code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQl As String
Dim String3 As Integer
Dim Stringy4 As Date

Set db = CurrentDb


Stringy3 = InputBox("Are There Any Returns", "Please Scan/Enter Cylinder Serial Number")
Stringy4 = InputBox("Please Enter the Cylinder Return Date")


Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = '" & Stringy3 & "'")

If rs.RecordCount > 0 Then

If rs![Cylinder Number] = Stringy3 Then
rs.Edit
rs![R Status] = "Returned"
rs![Date of R Status] = Stringy4
rs.Update
Else

If Stringy3 = rs![Cylinder Number] Then
rs.AddNew
rs![Cylinder Number] = Stringy3
rs!Status = "Returned"
rs!CustNo = Me!CustNo
rs.Update

End If
End If
End If
rs.Close
db.Close
End Sub

 

[shaz123]

I spotted one error,
"If Stringy3 = rs![Cylinder Number] Then"
which ive changed to
"If Stringy3 <> rs![Cylinder Number] Then"

But it still does not work

 

[rich.barry]

Try this



Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQl As String
Dim String3 As Integer
Dim Stringy4 As Date

Set db = CurrentDb


Stringy3 = InputBox("Are There Any Returns", "Please Scan/Enter Cylinder Serial Number")
Stringy4 = InputBox("Please Enter the Cylinder Return Date")


Set rs = db.OpenRecordset("SELECT * From tbl_Delupdate Where [Cylinder Number] = '" & Stringy3 & "'")

If rs.RecordCount > 0 Then

If rs![Cylinder Number] = Stringy3 Then
rs.Edit
rs![R Status] = "Returned"
rs![Date of R Status] = Stringy4
rs.Update
end if

Else

rs.AddNew
rs![Cylinder Number] = Stringy3
rs!Status = "Returned"
rs!CustNo = Me!CustNo
rs.Update

End If
rs.Close
db.Close
End Sub