Looping through records in subform not working (1 Viewer)

[bd200]

Hi Guys,

Im v new to VBA and been having trouble getting the following code to loop through the records in my subform:

Private Sub cmdComplete_Call_Click()

Dim rs As dao.Recordset
Set rs = Me.fsub_Call_Off_Quantities.Form.RecordsetClone
rs.MoveFirst

Do Until rs.EOF
[fsub_Call_Off_Quantities].Form![txtQuantity_Called_Off] = [fsub_Call_Off_Quantities].Form![Quantity]
rs.MoveNext
Loop

End Sub

It only seems to update the first records txtQuantity_Called_Off value. I have tried all the 'looping' code variations i can find and they all seem to only update the first :banghead:

Please can someone point out where im going wrong ?!

Thanks in advanced
J

 

[James Dudden]

Try this:

Private Sub cmdComplete_Call_Click()
With Me.fsub_Call_Off_Quantities.Form.RecordsetClone
    If .RecordCount <> 0 Then
        .MoveFirst
        Do Until .EOF
            ![txtQuantity_Called_Off] = ![Quantity]
            .MoveNext
        Loop
    End If
End With
End Sub

 

[bd200]

Hi JD,

Thanks for the almost instant response !! Ahh ive spent all day failing to write this tiny snippet of code :banghead::banghead:

Unfortunately i tried your code and recieved the following error:

Run-time error 3265:

Item not found in this collection

Debugging highlighted

![txtQuantity_Called_Off] = ![Quantity]

Just for some added info:
'fsub_Call_Off_Quantities' is the subform
'tsub_Order_Quantities' is the table and record source for the sub form

Am i right in thinking i need to reference the form in ?: Me.fsub_Call_Off_Quantities.Form.RecordsetClone

Thanks again for the help !!

J

 

[spikepl]

Explain in plain English - not DB speak - what you are attempting to do.

 

[Cronk]

BD200

I suspect the error is because you do not have a field in your table called
txtQuantity_Called_Off

Maybe that is a control in your sub form and you are trying to edit the table contents of field Quantity to the value in the text box. In which case reverse the edit.

Incidentally, the edit should be be included within an rs.Edit and rs.Update command.

And yes, it does help if you explain what you want the process to do rather than having us guess/assume.

 

[James Dudden]

As Cronk pointed out the code was lacking a .Edit and .Update which I have now added below. As for the error - again as Cronk says - it is due to a field being missing from your recordset. So either ![txtQuantity_Called_Off] or ![Quantity] is not in your recordset.

Private Sub cmdComplete_Call_Click()
With Me.fsub_Call_Off_Quantities.Form.RecordsetClone
    If .RecordCount <> 0 Then
        .MoveFirst
        Do Until .EOF
            .Edit
            ![txtQuantity_Called_Off] = ![Quantity]
            .Update
            .MoveNext
        Loop
    End If
End With
End Sub

 

[bd200]

Cronk & JD you both are legends...

Cronk you were right, 'txtQuantity_Called_Off' was referencing the control name on the subform & not the source.. oops !

JD the code works a charm. You da man

Much much appreciation to both you guys.