How to Transfer data from one subform to another?

[joner89]

I populate the fields of one subform by using a combo box, these fields contain product details.

Once this data has been obtained i want it to be added to another subform, this is an order subform ready for the products to be ordered.

i want to do this by using a button, therefore when a user selects the prducts they want they select the 'add to order' button to add the selection to their order form.

My attempt at this button so far is as follows...

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
 
Dim sql_string As String
sql_string = "INSERT INTO F_Order_line_subform (ProductID,Category,Description,Price) SELECT '" & _
Me.Recordset.Fields("ProductID").Value & "','" & Me.Recordset.Fields("Category").Value & "','" & _
Me.Recordset.Fields("Description").Value & "','" & Me.Recordset.Fields("Price").Value & "'"
Call DoCmd.RunSQL(sql_string)

Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
    
End Sub

As you can see i am trying to select the fields in my product catalogue and insert them into my order form(F_Order_line_subform).

The error i am getting is that.

'Could not find output table F_Order_line_subform'.

Why is this? It isnt a table in anycase so i dont understand why its saying it is. Is there another way to acheive what i want.

Thanks in advance!

 

[Galaxiom]

You cannot use sql to update the recorset of a form, only tables and queries. That is why it is saying it can't find the table.

 

[joner89]

Thanks GalaxiomAtHome.

So is there a way i can update the form? Its basically jsut a transferring exercise.

Move all values in my product catalogue form to my order form.

Thanks for your help so far!

 

[Galaxiom]

In the main form VBA repeat this for each value you want to transfer

Me.subformcontrolname.Form.subformcontrol = Me.controlname

Note that the default property of a control is Value do you can leave this out.
However if you change what is in the control without saving the record and want to refer to that text you must use the Text property of the control.

Another little known default is that of a subformcontrol.

Me.subformcontrol!fieldname

is the same as:

 Me.subformcontrol.Form.Recordset!fieldname

 

[Galaxiom]

From the VBA of a subform to another subform on the mainform:

Me.Parent.subformcontrol.Form.controlname = Me.controlname

This is a near complete reference resource:
http://www.mvps.org/access/forms/frm0031.htm

 

[joner89]

Thanks again GlaxiomAtHome!

I have tried to do what youve said, i think i understand now but for some reason i am still getting errors.

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
 
Me.F_Orders.Product_Catalogue.ProductID = Me.F_Orders.F_Order_line_subform.ProductID
Me.F_Orders.Product_Catalogue.Category = Me.F_Orders.F_Order_line_subform.Category
Me.F_Orders.Product_Catalogue.Description = Me.F_Orders.F_Order_line_subform.Description
Me.F_Orders.Product_Catalogue.Price = Me.F_Orders.F_Order_line_subform.Price
 
 
Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
    
End Sub

Method or data member not found<<< This is the error message i keep getting.

This is what im trying...

Me.F_Orders(main form).Product_Catalogue(Subform name).ProductID(field name) = Me.F_Orders(main form).F_Order_line_subform(Subform name).ProductID(field name)

Shouldnt this work now? Im not really sure what the error means? Maybe it cant find my form for some reason?

Thanks again!

 

[Galaxiom]

You must include the Form property in a subform reference:

Me.F_Orders.F_Order_line_subform.Form.ProductID

 

[joner89]

Galaxiom,

I am already including the form name in my code. The form is F_Orders.

I still dont understand why it isnt working.

I have...

Me.Form.SubForm.FieldName.

Why isnt this working!?

Thanks for being patient and helping!

 

[JANR]

This it what Galaxiom means:

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
 
Me.F_Orders.Product_Catalogue.[COLOR=red]Form[/COLOR].ProductID = Me.F_Orders.F_Order_line_subform.[COLOR=red]Form[/COLOR].ProductID
Me.F_Orders.Product_Catalogue.[COLOR=red]Form[/COLOR].Category = Me.F_Orders.F_Order_line_subform.[COLOR=red]Form[/COLOR].Category
Me.F_Orders.Product_Catalogue.[COLOR=red]Form[/COLOR].Description = Me.F_Orders.F_Order_line_subform.[COLOR=red]Form[/COLOR].Description
Me.F_Orders.Product_Catalogue.[COLOR=red]Form[/COLOR].Price = Me.F_Orders.F_Order_line_subform.[COLOR=red]Form[/COLOR].Price
 
 
Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
    
End Sub

JR

 

[joner89]

Hi JANR

Thanks for your help

I know this is what he meant but im still getting errors?

Code:

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
Me.F_Orders.Product_Catalogue.Form.ProductID = Me.F_Orders.F_Order_line_subform.Form.ProductID
Me.F_Orders.Product_Catalogue.Form.Category = Me.F_Orders.F_Order_line_subform.Form.Category
Me.F_Orders.Product_Catalogue.Form.Description = Me.F_Orders.F_Order_line_subform.Form.Description
Me.F_Orders.Product_Catalogue.Form.Price = Me.F_Orders.F_Order_line_subform.Form.Price

Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
    
End Sub

This is what i get when i try and click the button. <<< Compile error: Data or method member not found.

It then highlights this section.

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
Me.F_Orders.Product_Catalogue.Form.ProductID = Me[COLOR=red].F_Orders[/COLOR].F_Order_line_subform.Form.ProductID
Me.F_Orders.Product_Catalogue.Form.Category = Me.F_Orders.F_Order_line_subform.Form.Category
Me.F_Orders.Product_Catalogue.Form.Description = Me.F_Orders.F_Order_line_subform.Form.Description
Me.F_Orders.Product_Catalogue.Form.Price = Me.F_Orders.F_Order_line_subform.Form.Price

Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
    
End Sub

What does this mean? I dont understand as i am selecting the relevant form,subform and field but its not having it!

Thanks in advance!

 

[JANR]

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
Me.F_Orders.Product_Catalogue.Form.ProductID = Me.F_Order_line_subform.Form.ProductID
Me.F_Orders.Product_Catalogue.Form.Category = Me.F_Order_line_subform.Form.Category
Me.F_Orders.Product_Catalogue.Form.Description = Me.F_Order_line_subform.Form.Description
Me.F_Orders.Product_Catalogue.Form.Price = Me.F_Order_line_subform.Form.Price

Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
    
End Sub

If you look at the link Galaxiom gave you then the syntax for refering to another subform on the same main form is:

If you are on subForm1 and you want to refrence a control on subform2 then:

Me!F_Order_line_subform.Form.Price

Me refers to the mainform which holds the F_Order_Line_Subform conteiner.

JR

 

[joner89]

JANR

I was unaware the Me actually referred to the main form so thankyou for that. Here is my new code.

Code:

Private Sub AddtoOrder_Click()
On Error GoTo Err_AddtoOrder_Click
 
Me!Product_Catalogue.Form.ProductID = Me!F_Order_line_subform.Form.ProductID
Me!Product_Catalogue.Form.Category = Me!F_Order_line_subform.Form.Category
Me!Product_Catalogue.Form.Description = Me!F_Order_line_subform.Form.Description
Me!Product_Catalogue.Form.Price = Me!F_Orders.F_Order_line_subform.Form.Price

Exit_AddtoOrder_Click:
    Exit Sub
Err_AddtoOrder_Click:
    MsgBox Err.Description
    Resume Exit_AddtoOrder_Click
    
End Sub

This is the new error message i am getting>>>>>'Microsoft Access cant find the field 'F_Order_line_subform'.

This isnt a field though it is a form? This suggests maybe the order i am trying to call the data is wrong? Should i declare the field ProductID first then state what form and main form it resides?

Maybe i should take a different appraoch to this as i cant get it to work.

I noticed there is a button function that duplicates a record. Should i use this and try to get it to duplicate the record in from one subform in another?

Or should i stick to this method? It seems there is a flaw somewhere as you guys keep giving me answers that make sense but the programme doesnt like it!

Thanks for your help again!

 

[Galaxiom]

This is the new error message i am getting>>>>>'Microsoft Access cant find the field 'F_Order_line_subform'.

This isnt a field though it is a form?

It isn't a form it needs but the subformcontrol whose SourceObject property is the name of form object that is displayed as a subform. (By default, the wizard gives the form the same name as the control but it doesn't have to be.)

Access looks first for a control and then for a field so in the error it is referred to as a "field".

This is because the reference follows a heirarchy of defaults.

Me.somename

can refer to (in heirarchical order) either of these:

Me.Form.controlname
Me.Form.Recordset.fieldname

 

[joner89]

Hi Galaxiom

So Access looks for a control then a field..

I was told my JANR that the Me refers to the mainform where the subform(F_Order_line_subform) i am trying to access is contained.

Therefore the code should be.

Me(F_Order << Mainform).F_Order_line_subform(subform control for subform on mainform).RecordSet.ProductID( Field on the subform i want to select).

Im getting two types of erorr either the 'Microsoft Access cant find the field or data or method member not found?

I really dont get why it isnt working.

The main form is F_Order.

On here is the sub form F_Order_line_subform.

This subform contains a field product ID i want to access.

Thanks for your help again guys!

 

[JANR]

I think we have the order wrong here:

[COLOR=red]Me!Product_Catalogue.Form.ProductID[/COLOR] = [COLOR=blue]Me![/COLOR][COLOR=blue]F_Order_line_subform.Form.ProductID
[/COLOR]

Target = Source

The right side of the equalsign should be the source and the left the target. You'll need to flip this around, also where exactly is the ClickButton? is it on the main form or on the subform "Product_Catalogue"

JR

 

[Galaxiom]

Me does not refer to a main form. It refers to the form that "owns" the module.

From a form module you can refer directly to the control on that form. The display of that form inside a subform control makes no difference to the meaning of Me.

Me.controlname
from the module attached to a form used as a subform is the same as the cannonical reference:
Forms.mainformname.subformcontrolname.Form.controlname

 

[joner89]

Hey JANR

Ok so my new code is as follows...

[COLOR=red]Me!F_Order_line_subform.Form.ProductID[/COLOR] = [COLOR=blue]Me!Product_Catalogue.Form.ProductID[/COLOR]

Target = Source

So the source is my Product_Cataogue sub form and the target to copy the ProductID to is my F_Order_line_subform.

However i am getting the same error 'cant find the field Product_Catalogue referred to in your expression.

The button itself is in the subform Product_Catalogue....oops have a feeling this may be the problem!

Thanks again!

 

[JANR]

Now we are close:

[COLOR=#ff0000]Me!F_Order_line_subform.Form.ProductID[/COLOR] = [COLOR=blue]Me.ProductID[/COLOR]

JR

 

[joner89]

Hi JR

Im guessing the source didnt need to look for the form since the button was already in there. Hence the Me.ProductID. I think i understand that part so thanks!

I tried the code you gave me. Its now saying 'it cant find the field F_Order_line_subform referred to in your expression. Do you think this is because its looking for this subform in the subform Product_Catalogue?

Jay

 

[JANR]

From the VBA of a subform to another subform on the mainform:


Code:
Me.Parent.subformcontrol.Form.controlname = Me.controlname

From an earlier post, so:

[COLOR=#ff0000]Me!Parent!F_Order_line_subform.Form.ProductID[/COLOR] = [COLOR=blue]Me.ProductID[/COLOR]

I assume that the subformContainer Name is "F Order line subform" ?? just to double check.

JR