Run-time Error '-2147352567 (80020009)' The value you entered isn't valid for this fi

[DanielBry]

Dear All,

I am implementing a access program with the update statement based on the access program from
setha.info/docs/ict/msaccess/sourcecode/ict-msaccess-0004[dot]rar
(Please replace [dot with .] as i do not have the rights to post url.)

From the program u click on either row from the subform and click edit. The data will be loaded into the text box. Next change the data e.g address and press update. When I select the same row to edit again I encounter the error "Run-time Error '-2147352567 (80020009)' The value you entered isn't valid for this field.". I have look at the code but have no idea what is causing this. I would appreciate your input and help on how to resolve the following problem.

P.S I am sorry that I did not post my own access program as it contains alot of confidential data. However, the feature I want to implement is the same as the code from the link above.

Thanks!
Best Regards,
Daniel

Edited: I added in the accdb file

 

[sneuberg]

I don't know what a .rar file is. Please zip the database and upload it to this forum.

 

[DanielBry]

Dear Sneuberg,

I have added in the .accdb file. Thanks for any help!

 

[gemma-the-husky]

fwiw, rar is another zip type format. I would have thought winzip would manage a rar, but maybe not.

 

[sneuberg]

The problem is that the frmStudent form doesn't know which record in the subform is selected. You could fix this as I did in the attached database by putting a textbox (could be hidden) in the frmStudent that would contain the selected stdid. Then populate that in the frmStudentSub form in the current event with

Me.Parent.stdid = Me.stdid

With that in place you can change the cmdEdit_Click code to go to that record by changing it as follows:

Private Sub cmdEdit_Click()
    'check whether there exists data in list
    Dim rs As DAO.Recordset
    Set rs = frmStudentSub.Form.RecordsetClone
    rs.FindFirst "stdid = " & Me.stdid
    If Not rs.EOF And Not rs.BOF Then
        'get data to text box control
        With rs
            Me.txtID = .Fields("stdid")
            Me.txtName = .Fields("stdname")
            Me.cboGender = .Fields("gender")
            Me.txtAddress = .Fields("address")
            Me.txtPhone = .Fields("phone")
            'store id of student in Tag of txtID in case id is modified
            Me.txtID.Tag = .Fields("stdid")
            'change caption of button add to Update
            Me.cmdAdd.Caption = "Update"
            'disable button edit
            Me.cmdEdit.Enabled = False
        End With
        rs.Close
    End If
End Sub

But rather than go through all of this I suggest looking at a split form and see if that won't do what you want.

 

[Gasman]

From the program u click on either row from the subform and click edit. The data will be loaded into the text box.

Not sure how you managed that as when I tried that I got an error stating that I could not change the status of a control when it had focus.
This was due to cmdEdit having the focus and the code of cmdEdit trying to disable it.?

 

[DanielBry]

Dear Sneuberg,

Thanks for your help. May I ask is it normal to get the error message Run-Time Error 2452 : "The expression you entered has an invalid reference to the Parent property." when I click on frmStudentSub?

Thanks!

Best Regards,
Daniel

 

[Gasman]

Dear Sneuberg,

Thanks for your help. May I ask is it normal to get the error message Run-Time Error 2452 : "The expression you entered has an invalid reference to the Parent property." when I click on frmStudentSub?

Thanks!

Best Regards,
Daniel

I can click on any control on any of the records of the subform and do not get that error.?

 

[sneuberg]

May I ask is it normal to get the error message Run-Time Error 2452 : "The expression you entered has an invalid reference to the Parent property." when I click on frmStudentSub?

Edit: See next post.

Yes, that the normal operation if you reference the parent form when it's not open which happens during a form load because the subform is loaded first. So to change this behavior I change the code to:

If CurrentProject.AllForms("frmStudent").IsLoaded = False Then
    Me.Parent.stdid = Me.stdid
End If

in the revised attached version.

Did you look at the split forms?

 

[sneuberg]

Oops got that backwards. It should be

If CurrentProject.AllForms("frmStudent").IsLoaded Then
    Me.Parent.stdid = Me.stdid
End If

The revised revised database is attached.

 

[DanielBry]

Not sure how you managed that as when I tried that I got an error stating that I could not change the status of a control when it had focus.
This was due to cmdEdit having the focus and the code of cmdEdit trying to disable it.?

I am sorry I missed your post.
The exact step I do to encounter the error is as follow.
From the program u click on either row from the subform and click edit. The data will be loaded into the text box. Next change the data e.g address and press update. When I select the same row to edit again I encounter the error "Run-time Error '-2147352567 (80020009)' The value you entered isn't valid for this field.".

 

[DanielBry]

Oops got that backwards. It should be

If CurrentProject.AllForms("frmStudent").IsLoaded Then
    Me.Parent.stdid = Me.stdid
End If

The revised revised database is attached.

Hi Sneuberg,

Thanks for your help. I will definitely look at the split form. However, as some of the functionality of my access program is already working I will leave it to my enhance version in the near future. I am sorry as I am not getting the problem to the behavior of having an invalid reference to the Parent property. Is there a way I could get rid of this message completely? I am really sorry, I am still trying to familarise with Access and vb as I had just started learning it 1 week ago.

 

