Update subdatasheet from main form

[aas4mis]

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?

[ADezii]

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:

[CODE=vb]
Dim strSQL As String
If IsNull(Me![txtInv]) Then Exit Sub

DoCmd.SetWarnin gs False
strSQL = "Update <Child Table> Set <Child Table>.Invoice = '" & Me![txtInv] & "' Where [<Child Table>.[<Foreign Key>] = " & Me![<Primary Key>]
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True

Me![<Sub-Form Control>].Requery[/CODE]

[aas4mis]

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

[CODE=vb]
Dim strSQL As String
If IsNull(Me![txtInv]) Then Exit Sub

DoCmd.SetWarnin gs False
strSQL = "Update <Child Table> Set <Child Table>.Invoice = '" & Me![txtInv] & "' Where [<Child Table>.[<Foreign Key>] = " & Me![<Primary Key>]
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True

Me![<Sub-Form Control>].Requery[/CODE]

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.

[ADezii]

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?
   [CODE=vb]
   'Set Focus to the Sub-Form
   Me![<Sub-Form Control>].SetFocus
   
   'Add a New Record
   DoCmd.GoToRecor d , , acNewRec
   
   'Populate 2 Fields in the Sub-Form from values in the Main Form
   Me![![<Sub-Form Control>].Form![Invoice] = Me![txtInv]
   Me![![<Sub-Form Control>].Form![Slj] = Me![Slj][/CODE]

[aas4mis]

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

[CODE=vb]
Dim strSQL As String
If IsNull(Me![txtInv]) Then Exit Sub

DoCmd.SetWarnin gs False
strSQL = "Update <Child Table> Set <Child Table>.Invoice = '" & Me![txtInv] & "' Where [<Child Table>.[<Foreign Key>] = " & Me![<Primary Key>]
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True

Me![<Sub-Form Control>].Requery[/CODE]

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:
[code=vb]Private Sub Detail_DblClick (Cancel As Integer)

Dim strSQL

If IsNull(Me![txtInv]) Then Exit Sub

strSQL = "INSERT INTO tblDetailsP (" & "invoice, slj) " & _
"VALUES(txt Inv, txtSlj);"

DoCmd.RunSQL strSQL

Me![details].Requery

End Sub[/code]
Thanks for the pointers!

[aas4mis]

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?
   [CODE=vb]
   'Set Focus to the Sub-Form
   Me![<Sub-Form Control>].SetFocus
   
   'Add a New Record
   DoCmd.GoToRecor d , , acNewRec
   
   'Populate 2 Fields in the Sub-Form from values in the Main Form
   Me![![<Sub-Form Control>].Form![Invoice] = Me![txtInv]
   Me![![<Sub-Form Control>].Form![Slj] = Me![Slj][/CODE]

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.

[ADezii]

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.

[aas4mis]

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

[ADezii]

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).