I currently have a single form that when i fill in a date and press a button the receive field will populate the received field from the box where i enter the date. I want to move the data to a subform while the main form still has the controls. This is the code that works when it is a single continuous form. attached is a screen shot of the form and subform that i would like to use

Private Sub Command22_Click()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do Until rs.EOF
rs.Edit
rs!Received = Me.Text20
rs.Update
rs.MoveNext
Loop


End Sub

Thanks

Hi,

To convert your code from updating the main form to work for a subform, try modifying this line from:

Set rs = Me.RecordsetClone

to:

Set rs = Me.SubformContainerName.Form.RecordsetClone

Hope that helps...

Thank you..... I don't see how i missed that one....been working too many hours on this one

Thanks Again

Hello, Question...
It works the first time but i must close the form and go back in to it to do it again. any thoughts

Try adding these after the Loop:

rs.Close
Set rs = Nothing

Hope that helps...

Hello,

I get an error when i go to reset the date field for a new batch. Included is a screenshot of it.

Thanks

Hi,

Can you post your updated code? Also, have you tried using Recordset instead of RecordsetClone?

Hello. I tried using recordset and the subform has the #name error in the fields

this is the code

Private Sub Command22_Click()
Dim rs As DAO.Recordset
Set rs = Me.frmNotReceivedSub.Form.RecordsetClone
Do Until rs.EOF
rs.Edit
rs!Received = Me.Text20
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing



End Sub

Hi,


Did you mean you already tried it this way?

Set rs = Me.frmNotReceivedSub.Form.Recordset



If you did try it as a Recordset and not RecordsetClone, you might consider posting a zipped up version of your db because I can't really tell what's wrong with your code.

Just my 2 cents...

The Code works as it suppose to. I'm wondering if the the form itself needs to be reset, refreshed or something like that after the code runs

Hi,

I don't see any harm in trying. Let us know how it goes...

Ok here it is. the form in question is under the edit tab amd is receive orders

Thanks

Hi,

I got a "Unrecognized database format" error when I tried to open your file. Are you using Acc2010?

yes, with the new navigation system

i can up the original done 2 years ago in access 2007. It's 2 files as it is split when in use. it has the same issue as far as only 1 mass update then i must close the form . although i do not get the error i showd earlier.

No, that's okay. I'll just have to wait 'till I can download your db on a 2010 machine (unless somebody else beats me to it).

Later...

thanks

Hi,

Just want to give you an update, sorry for the delay but I probably won't be able to get to an Acc2010 machine until this weekend. I hope I am not causing you any problems with this delay.

No, Not at all. It was a bug with the last version also, just never realized it so it's no big deal. i've made a lot of change since the upload as so far as table normilization but that should have no effect on the issue at hand or its resolution.

Thanks

Hi,

I was finally able to download your db. If you see this post today, could you please give me a step-by-step on how to duplicate your issue? Thanks.

if you make a few entries in to the orders table with different dates then under the edits tab or reports tab is a tab called receive orders. Query by date so a couple of the orders show in the subform. type a receive date i the box and press the receive button. the date you entered for recieve will fill in the the subform in the received field. then try to requery by the date to show some of the other orders not included in the initial search. you should get the error i refered to earlier and when you enter a receive date in the box and press the receive button, nothing happens

hope thats clear enough


Thanks

Hi,

Thanks. I tried what you said but for some reason, I did not get any error. Maybe I misinterpreted your statement here:

>>then try to requery by the date to show some of the other orders not included in the initial search.<<

What date am I supposed to requery by? Am I supposed to use the same date I used before clicking on the Receive button?

Enter some orders So they cover a week. query 1/2 the week and then receive them. then requery with the second 1/2 of the week and try the receive the remaing orders

Hi,

I just tried it again, entered an order for each day for one week and then received two days worth but still not get any errors when I clicked on Requery.

Sorry...

Ok so you recieved 2 days worth say monday and tuesday, then you put the dates in the start and end box for wed and thurs and the hit the requery key. no errors hmmm. did you then put a receive date into the receive date box and press the receive button and the date filled in the subform a second time without closing?

Hi,

I don't have Acc2010 in front of me right now but yes, I think that's what I did. I could try again later.

When I entered a new date in the textbox underneath the buttons and then hit "Receive," the subform filled the records with that date. All that without closing the form first.

Thanks for the help but adding Me.Undo at the end of the code fixes the issue

Private Sub Command22_Click()
Dim rs As DAO.Recordset
Set rs = Me.frmNotReceivedSub.Form.RecordsetClone
Do Until rs.EOF
rs.Edit
rs!Received = Me.Text20
rs.Update
rs.MoveNext
Loop
rs.Close

Me.Undo

End Sub

Thanks again and to everyone who helped with my project.

Hi,

I'm glad you were able to fix the issue. Good luck with your project.

Doing this with a recordset/cursor is a bad way to go. If you really want to update all the related child records, just run an update query on the source of the subform (table), and just use the Master/Child information to make sure only the records you want are updated. Try it out first as a SELECT statement to make sure... it should look something like...

UPDATE TableThatIsSourceForSubform
SET SomeField = Date()
WHERE TableThatIsSourceForSubform.ForeignKey = Forms!MyParentForm!txtPrimaryKey

I like using recordsets when I need them, but this just isn't one of those cases.

Hello
I'm not sure what you mean. the subform comes from a query (not received) ran against the (Orders) table, on the main form is control [text20] which i input the received date. Then i have a button that onclick event runs the code i'm using. I'm not understanding your suggestion.

Thanks