[Gasman]

I am sorry I missed your post.
The exact step I do to encounter the error is as follow.
From the program u click on either row from the subform and click edit. The data will be loaded into the text box. Next change the data e.g address and press update. When I select the same row to edit again I encounter the error "Run-time Error '-2147352567 (80020009)' The value you entered isn't valid for this field.".

My version does not have an update button?
Just Add,Edit,Delete,Clear & Close and as the controld are unbound I cannot update.?
I am puzzled however as how you can get past the setfocus of the Edit Button?

 

[sneuberg]

I don't know how you would be getting the "invalid reference to the Parent property" other than opening the subform by itself. If that the case the only way I know around that is to ignore the error. The attached database has this revised code that does that.

If CurrentProject.AllForms("frmStudent").IsLoaded Then
    On Error Resume Next
    Me.Parent.stdid = Me.stdid
End If

 

[arnelgp]

here is the fix. try to view the code on cmdadd_click event on the later portion of the sub.

 

[sneuberg]

My version does not have an update button?
Just Add,Edit,Delete,Clear & Close and as the controld are unbound I cannot update.?
I am puzzled however as how you can get past the setfocus of the Edit Button?

The Update button appears after you click on the Edit button.

 

[DanielBry]

Dear All,

It is working perfectly now.
Please pardon my ignorance. May I seek help on one more thing. When I change the stdid in the student table from number to text. May I know why does the add, delete and edit button stop working with the error "run time error 3464 data type mismatch in criteria expression text". I know about how type mismatch works in java programming. However, I do not know how to fixed it in vb. May I get some direction on how to resolve it? Thanks for your time!

Best Regards,
Daniel

 

[arnelgp]

it has to do with add/update on the cmdAdd click event:

when adding/updating numeric field, it does not requre to put the new value in quote. while text field needs quote mark:

this is for numeric:

    Dim rs As DAO.Recordset
    If Me.txtID.Tag & "" = "" Then
        'this is for insert new
        'add data to table
        CurrentDb.Execute "INSERT INTO student([COLOR=Blue]stdid[/COLOR], stdname, gender, phone, address) " & _
                " VALUES(" & [COLOR=Blue]Me.txtID[/COLOR] & ",'" & Me.txtName & "','" & _
                Me.cboGender & "','" & Me.txtPhone & "','" & Me.txtAddress & "')"
    Else
        'otherwise (Tag of txtID store the id of student to be modified)
        CurrentDb.Execute "UPDATE student " & _
                " SET [COLOR=Blue]stdid[/COLOR]=" & [COLOR=Blue]Me.txtID[/COLOR] & _
                ", stdname='" & Me.txtName & "'" & _
                ", gender='" & Me.cboGender & "'" & _
                ", phone='" & Me.txtPhone & "'" & _
                ", address='" & Me.txtAddress & "'" & _
                " WHERE stdid=" & Me.txtID.Tag
    End If

while this is for text field:

   Dim rs As DAO.Recordset
    If Me.txtID.Tag & "" = "" Then
        'this is for insert new
        'add data to table
        CurrentDb.Execute "INSERT INTO student(stdid, stdname, gender, phone, address) " & _
                " VALUES([COLOR=Blue]'" & Me.txtID & "'[/COLOR],'" & Me.txtName & "','" & _
                Me.cboGender & "','" & Me.txtPhone & "','" & Me.txtAddress & "')"
    Else
        'otherwise (Tag of txtID store the id of student to be modified)
        CurrentDb.Execute "UPDATE student " & _
                " SET stdid=[COLOR=Blue]'" & Me.txtID &[/COLOR] _
                [COLOR=Blue]"'[/COLOR], stdname='" & Me.txtName & "'" & _
                ", gender='" & Me.cboGender & "'" & _
                ", phone='" & Me.txtPhone & "'" & _
                ", address='" & Me.txtAddress & "'" & _
                " WHERE stdid=[COLOR=Blue]'" & Me.txtID.Tag & "'"[/COLOR]
    End If

see the extra single quote for text.

 

[sneuberg]

You should check all the places where stdid is used to make sure. The delete code need this too for example

Private Sub cmdDelete_Click()
    'delete record
    'check existing selected record
    If Not (Me.frmStudentSub.Form.Recordset.EOF And Me.frmStudentSub.Form.Recordset.BOF) Then
        'confirm delete
        If MsgBox("Are you sure to delete?", vbYesNo) = vbYes Then
            'delete now
            CurrentDb.Execute "DELETE FROM student " & _
                    " WHERE stdid= [COLOR="Blue"]'[/COLOR]" & Me.frmStudentSub.Form.Recordset.Fields("stdid")[COLOR="blue"] & "'"[/COLOR]
            'refresh data in list
            Me.frmStudentSub.Form.Requery
        End If
    End If
End Sub

 

[DanielBry]

Dear Arnelgp and Sneuberg,

Really a very big thank you for all your help!

Best Regards,
Daniel