SQL RecordSource With Control Reference

[gray]

HI

I've a mainform/subform setup. I want to link them but I can't use the master/child links. I spent weeks converting them to use ADODB recordsets and when I try to add links I just get 'data providor could not be located'... I guess that master-child links only work with DAO?

I build and apply an SQL SELECT in the subform's open event and want to incoporate the relevant PK field from the mainform... so my subform recordsource wants to be in the order of.....

SQLLINE = "SELECT TBL1.* FROM My_Table1 WHERE Deleted=False AND TBL1.Addrs_ID = Forms!MyMainFrm.Form!ID"

MySubFrm.Recordsource = SQLLINE

The aim of course, is to get the sunform to keep step when the main form is moved to another record.

I've tried all manner of brackets, square brackets, exclamation marks, dot notations.... Salvador Dali could do no better....

Anyone know how this is done please? There's a quote in http://ss64.com/office/access.html that says

"If you are writing an expression as part of an SQL statement, then use an absolute reference".

But I can't get it working.... thanks

 

[jdraw]

This line

SQLLINE = "SELECT TBL1.* FROM My_Table1 WHERE Deleted=False AND TBL1.Addrs_ID = Forms!MyMainFrm.Form!ID"

may be part of your issue.

This is treated as one text string as you have written it.
What you need(untested) is to have Access use the value from
Forms!MyMainFrm.Form!ID

So try this
SQLLINE = "SELECT TBL1.* FROM My_Table1 WHERE Deleted=False AND TBL1.Addrs_ID = " & Forms!MyMainFrm.Form!ID

 

[gray]

Hi

Thanks for the reply. I tried the suggested syntax but the !ID does not get updated whilst moving thru' the mainform... I msgbox the recordsource out and the "TBL1.Addrs_ID =" always shows the same value... i.e. the number of the record at which the master form was at when the recordsource was built. ,,, I tried a requery on the subform but it still does not get updated...

 

[jdraw]

See if you can get the value in Forms!MyMainFrm.Form!ID and let us know what it is.

 

[JANR]

I build and apply an SQL SELECT in the subform's open event

I think one problem here is that the subform opens BEFORE the main form so your refrence to the main form would be invalid.

SQLLINE = "SELECT TBL1.* FROM My_Table1 WHERE Deleted=False AND TBL1.Addrs_ID = Forms!MyMainFrm.Form!ID"

You don't need the .Form part when refrencing your main form

Perhaps moving the code to your mainform's Current-event might get you there.

JR

 

[gray]

Hi

I added a button to another form with a click event calling Forms!MyMainFrm.Form!ID and it reported the value correctly...

Rather than try to do this in the Recordsource string, I think I'm going to try to apply an ADO filter on the underlying RS and then modify it from the main form current event.....

 

[gray]

Yes I switched to the filter idea and all us well.... more details at the bottom of:-

http://www.access-programmers.co.uk/forums/showthread.php?p=1126156#post1126156


Thnx