ComboBox - RecordSource function question

[Guest]

Situation: Form in datasheet view; Columns are F1 and F2
f2 is ComboBox with Record Source Type set to "fnBuildList"
fnBuildlist will supply options to the F2 combobox,
The list supplied will depend upon the value in F1
fnBuildList is properly constructed for its purpose (proper Parameters,
select structure, etc.) and runs well except for the problem described below:

(assume user has activated the F2 pulldown in record #6, which calls
fnBuildList)

fnBuildList contains the following code:

Dim f As Form : Set f = CodeContextObject 'establish reference to working
form

Debug.Print f.Name, f.CurrentRecord '>>> this works as expected

debug.print f!f1 '>>> This prints value of F1 in record #1, not what
we want

--------------------------------------------------------------

I want to be able to access the value of field F1 in the current record.
Why does Access make this so diffficult?

Note: "ME!f1" and "ME.f1.value" will not compile in this function. They
generate error: "Invalid use of keyword 'me'" , so don't suggest that unless
you can tell me why it won't compile.

I've also tried using the screen object, with no breakthrough.

My question, restated:

In a Record 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???

Putting it another way: How do I access the value of F1 in record 6?



Even if I setfocus to f1 in record 6 (which I can do in the function) a
reference to f!f1 still returns the vlaue of F1 in record #1. This makes me
think that there must be a separate cursor for pointing into the table that
does not synch with the apparent cursor in the screen.


If it is not possible, then what event is triggered when the user points or
arrows to any field in record 6? I can't find such an event.


Leonard

 

[Marshall Barton]

Situation: Form in datasheet view; Columns are F1 and F2
f2 is ComboBox with Record Source Type set to "fnBuildList"
fnBuildlist will supply options to the F2 combobox,
The list supplied will depend upon the value in F1
fnBuildList is properly constructed for its purpose (proper Parameters,
select structure, etc.) and runs well except for the problem described below:

(assume user has activated the F2 pulldown in record #6, which calls
fnBuildList)

fnBuildList contains the following code:

Dim f As Form : Set f = CodeContextObject 'establish reference to working
form

Debug.Print f.Name, f.CurrentRecord '>>> this works as expected

debug.print f!f1 '>>> This prints value of F1 in record #1, not what
we want

--------------------------------------------------------------

I want to be able to access the value of field F1 in the current record.
Why does Access make this so diffficult?

Note: "ME!f1" and "ME.f1.value" will not compile in this function. They
generate error: "Invalid use of keyword 'me'" , so don't suggest that unless
you can tell me why it won't compile.

I've also tried using the screen object, with no breakthrough.

My question, restated:

In a Record 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???

Putting it another way: How do I access the value of F1 in record 6?

Even if I setfocus to f1 in record 6 (which I can do in the function) a
reference to f!f1 still returns the vlaue of F1 in record #1. This makes me
think that there must be a separate cursor for pointing into the table that
does not synch with the apparent cursor in the screen.

If it is not possible, then what event is triggered when the user points or
arrows to any field in record 6? I can't find such an event.

Where is the function? You should be using Me instead of
CodeContextObject, but since that is invalid, it implies
that the code is not in the form's module. I don't know
enough about your application to guess where the value of f1
is coming from, but it sure seems that it is not coming from
the form with the combo box.

If the function is declared in a standard module, then you
should either move the code to the form's module or use a
helper function in the form's module to call the function
passing the form object as an argument.

 

[Guest]

Marsh

Right on! Thanks for the thoughtful response. Last night, after posting my
question, I began seriously to consider why the ME keyword was being
rejected and I realized it was because my INDEPENDENT module had no ME to
refer to. As you point out, the function needs to be in a FORM module.

It's been a valuable lesson. Thanks for your help.

Leonard