Update Subform

[SteveMck]

Hi Think i have got this in the right section.

I have a subform which is in datashet veiw and may return multiple records. Within this form i am trying to update the Problem feild in the subform which is a text box to "No Problems - Job Approved"
I am using an IF statement from the main form.This works fine but only updates one record how can i ensure all records within the subform are updated? Any help will be much apprciated.

If QtyOfFaults > 0 Then
Insp = "Fail"
Status_Control = "1"
MsgBox "Enter problem against part number", vbInformation, "Error's"

Else
Insp = "Pass"
Status_Control = "2"
Me.Fault_Data_subform.Form!Problem = "No Problems - Job Approved"
End If

End Sub

 

[PHV]

You may try to replace this:
Me.Fault_Data_subform.Form!Problem = "No Problems - Job Approved"
with this:
With Me!Fault_Data_subform.Form
.Recordset.MoveFirst
While Not .Recordset.EOF
!Problem = "No Problems - Job Approved"
.Recordset.MoveNext
WEnd
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SteveMck]

Thanks I have altered my code which now looks like this

Else
Insp = "Pass"
Status_Control = "2"
With Me!Fault_Data_subform.Form
.Recordset.MoveFirst
While Not .Recordset.EOF
!Problem = "No Problems - Job Approved"
.Recordset.MoveNext
Wend
End With
End If

But i now get a run time error 2465

ms office can't findthe feild Fault_Data_subform.Form referred to in your expression.
Any Ideas? i tried adding "!Problem" to Fault_Data_subform.Form

To no avail.

 

[PHV]

And this ?
With Me.Fault_Data_subform.Form

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SteveMck]

thried that too! Me.Fault_Data_subform.Form!Problem
still get the same error

 

[SteveMck]

not sure if i had a typo i've run the code again i now get an error here
.Recordset.MoveFirst

Runtime 91
object not variable or object not set

 

[SteveMck]

Wrror should have read as below.

object variable or with block variable not set

 

[PHV]

Datasheet view you said ?
what about this ?
...
Else
Insp = "Pass"
Status_Control = "2"
With Me.Fault_Data_subform.Form
Set rst = .Recordset
rst.MoveFirst
While Not rst.EOF
!Problem = "No Problems - Job Approved"
rst.MoveNext
Wend
Set rst = Nothing
End With
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SteveMck]

Fantastic!! thanks for all your help this works a treat.

Steve