How to copy data from a Subform to a table using VBA

[greeni91]

I am currently in the process of remaking a database that was created a long time ago. I have hit a bit of a snag when it comes to using subforms.

I have a main form called "New Fixtures"

In this form I have one field called "Fixture"

I also have one subform called "ChoosenParts_subform"

In the subform I have 2 fields called "Part" and "Operation"

What I am trying to achieve is:

I want a form that you enter a specific fixture number. Then I will enter the Part Number(s) and Operation(s) for this fixture in the subform. When I am done I will click on a command button called "cmdSave" this will copy the data I have just entered into my main form and subform and place it as a new record in a table called "Part".

My code is as follows:

Private Sub cmdSave_Click()
 
Dim strSQL As String
Dim Fixt As Variant, Part As Variant, Op As Variant
 
 
Fixt = Me.FixtureNumber
*Part = Forms![New Fixture]![ChoosenParts_subform].Form.["Part"]
*Op = Forms![New Fixture]![ChoosenParts_subform].Form.["Operation"]
 
       With Me
 
           strSQL = "INSERT INTO [FixtPart] " & _
                                "([Fixture] " & _
                                ",[Part] " & _
                                ",[Operation] " & _
                    "VALUES      ('%F' " & _
                                ",'%P' " & _
                                ",'%O')"
 
           strSQL = Replace(strSQL, "%F", Fixt)
           strSQL = Replace(strSQL, "%P", Part)
           strSQL = Replace(strSQL, "%O", Op)
 
 
           Call CurrentDb.Execute(strSQL)
           MsgBox "Fixture Information Updated!", vbOKOnly, "...Complete..."
       End With
 End Sub

Where I have placed an asterisk is where the Debugger is getting an error and returning a value "Empty".

Any help on this is greatly appreciated.

Regards,

greeni91

[Seth Schrock]

I believe that the error on your lines 8 & 9 are caused by the fact that you have double quotes " around the field names Part and Operation. Those need to be removed. I haven't looked at the rest of the code other than to wonder why the With/End With statements. I don't see where any Me.something is used. Not being used won't produce an error (I think, though I have never tried it), but seems unnecessary.

[NeoPa]

Forms![New Fixture] is not required as Me is always a reference to the associated form (and is less complicated).

Otherwise your references are pretty close. Seth has already pointed out the need to lose the double-quotes, but otherwise they should work. If you look through Referring to Items on a Sub-Form you'll see an alternative is to replace the ".Form." with "!".

Part = Me.ChoosenParts_subform!Part
Op = Me.ChoosenParts_subform!Operation

[greeni91]

Thanks guys,

I have followed NeoPa's way of writing the code and it is working perfect now.

Here is a copy of the working code for reference:

Private Sub cmdSave_Click()
 
Dim strSQL As String
Dim Fixt As Variant, Op As Variant
Dim Prt As String
 
 
Fixt = Me.FixtureNumber
Prt = Me.ChoosenParts_subform!Part
Op = Me.ChoosenParts_subform!Operation
 
       With Me
 
           strSQL = "INSERT INTO [FixtPart] " & _
                                "([Fixture] " & _
                                ",[Part] " & _
                                ",[Operation] )" & _
                    "VALUES      ('%F' " & _
                                ",'%P' " & _
                                ",'%O')"
 
           strSQL = Replace(strSQL, "%F", Fixt)
           strSQL = Replace(strSQL, "%P", Prt)
           strSQL = Replace(strSQL, "%O", Op)
 
 
           Call CurrentDb.Execute(strSQL)
           MsgBox "Fixture Information Updated!", vbOKOnly, "...Complete..."
       End With
 End Sub

[NeoPa]

I'm pleased we could help :-)