Move Current record from Main AND sub form to another table

[jw5107]

Below is code I retrieved:

Private Sub Command75_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.OpenForm "Copy of MORNING", , , acNew
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "Copy of MORNING"
End Sub

This code takes the current record of the main form ONLY and adds it to another form as a new record. I would like for this to work with the current record on the on the subform as well. Ending up with the current records from the main and subform as a complete new record in another form. How do I get this code to pull the current record from the subform at the same time??

Also - when the code is ran - I keep getting an error that states some of the field names don't match. I don't want to save EVERY field as an new record, just a select few...
Can get an example of how this could be done??

Thanks in advance!!!!
jw5107

 

[jw5107]

Can anybody help me with this one?? Still no response...
Thanks in advance...
jw

 

[willir]

jw5107

I would approach this another way. Obviously, you want the record written to a table -- same table as the orignal form, or a different table?

You can ise DAO or ADO or SQL to insert the current record in the current or different table. Pretty easy, espcially if you understand record sets.

Then if you realy must open another form, open up the new form using the link criteria or OpenArg feature.

 

[jw5107]

Willir,

Do you have an example of how I would apply this to a command button??

I am not all that understanding of recordsets, but if I can get a good example I can make it work in my d/base.

I want to record written to a different table then the original form. I have a main form with a subform. The main form shows the aircraft delays, and the subform shows the aircraft parts needed to clear the delay.... I need to be able to click a button and move the current record from both the main and subform to a different table.

Thanks for the response!!!
jw

 

[powerlock2005]

Try this:

DoCmd.RunSQL "INSERT INTO tbl_name2 SELECT * FROM tbl_name1 WHERE someID = " & Me.someID & ";"


DoCmd.RunSQL "INSERT INTO tbl_subname2 SELECT * FROM tbl_subname1 WHERE someID = " & Me.someID & ";"

MsgBox "The records have been successfully copied"

Hope that helps.

 

[TheAceMan1]

How are ya jw5107 . . . . .

The following transfers your data from source form & subform to the destination form, and allows y[blue]ou select the specific fields[/blue]. The [blue]source and destination field names can be different.[/blue]
[ol][li]The code checks to see if the destination form is open and opens the form if not. To detect this condition, copy/paste the following in a [blue]module[/blue] in the modules window:

[blue]Function IsOpenForm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function[/blue]

[/li]
[li]In the [blue]code module[/blue] of the [blue]source form[/blue], copy/paste the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub XferData()
   Dim Ary, srcFrm As Form, desFrm As Form, desName As String
   Dim MainPairs As String, subPairs As String
   Dim x As Integer, y As Integer
   
   desName = "[purple][b]DestinationFormName[/b][/purple]"
   
   If Not IsOpenForm(desName) Then
      DoCmd.OpenForm DesName
   End If
   
   Set desFrm = Forms(desName)
   DoCmd.RunCommand acCmdRecordsGoToNew
   
   [green]'SFCN = SourceFormControlName
   'DFCN = DestinationFormControlName
   'Add as many pairs as desired in the format shown.[/green]
   MainPairs = "SFCN,DFCN,SFCN,DFCN" [green]'MainForm[/green]
   subPairs = "SFCN,DFCN,SFCN,DFCN"  [green]'subForm[/green]
   
   For x = 1 To 2 [green]'1 for MainForm, 2 for subForm[/green]
      If x = 1 Then
         Set srcFrm = Me
         Ary = Split(MainPairs, ",")
      Else
         Set srcFrm = Me![purple][b]subFormName[/b][/purple].Form
         Ary = Split(subPairs, ",")
      End If
         
      [green]'Transfer here[/green]
      For y = LBound(Ary) To UBound(Ary) - 1 Step 2
         desFrm(Ary(y + 1)) = srcFrm(Ary(y))
      Next
   Next

   Set desFrm = nothing
   Set srcFrm = nothing

End Sub[/blue]

[/li][/ol]
[purple]You can call the routine anyway you like . . .[/purple]

See Ya! . . . . . .