CODEWITHSUNDEEP
vbams-access-2010
KraZy
KraZy

Reputation: 87

Run-time error: The value you entered isn't valid for this field

I am trying to update a record using Subform. When I update the first time it updates properly but when I try to update the same record again I am getting the error:

Run-time error '-2147352567 (80020009)': The value you entered isn't valid for this field

The following is the form.

Form

When I click edit, the information from the selected record is populated into the respective text-boxes. Once I update the information and click update, the record gets successfully updated for the first time.

When I try to update the same record again I get the mentioned error.

Error

Here is the VB script that runs on clicking edit.

Private Sub cmdEdit_Click()
    'Check if data exists in the list
    If Not (Me.frmschoolsub.Form.Recordset.EOF And Me.frmschoolsub.Form.Recordset.BOF) Then
        'get data to text box control
        With Me.frmschoolsub.Form.Recordset
            Me.Schooltxt = .Fields("School_Name")
            Me.Desctxt = .Fields("Description")
            Me.Deantxt = .Fields("Dean")
            Me.Adeantxt = .Fields("Associate_Dean")
            'store id of student in tag
            Me.Schooltxt.Tag = .Fields("School_ID")
            'change caption of button to update
            Me.cmdAdd.Caption = "Update"
            Me.cmdEdit.Enabled = False
        End With
    End If
End Sub

When I click on Debug it highlights the following line.

Me.Schooltxt = .Fields("School_Name")

Can you help me in identifying what is the issue here.

Upvotes: 0

Views: 1338

Answers (2)

Cennamanesh
Cennamanesh

Reputation: 1

This error is happening that a form field cannot be referenced to a textbox like you did.

You can do it as below.

With Me.frmschoolsub.Form.Recordset
     Me.Schooltxt = Forms![<<if you have main form  >>]![frmschoolsub].form![School_Name]

Upvotes: 0

KraZy
KraZy

Reputation: 87

I figured that after the each update, I am losing the position of record. I added the following statement after update and Requery 

Me.frmschoolsub.Form.Recordset.MoveFirst

Following is the code snippet.

    Else
        CurrentDb.Execute "Update School " & _
                " SET School_Name ='" & Me.Schooltxt & "'" & _
                ", Description ='" & Me.Desctxt & "'" & _
                ", Dean ='" & Me.Deantxt & "'" & _
                ", Associate_Dean='" & Me.Adeantxt & "'" & _
                "where School_ID=" & Me.Schooltxt.Tag


    End If
    'Clear the Fields
    cmdClr_Click
    'Refresh the table
    frmschoolsub.Form.Requery
    Me.frmschoolsub.Form.Recordset.MoveFirst

This fixed the issue.

Upvotes: 1

Related Questions