Avatar of Scott Palmer
Scott Palmer
Flag for United States of America asked on

Obtaining the value of a field in a subform

I want to access a field in a subform like I do with a List Box.  With the List Box I reference it like this.

txtLastName = lstListBox.Column(2)

Is there a way to do that in a subform?  The subform is name "chlAddEdit" and the field I want to access is named "ClaimNumber".  

Thanks,
Scott
Microsoft DevelopmentMicrosoft Access

Avatar of undefined
Last Comment
Scott Palmer

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Like so:
dim x

x = Me.chlAddEdit.Form.ClaimNumber
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

The syntax is:

From within a main form, to reference a control in a subform ..

Me.YourSubformName.Form.ControlName

mx
Dale Fye

The syntax to access the value of a control on the subform would be something like:

strClaimNumber = me.subformControlName.Form.txt_ClaimNumber

Where "subformControlName" is the Name of the subform control, not the name of the subforms SourceObject (although they may be the same).
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Correcting my typo:

Me.YourSubformControlName.Form.ControlName

" (although they may be the same)"
And they are the same by Default ... fyi.
Scott Palmer

ASKER
I copied the syntax but I am getting the error "The expression you entered refers to an object that it closed or does not exitst."

The only thing I can think of that might be affecting it is that the subform source object is added after I open the form and select some parameters.
Scott Palmer

ASKER
I have checked the names many times.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

The syntax is correct, So, something else is going on.

Confirming;
You have a Main Form, with a SubForm ?

"that the subform source object is added after I open the form and select some parameters."
At what point are you executing code that tries to reference the control on the subform?
Scott Palmer

ASKER
Yes I have a main form with a subform called "chlAddEdit"
I open the main form, add the parameters and click a button to execute this code:

Me.chlAddEdit.SourceObject = "Query.qryValidAnalysis"
Me.chlAddEdit.Requery

which populates the subform.  I then click on the subform and that executes the code to access the Claim Number field.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Also re:

and the field I want to access is named "ClaimNumber".  

Is ClaimNumber the Name of a Control on the subform ?
And is the Control Source also ClaimNumber ?
If so, rename the control to txtClaimNumber, then try:

dim x

x = Me.chlAddEdit.Form.txtClaimNumber


"I then click on the subform and that executes the code to access the Claim Number field."
Not following.  You 'click' on the subform to execute the code ?  What event is executing the code ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

Joe,

It doesn't look like the OP is using a form as the subform, but rather a query.

Gotta run, will check back in later.
Scott Palmer

ASKER
I am getting confused by what a Control and a Control source are.

I have attached a screen print of the form.  I initially gave you "ClaimNumber" as the field name as an example, the real field name is Legacy_Claim_ID which I have been using in the code:  txtClaimNumber = Me.chlAddEdit.Form.Legacy_Claim_ID

If Control is the name of the field in the subform then Legacy_Claim_ID is the Control.  txtClaimNumber is a text box on the main form.

The code is executed on the 'On Enter" event on the subform.  Which I guess begs the question can you use this code with "On Enter" event on a subform?
Subform.docx
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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
or
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.
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
Scott Palmer

ASKER
What other option is there besides using a query as the source object?  My problem here is that I need to use the subform to be able to add data to the fields.  Otherwise I would just use a listbox populated by a recordset.  

Basically what I need is something like a list box that I can add data to many rows that will save to the table.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Palmer

ASKER
DatabaseMX, thanks for your work on this.  I have decided do it another way.

Scott