Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old January 19th, 2013, 12:11 PM
roh710 roh710 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2012
Posts: 19 roh710 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 55 m 33 sec
Reputation Power: 0
Update records in subform from parent form

Hello everyone,

I've searched through threads and I've not found exact answer I'm looking for. I am new to VBA and I hope someone could enlighten me on my problem.

I have a "CustomerOrder" form with "OrderDetail" subform, linked together by OrderID. The main form has a Order ID listbox and when clicked, it filters the customer Order and also the subform with corresponding OrderIDs.

The main form also has an "INVOICE" button and when the "INVOCE" command button is clicked, it places the OrderID into the Invoice Number field. What I want to accomplish from this point on is that when the "INVOICE" button is clicked, I want it to also update records in the subform with newly generated invoice (OrderID) number into the InvoiceNumber field.

I've tried following code but the code only updates the first record. I'm stumped.

I've attached link for the snapshot of the form. Form Snapshot
Can anyone help me with this task? Please see the code below:

Thanks!!!

Private Sub cmdINVOICE_Click()
Dim rs As Recordset
Set rs = Me.[OrderDetail subform].Form.RecordsetClone
rs.MoveFirst
Do Until rs.EOF
With rs
.Edit
[OrderDetail subform].Form![InvoiceNo] = [OrderID]
.Update
End With
rs.MoveNext
Loop
MsgBox "A Total Of " & rs.RecordCount & " Records Were Updated"
Set rs = Nothing
Me.Refresh
End Sub

Reply With Quote
  #2  
Old January 28th, 2013, 04:43 PM
June7's Avatar
June7 June7 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2010
Location: The Great Land
Posts: 256 June7 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 29 m 29 sec
Reputation Power: 4
OrderID and InvoiceNumber fields are in same table? Why two fields with the same value? Why have InvoiceNumber also saved in the child records? This is duplication of data. If the tables are related by the OrderID then InvoiceNumber can always be retrieved in query. But the OrderID and InvoiceNumber are the same so why is this necessary?
__________________
I like to know if my suggestion helped you. One way to let me know is by clicking scales at top right corner of post.
Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm

Reply With Quote
  #3  
Old February 1st, 2013, 01:16 AM
roh710 roh710 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2012
Posts: 19 roh710 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 55 m 33 sec
Reputation Power: 0
Quote:
Originally Posted by June7
OrderID and InvoiceNumber fields are in same table? Why two fields with the same value? Why have InvoiceNumber also saved in the child records? This is duplication of data. If the tables are related by the OrderID then InvoiceNumber can always be retrieved in query. But the OrderID and InvoiceNumber are the same so why is this necessary?


The invoice # and Order ID are in same table. In most cases, the OrderID becomes the invoice number. However, sometimes an order is shipped out separately and the invoice # will have "-1", "-2" etc.

For example, if OrderID of 1001 is received with 10 different items and if I can only ship out 5 items, the Invoice # will be 1001. When I ship the remaining items, the subsequent invoice # will be 1001-1.

Reply With Quote
  #4  
Old February 1st, 2013, 08:15 PM
June7's Avatar
June7 June7 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2010
Location: The Great Land
Posts: 256 June7 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 29 m 29 sec
Reputation Power: 4
My recommendation would be not to use orderID as the invoice number. If orders have to be partially shipped and billed then each shipment and billing would be entirely new invoice number. Each invoice would list the items from purchase order that are billed. Ideally, I think would open an Invoice data entry form, select the customer purchase order to bill and then select the items to bill. However, sounds like this would be a major modification of database.

Code updates only first record because refers to the subform instead of recordset.

Might help to explicitely declare the recordset as DAO.
Dim rs As DAO.Recordset
or eliminate the recordset

Private Sub cmdINVOICE_Click()
With Me.[OrderDetail subform].Form.RecordsetClone
Do Until .EOF
.Edit
!InvoiceNo = Me.OrderID
.Update
.MoveNext
Loop
MsgBox "A Total Of " & .RecordCount & " Records Were Updated"
End With
Me.Refresh
End Sub

Last edited by June7 : February 1st, 2013 at 08:18 PM.

Reply With Quote
  #5  
Old February 1st, 2013, 09:29 PM
roh710 roh710 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2012
Posts: 19 roh710 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 55 m 33 sec
Reputation Power: 0
Quote:
Originally Posted by June7
My recommendation would be not to use orderID as the invoice number. If orders have to be partially shipped and billed then each shipment and billing would be entirely new invoice number. Each invoice would list the items from purchase order that are billed. Ideally, I think would open an Invoice data entry form, select the customer purchase order to bill and then select the items to bill. However, sounds like this would be a major modification of database.

Code updates only first record because refers to the subform instead of recordset.

Might help to explicitely declare the recordset as DAO.
Dim rs As DAO.Recordset
or eliminate the recordset

Private Sub cmdINVOICE_Click()
With Me.[OrderDetail subform].Form.RecordsetClone
Do Until .EOF
.Edit
!InvoiceNo = Me.OrderID
.Update
.MoveNext
Loop
MsgBox "A Total Of " & .RecordCount & " Records Were Updated"
End With
Me.Refresh
End Sub


Thanks for your input. However, using entirely different invoice number system is not my call. The company have been using the order number as invoice number for years and if the orders are broken up, they just add "-1", "-2" etc.

I just have to program it that way..

Thanks again for your suggestion and I will tryout your code!

Reply With Quote
  #6  
Old April 10th, 2013, 12:56 PM
kyidmnmaiv kyidmnmaiv is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2013
Posts: 1 kyidmnmaiv User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 43 sec
Reputation Power: 0
Hey roh - did you ever find a solution? I'm facing the same issue. I've tried both of June's suggestions, but neither fixed it.

Reply With Quote
  #7  
Old April 11th, 2013, 03:07 AM
pooleracm pooleracm is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2013
Posts: 2 pooleracm User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 55 sec
Reputation Power: 0
I've tried both of June's suggestions, but neither fixed it.URL
URL
URL
URL

Reply With Quote
  #8  
Old April 12th, 2013, 02:03 AM
Jason3beard Jason3beard is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Apr 2013
Posts: 1 Jason3beard User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 m 2 sec
Reputation Power: 0
I've tried following code but the code only updates the first record. I'm stumped.URLURLURLURL

Reply With Quote
  #9  
Old April 13th, 2013, 03:57 PM
June7's Avatar
June7 June7 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2010
Location: The Great Land
Posts: 256 June7 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 29 m 29 sec
Reputation Power: 4
Sorry, guys, the code works for me but only once after the form first opens. A minor change allows the code to execute any number of times.

With Me.[OrderDetail subform].Form.RecordsetClone
.MoveFirst
Do Until .EOF

Would have to know more about your data and form structure and analyse your exact code.

Should start a thread on your issue. Can include reference to this thread.

Last edited by June7 : April 13th, 2013 at 03:59 PM.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Update records in subform from parent form


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap