Access, copy field from subform to subform?

[Turnip12]

I have a value in one subform that I need to copy to another subform. I've been copying values from the from to a subform by using something along the lines of:

strFormValue = txtFormField.Text

Me!frmSubForm.SetFocus
DoCmd.GoToRecord, , AcNewRecord

Me!frmSubForm!txtSubFormField.Text = strFormValue

But, now the value I need is in another subform and I'm stumped. Access won't recognize the field in the subform. I tried using similar code but I can't get Access to recognize the field on the source subform. Only difference is that I'm not including the DoCmd.GoToRecord... line. I can set the focus to the source subform, but I can't get to the actual field in the subform.

Ok now to try and help clarify all this garbage. I have one form for data entry, it's linked to a table of current values. The calculations are performed in a query and a subTotal is displayed in a subform (I'll call it source subform) I have another table with past values, a history table, so when the enduser clicks on a button in the form, select values from the form are copied into a subform (destination subform) which is linked to the history table. But, I need to copy the subtotal from the source subform into the destination subform. If anyone was able to understand any of this, and has some ideas, I'd love to hear them. Thanks.

[Turnip12]

Ok, got it to work part of the time with

dblSubTotal = CDbl(Me!sfrmSubTotal!txtSubTotal.Text)

But wouldn't mind hearing suggestions if you have them, my code is extremely sloppy, not to mention the rest of my database design.

[ctaylor]

VBA expression builders cannot build expresions that draw from controls on a sub form. The reason for this is that often times command buttons are placed on the form that change the form displayed within the subform window on the superform.

(Superform = parent form containing a subform in this instance. The term is my own and used for clarification. It is not access-specific language.)

You can write your VBA code to force the code to go to controls on the subform by writing your code instructions without the assistance of expression builders. From your second post I'm seeing that you have already figured this out. Personally, I like to avoid the use of the Me! code instruction when dealing with forms and subforms because I just don't trust Access to think that the form it thinks is current is the same one I think is current. In these circulstances I write the full code out, something along the lines of

[Forms]![frmFormName]![sbfrmSubforms]![txtTextBox].value = [Forms]![frmFormName]![cboComboBox].value
[Forms]![frmFormName]![sbfrmSubforms]![txtTextBox].requery

(This example assigns the value displayed in the combo box of a superform to a text box in a subform and then requerys the text box so that the value visually displayed on screen is the new value just assigned.)

You get the idea. I have nested a subform inside of a subform and made click events on the lowest level subform work by using explicit declarations as demonstrated above.
What you may want to do is use the .requery property for the controls in the subform to force the updated value to be displayed. You can use events like OnCurrent, OnChange, OnClick etc on the superform to force values to change in a subform.

I hope this helps you think this through.

[Turnip12]

Thanks for the reply, that's kind of the path I'm trying to learn, then take, but is there a way to simplify the code? I have somewhere along the lines of 50 textboxes, I was hoping I could create an array of all the names (they're the same names in both subforms) and then loop through to hit all of them, unfortunately it looks like this isn't an option in VB?

[ctaylor]

BRUTE FORCE APPROACH
=======================
What you could do then is use some intermediary pop-up forms with a sequence of events like this:

1 - [frmMainOrderEntry]![cboSupplierName] has a click event, the click event opens another form [frmSupplierLookup] for example, with all sorts of text boxes on it with information about the supplier

2 - [frmSupplierLookup] would be bound to a query that uses [frmMainOrderEntry]![[cboSupplierName].value as the query criteria, this will allow [frmSupplierLookup] to show detailed information about the supplier.

3 - When [frmSupplierLookup] opens, that would launch an OnOpen event where the following type of events could happen:

With [Forms]![frmMainOrderEntry]
[txtSupplierAddress] = [frmSupplierLookup]![txtLookUpSupplierAddress]
[txtSupplierAddress].requery
[txtSupplierPhone] = [frmSupplierLookup]![txtLookupSupplierPhone]
[txtSupplierPhone].requery
[txtSalesRep] = [frmSupplierLookup]![txtLookUpSalesRep]
[txtSalesRep].requery
End With

4 - At the end of the OnOpen event for [frmSupplierLookup] you would place instructions to close [frmSupplierLookup]. This could look like this.

[cboSupplierName] OnClick event could run code something like this:
docmd.close "frmSupplierLookup"


A MORE ELEGANT SOLUTION
======================
a more elegant method would be to change the source object property for the subform and then requery the subform so that it will populate with the information you want automatically. This would not enter the information into a new record for each sales order, but would allow for the supplier name (or number depending upon how you define the properties for [cboSupplierName]) to be recorded in your database and visually display supplier details (assumably stored within a separate suppliers table in your database) to the user at run time.

Private Sub cboSupplierName_Click()
On Error GoTo Err_cboSupplierName_Click
Dim strSubformName As String

strSubformName = "frmSupplierLookup"

With [Forms]![frmMainOrderEntry]
![frmSubform].SourceObject = strSubformName
![frmSubform].Requery
End With

Exit_cboSupplierName_Click:
Exit Sub

Err_cboSupplierName_Click:
MsgBox Err.Description
Resume Exit_cboSupplierName_Click
End Sub