Report Open Select Case

[haydenal]

I have 2 text boxes on a report. I am using a DLookup on the first text box to get a value. I would like to consider this value when determining the control source for the second text box. I thought I would do a select case on the first text box, in the report open event, to do this but get Run-time error '2427': You entered an expression that has no value." for the Select Case Text1.Value. Is there a more appropriate way to do this?

 

[pbaldy]

You would probably have to repeat the DLookup, as the values are not yet available in the open event.

 

[haydenal]

If I set the control source of text box 1 to "=DLookup("Description", "Self_Directed_Activity_Table", "ActivityCode=" & (DLookup("SD1", "District_Priority_Table", "Beat='" & Left([Beat].Value, 2) & "'")) & "")" the value of text box 1 is what I expect.

If I then switch up my select case from:
"Select Case Textbox1.Value"

to:
"Select Case DLookup("Description", "Self_Directed_Activity_Table", "ActivityCode=" & (DLookup("SD1", "District_Priority_Table", "Beat='" & Left([Beat].Value, 2) & "'")) & "")"

I get "Run-time error '2454': Microsoft Access can't find the field 'Beat' referred to in your expression."

I don't get it. How can it find it, if I set the control source equal to it, but it can't find it if I'm trying to programmatically evaluate it?

 

[haydenal]

Would there be any reason not to do what I'm trying to do with a form as opposed to a report? I copied all the controls and pasted them on to a new form and everything I'm trying to do in a report works just fine. I'm not sure I want to mess around trying to get this report to work if I can achieve what I'm trying to do with the form. Thoughts?