Auto populate multiple text boxes and combo box in continuous form

[Ray67]

Hello



I would like to use this continuous form to stamp date and user. The user enters there name(Combo box) and date, than access stamps all of the data that appear on the form with the same data.

Provider	Check Request Number	Refund Amount	Date	User
Smith	11111	500.00	1/10/2013	John
Smith	22222	200.00	1/10/2013	John
Smith	12285	300.00	1/10/2013	John

Thank you

[June7]

Requires VBA code. Options:

1. An UPDATE sql action to edit records in table and then refresh form.

2. Looping code to cycle through the form's records and populate date field. Something like:
While Not Me.RecordsetClone.EOF
Me.RecordsetClone![Date] = Date
Me.RecordsetClone.MoveNext
Wend
Me.Refresh

BTW, should not use reserved words as names. Date is a reserved word. Also advise no spaces, special characters, punctuation (underscore is exception) in names.

[Ray67]

option 2 Would i put this code in unbound text box in the after update event?

[June7]

What unbound combobox - used to enter Provider criteria to filter the form recordset? If that's where you want it.

[Ray67]

Unbound combobox will not have filters. The form would show all data for all provider. I would have one unbounded text box and one combobox. I don't want my user to have to populate data one by one.

[June7]

I didn't ask if the combobox has filter, I asked if combobox is used to filter the form. What is the unbound combobox for? The code I suggested should update all records displayed on the form.

[Ray67]

Never mind the combobox. I chaged the name date to DateSentToAP. I put in the code and i get this error Run-time error 3020 Undate or cancelupdate without addnew or edit.

Private Sub DateSentToAP_AfterUpdate()
While Not Me.RecordsetClone.EOF
Me.RecordsetClone![DateSentToAP] = DateSentToAP
Me.RecordsetClone.MoveNext
Wend
Me.Refresh

End Sub

[June7]

I did it again, forgot the Edit!

With Me.RecordsetClone
.MoveFirst
While Not .EOF
.Edit
![DateSentToAP] = Date
.Update
.MoveNext
Wend
End With
Me.Refresh

Why are you setting DateSentToAP equal to itself? Isn't DateSentToAP the field to be populated and is currently empty?

[Ray67]

I try the code and it works but i get this messages

Write Conflict
This Record has been changed by another user since you started editing it.If you save the record you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user enteed and then paste your changes back in if you decide to make changes. Options Save record copy to clipboard Drop changes

Should this messages be showing up???

[June7]

It should not. I tested this code in my db and it works without error. I would have to examine your db.

[alcapps]

June7 code should have worked...

Can you tell me how are you filling the dataset for the form?
do you have an id field in the data?
there are several ways to update records.
use the same dataset or query and create an update query. then requery the same data in the same form. Using the after update of the textbox or combo box.

I would need more information form you though.
1. forms data source i.e. query or table (table structure)
2. screen shot of the form or a sample database with sample data in it.

I can help you but I will need this information to give you more help.

[Ray67]

June7 and Alcapps

Thank you for your reply. I was able to fix the error. The code june7 gave is correct. This behavior occurs when you open two forms that update the same data source at the same time. The first form puts an edit lock on the record or records, and then the second form changes the record or records and saves the changes. When the first form tries to close and write back to the table, the changes that were made by the second form are detected. This causes the error to be returned.. Here is the link http://support.microsoft.com/kb/304181

Thank you