Reference to Form Field

[gray]

Hi

I'm struggling with yet another syntax problem... I want to reference a field in a form/subform recordset that has a table join in it's recordsource...

SELECT * FROM My_Table WHERE Unique_no = Forms!Addrs_Dtls_Form.Addrs_Dtls_SubForm.Form![TBL1.Parent_Unique_No]

If I keep the square brackets in, I get an invalid bracket error... if I take them out I get the parameter request prompt for
Forms!Addrs_Dtls_Form.Addrs_Dtls_SubForm.Form!TBL1.Parent_Unique_No

Derrr....I've tried every permutation of brackets I can think of..... Any idea how this is done please? The form uses an ADODB Rs, incidentally.

I'm referencing this in the controlsource of a combobox in the subform itself so maybe there's a better shortcut? The subform is in datasheet view.


Thnx

 

[jdraw]

You might try this (untested)

"SELECT * FROM My_Table WHERE Unique_no = " & Forms!Addrs_Dtls_Form.Addrs_Dtls_SubForm.Form!TBL1.Parent_Unique_No

 

[gray]

Hi

Thanks for the reply.... I've give that a whirl but no joy...I think support for table aliasing in Access is unbelievably poor.. it's driven me to distraction.....

I've been avoiding this for months but I think the only way to get around all these problems is to give every column in the joined tables their own alias... so...

TBL1.Parent_Unique_No AS TBL1_Parent_Unique_No,
TBL2.Parent_Unique_No AS TBL2_Parent_Unique_No

....etc...

Let's see, three joined tables x 30 columns ish each...
then run through the several thousands of lines of code and controls and replace all the references... multiply that by the number of main forms.... should only take a month or so

RDMS... I think not!

cheers