How to update a field in a continuous form

[OssieMac]

Access 2002.

I have a form with the Default View set to Continuous forms. The Record
Source for the form is set up with the forms query builder and gets a filter
value from a combo box in the form header. An After Update event on the combo
box runs Me.Requery. All this works fine and displays the list of records
matching the filter.

What I want to do is enter a number in a text box in the form header and
then copy that number to a field in all of the displayed records. Can it be
done and if so what code is required to do it.

With the continuous form I am not even able to edit the records on the
screen and enter the numbers individually for each record. If I set Data
entry property to Yes then no records get displayed on the Requery. (Even
with Allow edits, Allow Deletions and Allow Additions set to Yes.)

Any help will be greatly appereciated.

 

[Ken Snell \(MVP\)]

Assuming that you use a command button to initiate the edit of the records:

Private Sub ButtonName_Click()
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
.Fields("NameOfTheFieldBeingUpdated").Value = Me.TextboxName.Value
.MoveNext
Loop
End With
End Sub

 

[OssieMac]

Thanks for your reply Ken. I am sure that it is probably close to what I want
but I am getting a runtime error on the line
..Fields("ReceiptOut").Value = Me.ReceiptNo.Value

Runtime error 3020
Update or cancelupdate without addnew or edit.

I have overcome the problem of editing the records directly on the form but
sometimes there are a lot of them and I would like to be able to do it with
code from a single entry.

Will appreciate any further help to solve this problem.


--
Regards,

OssieMac

Assuming that you use a command button to initiate the edit of the records:

Private Sub ButtonName_Click()
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
.Fields("NameOfTheFieldBeingUpdated").Value = Me.TextboxName.Value
.MoveNext
Loop
End With
End Sub

 

[OssieMac]

Hi Ken,

I have now solved the problem. Just needed a couple of extra lines of code.
I really appreciate your help and it was due to your code sample that I have
been able finally solve the problem. Without it I had no idea where to start.
I finally managed to find an item on this forum that explained why the error
occurs and that was how I managed to work out that I probably needed edit
before the copy and update after.

Anyway here is the modified code for anyone else that it might help:-

Private Sub CopyReceipt_Click()
'Copies a textbox value in the header of a form
'into a field for all records in a continuous form

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
.edit
.Fields("ReceiptOut").Value = Me.ReceiptNo.Value
.Update
.MoveNext
Loop
End With

End Sub


--
Regards,

OssieMac

Thanks for your reply Ken. I am sure that it is probably close to what I want
but I am getting a runtime error on the line
.Fields("ReceiptOut").Value = Me.ReceiptNo.Value

Runtime error 3020
Update or cancelupdate without addnew or edit.

I have overcome the problem of editing the records directly on the form but
sometimes there are a lot of them and I would like to be able to do it with
code from a single entry.

Will appreciate any further help to solve this problem.

 

[Ken Snell \(MVP\)]

My sincere apologies for leaving out those two lines of code. I cannot offer
any valid excuse except -- I goofed! Glad you got the code working
nonetheless!