Reference a value in a subform

To refer to the control on the subform, you need to use the following construct:

[Forms]![MainFormName]![SubFormControlNameNotTheSubformName].[Form]![controlName]

So if I had frmCustomers, with another form called frmOrders, and I used the subform control on frmCustomers to display frmOrders, I would need to use the name I have given to the subform control, in order to refer to a control on that subform.  Imagine I called the subform control frmsubOrders, then to refer to the txtOrderID control, I would use:

[Forms]![frmCustomers]![frmsubOrders].[Form]![txtOrderID]

Well, the value of [Field1] on the subform is likely to change, depending on which record is displayed in the subform (datasheet).  Do you want the value for the first record in the subform, or the selected record.

How do you want to retrieve that value?  Do you want it to be the control source of a control on your main form, or are you trying to determine the value in the code of a form, somewhere?

I haven't read through everything you both wrote in detail but to answer one of the questions you had:

Lets say there are query results currently displaying in the subform.

I have a combobox on my main form.  That combobox contains all possible Field1 values (the combobox is tied to a different table than the one that is queried to display the data in the subform). If the user makes a selection from the combobox that is different than what is currently displayed in Field1 of the subform, I want to do something (display a message box notifying the user (s)he's about to select a different value, etc).

Actually, Field1 of the query results will all contain the same value. For example, if "New York" records are currently displayed, the subform may look something like this:

Field1   Field2       Field3       Field4
NY         whatever  whatever  whatever
NY         whatever  whatever  whatever
NY         whatever  whatever  whatever

Now let's say the user select "CA"  (for California). Before doing anything else, I want to compare the combobox value to the value in Field1 of the subform to see if they're the same or different.

The problem I'm having is when I try to reference the Field1 textbox on the subform, it's returning the the first value in Field1 of the table itself (which may be, say AK for Alaska).  I want to reference the value in the subform (the results of the query); not the table. Like I said, the values in Field1 of the query results will all be the same because the query "filters" data by state, which is what Field1 represents.  Since the subform is in datasheet view, there are multiple rows. I don't care which row is referenced. I'm just trying to reference any value in Field1.

OK, so what you really want to do, is use the main forms cbo_Field1_BeforeUpdate event to check to see whether the newly selected value in that combo matches with what is currently displayed in the subform.  And if they don't match, you want to present a message to the user and allow them to either cancel or continue with the selection.  Is that correct?

Private Sub cbo_Field1_BeforeUpdate(cancel as integer)

    Dim strField1 as string
    Dim strMsg as string

    strField1 = me.[subformControlName].[Form]![Field1] 

    if me.cbo_Field1 <> strField1 then
        strMsg = "You have selected a State value different than that displayed below!"
        if msgbox(strMsg, vbcritical + vbOKCancel) = vbCancel then
            Cancel = true
            exit sub
        else
            'this may not be necessary if the subform is linked to the main form on Field1
            me.[subformControlName].Form].requery
       end if
    end if

End Sub

Select allOpen in new window

That seems to be it except for one thing.

I'm getting a Run-Time error 94.  Invalid use of Null.

This is because when the form is opened up, the subform will be blank, as will also be the combobox.

The subform won't be populated (query won't be run) until a combobox selection is made.

Because of this, I'm getting an error on the strField1 line.  Any way to handle if the value in Field1 of the subform is null?

Thank you.

glad I could help.