Master/Child Links in Subform

[gray]

Hi

Got an interesting syntax issue. I have a linked Mainform/Subform set up. My main form is populated with an SQL Select statement in the recordsource... For all manner of reasons (mainly due to the possiblilty of a JOIN) I have to alias the column names in the Select by altering the "." notation to an underscore "_" .. e.g.

SELECT
TBL1.Unique_No AS TBL1_Unique_No,
TBL1.Item_Name AS TBL1_Item_Name
TBL1.Contacts_Unique_No AS TBL1_Contacts_Unique_No,
TBL2.Unique_No AS TBL2_Unique_No,
TBL2.Item_Name AS TBL2_Item_Name
FROM ((Addesses) AS TBL1
OUTER LEFT JOIN Contacts AS TBL2 ON TBL1.Contacts_Unique_No=TBL2.Unique_No)
WHERE blah blah

The Subform needs to be linked to the TBL1.Unique_No column.

Now here's the syntax query. If I put TBL1_Unique_No as the Master Field it works perfectly....the subform shows the relavant data.... BUT when I open the form I get the "Enter Parameter Value" box asking for "TBL1_Unique_No" ???

Thinking that Access might be looking under the bonnet at the real columns I tried putting "TBL1.Unique_No" but that still produces the Enter Parameter Value box and does not produce the correct data in the subform anyway.

Any ideas anyone please? Thnx

 

[Galaxiom]

TBL1_Unique_No will prompt since you have aliased the field in the query as:
TBL1_Contacts_Unique_No

 

[gray]

Hi

Unless I'm missing something, I have both...

TBL1.Unique_No AS TBL1_Unique_No,
TBL1.Contacts_Unique_No AS TBL1_Contacts_Unique_No

The Contacts_Unique_No is the foreign PKey in the Addresses table for the Contacts table....

The odd thing is that ,despite the parameter value box prompt, it works properly with master_link of TBL_Unique_No...

My mainform SELECT statement is built in the mainform-open event and then assigned to an ADODB Rs. The form's recordsource is then set to that ADODB Rs.... so it's probably happening because the subforms are opening before the main form at which point TBL1_Unique_No does not exist yet.

I tried deleting the links in design view and adding them manually in VB after the build of the main form's recordset... but I get 'Data Providor could not be initialised' when I try to add the master link....

 

[gray]

Hi

IN the end I opted to lose the master/child links and use ADODB filter on the subform managed by the mainform current event.... so in that event I have this

Dim CntrlBX As Control

Set CntrlBx = Me.My_SubForm_Control_Name
CntrlBx.Form.Recordset.Filter = adFilterNone
CntrlBx.Form.Recordset.Filter = "[Unique_No] = " & Me![TBL1_Addresss_Unique_No]
Set CntrlBx.Form.Recordset = CntrlBx.Form.Recordset
Set CntrlBx = Nothing

Wotks fine now..... thnx