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.
dbfromnewjerseyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

armchair_scouseCommented:
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]
0
Dale FyeCommented:
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?
0
dbfromnewjerseyAuthor Commented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dale FyeCommented:
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

Open in new window

0
dbfromnewjerseyAuthor Commented:
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?
0
Dale FyeCommented:
I see another error in there as well.  Try:

Private Sub cbo_Field1_BeforeUpdate(cancel as integer)

    Dim strField1 as string
    Dim strMsg as string

    'Check to see whether the subform has any records
    if me.[subformControlName].Form.recordsetclone.Recordcount = 0 then Exit Sub

    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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbfromnewjerseyAuthor Commented:
Thank you.
0
Dale FyeCommented:
glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.