Data control - linking 2 tables

[David Laplante]

Hi. I have a simple question about data controls and recordsource.
I have 2 data controls. Data1 is openned on the table "Team" and Data2 is opened on the table "Player".
I tried the following recorsource on data2:
SELECT Player_Name FROM Player WHERE Player_TeamNo = Data1!Team_No
The failing part of this recordsource is the WHERE clause. I would like to know to make such a referrence to the Data1 control.

Thanks in advance.

[smalig]

"SELECT Player_Name FROM Player WHERE Player_TeamNo = " & Data1.Recordset!Team_No


------------------
smalig
[email protected]
smalig.tripod.com

[David Laplante]

Thanks for your help but it's still not working. Maybe I'm doing something wrong.
Here is my original recordsource:
SELECT Beneficiaire_Name, Beneficiaire_PropositionNo FROM Beneficiaire WHERE Beneficiaire_PropositionNo = " & DataProposition.Recordset!Proposition_No
First, I guess you must know that i'm using this recordset at design time, on the data control recordsource property, not in code. This recordset gives me the following error: Syntax error in string in query expression 'Beneficiaire_PropositionNo = " & DataProposition.Recordset!Proposition_No'. I tried taking off the concatenation and it says Too Few Parameters, Expected 1.

Thanks again.

[smalig]

For sample I have two tables (table1 and table2).

table1
city code
-----------------------
berlin 1
stuttgart 2
bonn 3

table2
city street
-----------------------
berlin mannstr.
stuttgart karlsstr.
stuttgart erichstr.
stuttgart rugostr.
bonn -

And I want display all streets from table2 for city #2. Place two data controls on the form. And copy follow code to Form_Load. It must working.

Dim Db As Database, Rs As Recordset
Dim x&

    x = 2
    Set Db = Workspaces(0).OpenDatabase("d:\db1.mdb")
    Set Rs = Db.OpenRecordset("select city from table1 where code = " & x)
    Set Data1.Recordset = Rs
    Set Rs = Db.OpenRecordset("select street from table2 where city = '" & Data1.Recordset!city & "'")
    Set Data2.Recordset = Rs
    
    Debug.Print "Found " & Data2.Recordset.RecordCount & " for " & Data1.Recordset!city
    Data2.Recordset.MoveFirst
    Do
        Debug.Print Data2.Recordset!street
        Data2.Recordset.MoveNext
    Loop Until Data2.EOFAction
    
    Db.Close

Best regards.

------------------
smalig
[email protected]
smalig.tripod.com

[David Laplante]

No idea, smalig?
..This would really help me out!

[David Laplante]

This solution worked. But my question persist: How can I set up such a link between the two tables at design time? With absolutely NO code... (With the recordset property of the DataControl).

Thanks for your patience.

[smalig]

Sorry, David, but I don't understand why you don't like these code. Maybe I don't understand your question completely because english not my native language. Can you describe your problem once again? Maybe I know the answer.

Best regards.

------------------
smalig
[email protected]
smalig.tripod.com

[David Laplante]

Bah, don't blame yourself, english ain't my native language neither. Anyway, I'll try explain it another way...
On my form I have 2 data controls. In one of these data controls, I want to set the PROPERTY "RecordSource" (in the "DATA" node of the property sheet) to something like : select street from table2 where city = " & Data1.Recordset!city.

This just won't work. Gives some errors (that I described in my first or second reply on the topic).

I know it would work with the solution you gave me, but why should I use such coding when I can put it directly in the recordsource property of the data control....

That's it... thanks!

[Serge]

You cannot do this with data control. RecordSource poroperty expects a string value. And Data1 is not initialized yet. It means there's no way of knowing the particular field value yet. RecordSource property excepts Table Object, QueryDef Object or valid SQL statement.


Regards,

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

Serge

Software Developer
[email protected]
[email protected]

[David Laplante]

Thanks Serge, thanks smalig!

...Big help!