form/subform combo, I get the run-time error '-2147352567 (80020009)

[mithinxavier]

When I try to edit a record a second time, directly after updating it from the form/subform combo, I get the run-time error '-2147352567 (80020009)

Option Compare Database


Private Sub cmdAdd_Click()
'when we click add button there are tw option
'1. ADD
'2. Update
If Me.txtID.Tag & "" = "" Then
'this is for inserting new
' add data to tabel
CurrentDb.Execute " INSERT INTO student(StudentID,StudentName,Gender,Phone,Address)" & _
" VALUES (" & Me.txtID & ",'" & Me.txtName & "','" & Me.cboGender & "','" & Me.txtPhone & "','" & _
Me.txtAddress & "')"
Else
'otherwise tag of textid stores the id to be modified
CurrentDb.Execute "UPDATE student " & _
" SET StudentID = " & Me.txtID & _
", StudentName = '" & Me.txtName & "'" & _
", Gender = '" & Me.cboGender & "'" & _
", Phone = '" & Me.txtPhone & "'" & _
", Address = '" & Me.txtAddress & "'" & _
" WHERE StudentID = " & Me.txtID.Tag


End If












'clear form
cmdClear_Click
'refresh data in the sub form
formStudentSub.Form.Requery


End Sub


Private Sub cmdClear_Click()
Me.txtID = ""
Me.txtName = ""
Me.cboGender = ""
Me.txtPhone = ""
Me.txtAddress = ""


'focus on ID text box
Me.txtID.SetFocus
'enable edit button
Me.cmdEdit.Enabled = True
'change update button caption to add
Me.cmdAdd.Caption = "Add"
'clear ID tag for reset new
Me.txtID.Tag = ""




End Sub


Private Sub cmdClose_Click()
    DoCmd.Close
End Sub


Private Sub cmdDelete_Click()
' delete record
' check existing record
If Not (Me.formStudentSub.Form.Recordset.EOF And Me.formStudentSub.Form.Recordset.BOF) Then
'confirm delete
        If MsgBox("Confirm Delete", vbYesNo) = vbYes Then
        'delete now
        CurrentDb.Execute " DELETE from student " & _
        " WHERE StudentID= " & Me.formStudentSub.Form.Recordset.Fields("StudentID")
        'refresh data in the sub form
        Me.formStudentSub.Form.Requery
        End If
End If




End Sub


Private Sub cmdEdit_Click()
'check whether data exist in the list
If Not (Me.formStudentSub.Form.Recordset.EOF And Me.formStudentSub.Form.Recordset.BOF) Then


'get data into text box


With Me.formStudentSub.Form.Recordset
Me.txtID = .Fields("StudentID")
Me.txtName = .Fields("StudentName")
Me.cboGender = .Fields("Gender")
Me.txtPhone = .Fields("Phone")
Me.txtAddress = .Fields("Address")


'store id of student in tag of student id incase of od change
Me.txtID.Tag = .Fields("StudentID")


'change caption of add button to update
Me.cmdAdd.Caption = "Update"


'disable edit button
Me.cmdEdit.Enabled = False


End With
End If




End Sub

[ItsMe]

Maybe one or more of the controls do not have a value. I do not see any data validation before execution of the query. Also, you clear all the controls. Is txtID formatted for numbers? seems like you might be using unbound controls and assigning an empty string to a number field.

[NTC]

Use a form that is bound to the record source, and do not use the code-centric approach.

[mithinxavier]

Maybe one or more of the controls do not have a value. I do not see any data validation before execution of the query. Also, you clear all the controls. Is txtID formatted for numbers? seems like you might be using unbound controls and assigning an empty string to a number field.

but when i edit 1st time it is working. after the error if i close the db and open it again and try editing it will work. This error occur when i try to edit data on 2nd time.Kindly please help me to solve this.. this is only a sample database.. i'am working on another one base on this idea . I'm new to coding and access

[ItsMe]

What happens if you comment out some of the code? Bite off small pieces to determine what works and what does not.

'
''clear form
'cmdClear_Click
''refresh data in the sub form
'formStudentSub.Form.Requery

[mithinxavier]

please find the attached db

[June7]

I looked at your db. Agree with NTC. There is no reason for this form/subform arrangement. Just a single form for data entry/edit. It can be set as Continuous view and data controls arranged to look like datasheet. Then the buttons can be in form header section. All of the code writing record to table would be unnecessary.

[mithinxavier]

actually i'm doing another database based on this idea. i'm new to access and vb can you please make the db working by correcting the code.

[June7]

I am not going to build db for you. Advice has been given to use more conventional approach. Use it or not as you choose. Learn to debug. Refer to link at bottom of my post. What line triggers the error?