Can't update field after requery

[johnsonhuo]

Hi folks,

just have a stupid question to ask for help

When I try to update some of records from subform by code

Me.Text10 = .Fields("Car")

This one will not work after code
me.subformA.form.requery

it indicates that the value isn't valid for the field.

I really have no idea how this happens and it will be really great if someone call help me solve it.

Thank you very much!

 

[arnelgp]

Where does .field comes from, give the complete code.

 

[johnsonhuo]

Hi Arnelgp,

Thank you so much for your reply.

The complete code is

Private Sub Command16_Click()
If Me.Command16.Caption = "Add" Then
If Nz(Me.Text6, "") = "" Then
MsgBox "Please input Set Number"
ElseIf Nz(Me.Text10, "") = "" Then
MsgBox "Please input Car Number"
Else
CurrentDb.Execute "insert into OilSampleEmail ([Set], [Car]) values ('" & Me.Text6 & "', '" & Me.Text10 & "')"
Me.Text10 = Null
Me.OilSampleEmail_subform.Form.Requery
End If

ElseIf Me.Command16.Caption = "Update" Then

If Nz(Me.Text6, "") = "" Then
MsgBox "Please input Set Number"
ElseIf Nz(Me.Text10, "") = "" Then
MsgBox "Please input Car Number"
Else
Me.OilSampleEmail_subform.SetFocus

CurrentDb.Execute "update OilSampleEmail set [Set] ='" & Me.Text6 & "', [Car]='" & Me.Text10 & "' where [ID]=" & Me.Text10.Tag
Me.Text10 = Null
Me.Command16.Caption = "Add"
Me.Command58.Enabled = True
'Me.OilSampleEmail_subform.Form.Requery
End If
End If

End Sub



Private Sub Command58_Click()
If Not (Me.OilSampleEmail_subform.Form.Recordset.EOF And Me.OilSampleEmail_subform.Form.Recordset.BOF) Then
With Me.OilSampleEmail_subform.Form.Recordset
Me.Text10 = .Fields("Car")
Me.Text10.Tag = .Fields("ID")
Me.Command16.Caption = "Update"
Me.Command58.Enabled = False
End With
End If

End Sub

 

[Pat Hartman]

Try cleaning up a couple of problems.

1. Controls should ALWAYS be given meaningful names BEFORE you start to use them. Changing the names now will be a PITA since it will orphan any event code which you will need to find and fix. At least field references will generate compile errors if you miss any of them.

2. Validation code belongs in the BeforeUpdate event of the Form, NOT in the click event of your save button.

3. Using unbound forms gives you all the baggage of working with Access but not the benefits. Access is a Rapid Application Development (RAD) tool. At its heart is bound forms. If you are not using bound forms, you should probably not be using Access as your FE.

 

[arnelgp]

Ok, when you requery the subfrm all record pointer becomes invalid that is why you get "no current record" sometimes.
It would be better to move the record pointer first before getting the field value.

With Me.OilSampleEmail_subform.Form.Recordset
.movefirst
...
...

You can also reference the Form:

With Me.OilSampleEmail_subform.Form
.recordset.movefirst
Me.Text10 = .Controls("Car").value
Me.Text10.Tag = .Controls("ID").value

 

[johnsonhuo]

Ok, when you requery the subfrm all record pointer becomes invalid that is why you get "no current record" sometimes.
It would be better to move the record pointer first before getting the field value.

With Me.OilSampleEmail_subform.Form.Recordset
.movefirst
...
...

You can also reference the Form:

With Me.OilSampleEmail_subform.Form
.recordset.movefirst
Me.Text10 = .Controls("Car").value
Me.Text10.Tag = .Controls("ID").value

Oh My World, this is exactly the solution!

You really helped me again arnelgp!

It is so great to have you here and I guess you know everything about VBA and access. You are a real talent!

Thank you very much!

 

[johnsonhuo]

Try cleaning up a couple of problems.

1. Controls should ALWAYS be given meaningful names BEFORE you start to use them. Changing the names now will be a PITA since it will orphan any event code which you will need to find and fix. At least field references will generate compile errors if you miss any of them.

2. Validation code belongs in the BeforeUpdate event of the Form, NOT in the click event of your save button.

3. Using unbound forms gives you all the baggage of working with Access but not the benefits. Access is a Rapid Application Development (RAD) tool. At its heart is bound forms. If you are not using bound forms, you should probably not be using Access as your FE.

Hi Pat Hartman,

Thank you so much for your advice. It is really important and I will rename all those text boxes and buttons and make my code more professional!

 

[arnelgp]

Thanks for the compliment. But i am just a small inefficient, in experienced new user here.