Acquiring a field value from a currently selected record in a subf

[Dan_S]

Dear All,

I'm working on a data entry system whereby I create a data entry dialogue
box to enter information regarding staff going on courses. My main form
contains the staff number I need, and the course ID can be found in a
sub-form, shown as a continuous list of courses.

What I'd like to do is pull the course ID of the selected record from the
sub form to my data entry dialogue box. I can get the staff number with ease,
but the control source expressions I've used for the course ID don't appear
to work.

Thanks in advance!

Kind Regards,

Dan

 

[Danny J. Lesandrini]

Dan:

I'm not sure I understand your question, but if it's as simple as the subject
of the post, "How do I read the selected record in a subform?" then this is
how it may be done ...

Dim lngID As Long
lngID = Me!objExampleSubform.Form!txtExampleID

In case it isn't obvious, the subform name (objExampleSubform) should
be replaced wtih the name of the control embedded on the main form,
not by the name of the subform, which for my example would probably
follow the naming convention, sfrmExampleSubform. Also, I'm accessing
the control on the subform named txtExampleID, not the field named
ExampleID that is part of the subform's underlying recordset.

Clear as mud? I didn't mean to make it complicated, but always remember
to reference the controls by their control names.

 

[Dan_S]

Thanks Danny. It's taking me a few reads to understand your response, but I
think I'm getting there!

So, rather than directly referencing any recordset or subform directly, we
reference those objects as they appear on the form itself...right, that makes
sense!

The subform with the list of courses is a continuous form, showing all of
the courses that member of staff can take. What I want to do is allow the
user to select a record from that list, and then when I click a command
button to open up the data entry form, it copies the ID of the course
selected to the relevant text box.

Is that what your code will do? I've only used control sources so far, and
Macros to open the data entry form.

 

[Danny J. Lesandrini]

Dan:

Yes, I think you're on the right track. Just remember the syntax for referencing
a value on your subform is ...

Me!Subform.Form!Control

Use bangs (!) in front of your control names and a dot (.) in front of the Form
keyword, like this Me!________.Form!________

This assumes there is a single selection in the subform, the currently selected row,
that you want to evaluate and act on. Given that you can extract the ID number
you need, from there you can open an additional form filtered for that ID.

 

[Dan_S]

So am I right in thinking that the Control part of the expression is the name
of the field that I'm trying to get data for?

In my case, the syntax looks like this: Me!Child6.Form![Course ID]

I've tried this, but Access tells me that this is an invalid control source,
and wants me to edit it.

 

[Danny J. Lesandrini]

Is the text box that contains [Course ID] named "Course ID"?
Try renaming it to txtCourseID with no space and use that handle.

 

[Dan_S]

Thanks very much for your help, Danny. I was still getting stuck with the
#Name? problem, but I did a bit of fiddling with your control source, and
came up with a working solution. For me, this appears to work as required:

=Child6!txtCourseID

This is the control source of the text box I wanted to populate with the
data, and it seems that the '=' is a required character for this purpose.

We got there in the end! Thanks for pointing me in the right direction.

Kind Regards,

Dan

Is the text box that contains [Course ID] named "Course ID"?
Try renaming it to txtCourseID with no space and use that handle.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

So am I right in thinking that the Control part of the expression is the name
of the field that I'm trying to get data for?

In my case, the syntax looks like this: Me!Child6.Form![Course ID]

I've tried this, but Access tells me that this is an invalid control source,
and wants me to edit it.