Link to home
Start Free TrialLog in
Avatar of Scott Palmer
Scott PalmerFlag 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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Like so:
dim x

x = Me.chlAddEdit.Form.ClaimNumber
The syntax is:

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

Me.YourSubformName.Form.ControlName

mx
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).
Correcting my typo:

Me.YourSubformControlName.Form.ControlName

" (although they may be the same)"
And they are the same by Default ... fyi.
Avatar of 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.
I have checked the names many times.
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?
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.
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 ?
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.
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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
DatabaseMX, thanks for your work on this.  I have decided do it another way.

Scott