CodeContextObject - how to refer to "current" record/fields?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to build a lookup RecordSourceType function that will return
varying results depending upon the value in another field in the record that
is "current" at the time when the lookup function is executed. I have gotten
this far (simplified code placed within the lookup function):

Dim S As Object
Set S = CodeContextObject ' "S" is table containing combo boxto be filled
debug.print s!FieldName ' or any other property of S

This returns the data value in <FieldName> of the first record in table "S".

I want to be able to fetch the value of <FieldName> in the record that the
user is currently working.

I've done a lot of research, inlcuding here on the MS site, without finding
a clue.
I'm sure that it must be possible and probably simple, but ... ??? NOTE
that the function will be called from a table. not from a form, if that makes
a difference.

Thanks,

Leonard
 
Perhaps you can explain why you want to do this in a table, rather than a
form.
Tables don't expose nearly as many properties or events as forms.
And in general, pretty much all the experienced developers I know prefer
*not* to use the lookup function available in tables.
 
Thanks for the perceptive response. Excellent question!

In fact, since posting, I realized that I needed to create a form in
datasheet view, which I did and have learned this:

(code placed within the RowSourceType function)

dim f as form
set f = CodeContextObject
debug.print f.CurrentRecord '>> shows current record number - hooray
debug.print f!FieldName '>> still shows <FieldName> value in record
#1 ;^(

So, my question is: how does the code in a RowSourceType function access
the values in specific fields in the parent form's "CurrentRecord"?
 
Any code in the form's module can refer to the value currently displayed in
any control on the form, simply by using that control's name.
If you want to be super-explicit, you can use this syntax:
Me.MyControl.Value
 
Thanks again. I appreciate that you have stayed with me. I tried that
approach pretty early in the game. Unfortunately, I've unable to get any use
of "me" to compile when included in my row source function.

I get "Compile error: Invalid use of Me keyword"

when the combobox on the form is pulled down. (This is a form in datasheet
view)

Still trying to find out:

In a row source function, which is designed to deliver a list of options to
a combobox on a datasheet form- a list that will be customized depending upon
the value in another field in the same record on the form - how does one
construct a reference to the independent field/control value???

L
 
Problem solved: I had placed the record source function in an INDEPENDENT
module, whereas it should have been in a FORM module. Amateur's dumb mistake
but a valuable learning experience.
 
"Me" refers to the Form in whose Form Module the "Me" is used. It can't be
used in a Standard Module or a Class Module, or an Expression used in the
Properties of any object.

Row Source can refer to a Table, Query, SQL Statement, or a Value List.

Just as an aside: I do not include either datasheets nor forms in datasheet
view in any of my developed applications. There are just too many
opportunities for user error when using datasheets.

Larry Linson
Microsoft Access MVP
 
Back
Top