By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Update subdatasheet from main form

aas4mis
P: 97
I have two textboxes in my main form that I use for default information. (purely to save keystrokes for the end user). They are txtInv (invoice number) and txtSlj (date invoiced). My subform in viewed in datasheet mode (has fields "invoice" and "slj". I know it's possible to update a subform with information from the main form when viewed as a form but how would that be done when the subform is viewed as a datasheet?
Feb 8 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,599
I have two textboxes in my main form that I use for default information. (purely to save keystrokes for the end user). They are txtInv (invoice number) and txtSlj (date invoiced). My subform in viewed in datasheet mode (has fields "invoice" and "slj". I know it's possible to update a subform with information from the main form when viewed as a form but how would that be done when the subform is viewed as a datasheet?
I'll give you a Template for the Invoice Field:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. If IsNull(Me![txtInv]) Then Exit Sub
  3.  
  4. DoCmd.SetWarnings False
  5.   strSQL = "Update <Child Table> Set <Child Table>.Invoice = '" & Me![txtInv] & "' Where [<Child Table>.[<Foreign Key>] = " & Me![<Primary Key>]
  6.   DoCmd.RunSQL strSQL
  7. DoCmd.SetWarnings True
  8.  
  9. Me![<Sub-Form Control>].Requery
Feb 9 '08 #2

aas4mis
P: 97
I'll give you a Template for the Invoice Field:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. If IsNull(Me![txtInv]) Then Exit Sub
  3.  
  4. DoCmd.SetWarnings False
  5.   strSQL = "Update <Child Table> Set <Child Table>.Invoice = '" & Me![txtInv] & "' Where [<Child Table>.[<Foreign Key>] = " & Me![<Primary Key>]
  6.   DoCmd.RunSQL strSQL
  7. DoCmd.SetWarnings True
  8.  
  9. Me![<Sub-Form Control>].Requery
Thanks for the input but, this doesn't work for me because of the sql string. It depends on a matching foreign key. My child table doesn't have a matching identifier as [invoice] and [sgl] are new values [key created on new record]. What needs to be done to the sql string to create the record in the child table? Your help is appreciated.
Feb 9 '08 #3

ADezii
Expert 5K+
P: 8,599
I have two textboxes in my main form that I use for default information. (purely to save keystrokes for the end user). They are txtInv (invoice number) and txtSlj (date invoiced). My subform in viewed in datasheet mode (has fields "invoice" and "slj". I know it's possible to update a subform with information from the main form when viewed as a form but how would that be done when the subform is viewed as a datasheet?
Let me see if I can get two strikes against me on this one (LOL). The following code will:
  1. Set Focus to the Sub-Form Control.
  2. Place the Sub-Form itself in Add Record Mode.
  3. Transfer 2 Values from the Main Form to the Sub-Form.
  4. Am I close yet?
    Expand|Select|Wrap|Line Numbers
    1. 'Set Focus to the Sub-Form
    2. Me![<Sub-Form Control>].SetFocus
    3.  
    4. 'Add a New Record
    5. DoCmd.GoToRecord , , acNewRec
    6.  
    7. 'Populate 2 Fields in the Sub-Form from values in the Main Form
    8. Me![![<Sub-Form Control>].Form![Invoice] = Me![txtInv]
    9. Me![![<Sub-Form Control>].Form![Slj] = Me![Slj]
Feb 9 '08 #4

aas4mis
P: 97
I'll give you a Template for the Invoice Field:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. If IsNull(Me![txtInv]) Then Exit Sub
  3.  
  4. DoCmd.SetWarnings False
  5.   strSQL = "Update <Child Table> Set <Child Table>.Invoice = '" & Me![txtInv] & "' Where [<Child Table>.[<Foreign Key>] = " & Me![<Primary Key>]
  6.   DoCmd.RunSQL strSQL
  7. DoCmd.SetWarnings True
  8.  
  9. Me![<Sub-Form Control>].Requery
Well, your code definately pointed me in the right direction. [I put this in the detail dblclick event for testing.] With your help I was able to come up with the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Detail_DblClick(Cancel As Integer)
  2.  
  3.     Dim strSQL
  4.  
  5.     If IsNull(Me![txtInv]) Then Exit Sub
  6.  
  7.     strSQL = "INSERT INTO tblDetailsP (" & "invoice, slj) " & _
  8.              "VALUES(txtInv, txtSlj);"
  9.  
  10.     DoCmd.RunSQL strSQL
  11.  
  12.     Me![details].Requery
  13.  
  14. End Sub
Thanks for the pointers!
Feb 9 '08 #5

aas4mis
P: 97
Let me see if I can get two strikes against me on this one (LOL). The following code will:
  1. Set Focus to the Sub-Form Control.
  2. Place the Sub-Form itself in Add Record Mode.
  3. Transfer 2 Values from the Main Form to the Sub-Form.
  4. Am I close yet?
    Expand|Select|Wrap|Line Numbers
    1. 'Set Focus to the Sub-Form
    2. Me![<Sub-Form Control>].SetFocus
    3.  
    4. 'Add a New Record
    5. DoCmd.GoToRecord , , acNewRec
    6.  
    7. 'Populate 2 Fields in the Sub-Form from values in the Main Form
    8. Me![![<Sub-Form Control>].Form![Invoice] = Me![txtInv]
    9. Me![![<Sub-Form Control>].Form![Slj] = Me![Slj]
HA! You beat me to the post, This wasn't here when I was typing post #5. I haven't tried this code but it looks like it'll work. Just goes to show there's more than one way to get things done. You've helped out on other posts, just wanted to let you know your knowledge is priceless. Thanks ADezii.
Feb 9 '08 #6

ADezii
Expert 5K+
P: 8,599
HA! You beat me to the post, This wasn't here when I was typing post #5. I haven't tried this code but it looks like it'll work. Just goes to show there's more than one way to get things done. You've helped out on other posts, just wanted to let you know your knowledge is priceless. Thanks ADezii.
That's why we are all here - to help.
Feb 9 '08 #7

aas4mis
P: 97
UPDATE:
The code in post #5 is a no go. That code inserts values into a new record, not associating with the record in the main form. The correct code is in post #6 by ADezii. This creates a new record that's associated with the correct key. Just be careful of the extra left bracket on lines 8 and 9.
:END UPDATE
Feb 9 '08 #8

ADezii
Expert 5K+
P: 8,599
UPDATE:
The code in post #5 is a no go. That code inserts values into a new record, not associating with the record in the main form. The correct code is in post #6 by ADezii. This creates a new record that's associated with the correct key. Just be careful of the extra left bracket on lines 8 and 9.
:END UPDATE
The 'Olde Extra Left Bracket' it gets me all the time (LOL).
Feb 9 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.