Avatar of Jenkins
JenkinsFlag for United States of America asked on

Reference a value in a subform

I'm having trouble referencing a value displayed in a subform.

The subform displays query results of a table that is queried.

From the Access Objects window, my main form is named MyForm, the subform is named MySubForm subform, and the textbox on the subform I'm trying to check is named Field1.

The subform is displayed in datasheet view.

I'm trying to see what the first value in Field1 of the subform is displaying.  I'm not trying to see what the first value in the table is. I'm trying to check what is actually displayed in the subform.
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Andy Marshall

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


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:

Dale Fye

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Dale Fye

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
            'this may not be necessary if the subform is linked to the main form on Field1
       end if
    end if

End Sub

Open 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?
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

glad I could help.