Subform problem

[Jerome]

Hello,

I am modifying an excising database for a acquaintance. now I have the following problem:

The database consists of two tables (TblA and TblB) and two forms (FrmA and FrmB). One of the two forms has a subform (SubA).

Form A is used to populate data from table A using listbox and a textbox. The user can double click a row, then form B is opened and the data in table A is showed in a better way (using bound textboxes that show data from table A).

This code is placed in the form record source of from B to pull the data out of table A.

SELECT TblA.*, TblA.ID FROM TblA WHERE (((TblA.ID)=forms!FrmA!ListA));

Using the recordnumber from table A additional data is pulled out of table B and showed in subform A.

but the subform does not load any data but gives a inputbox "parameter value". When I enter the recordnumber from table A it works. I have tried different things to fix this but without any good results. The subform settings:

The subform.sourceobject is set to is Table B

subform.linkmasterfields is set to:
SELECT TblA.*, TblA.ID FROM TblA WHERE (((TblA.ID)=forms!FrmA!ListA));

subform.linkchieldfields is set to:
SELECT TblA.*, TblA.ID FROM TblA WHERE (((TblA.ID)=forms!FrmA!ListA));

I've also tried:

SELECT TblB.*, TblB.ID FROM TblB WHERE (((TblB.ID)=forms!FrmA!ListA)); for the linkmasterfields and the linkchildfields, but this also does not work.

What is the problem (I know....... I am a Noob )

Best regards.

 

[boblarson]

The Master/Child links expect just a field name from each form -

So, if you have say RecordID which is in both the main table and the child table and it would be the primary key (in the main) and foreign key (in the child) then you just put

RecordID
RecordID

in both.

If the name is different - say RecordIDPK in the main and then RecordIDFK in the child you would put


RecordIDFK (under the Child spot)
RecordIDPK (under the Master spot)

does that help?

Do

 

[Jerome]

I've tried what you proposed but this does not work. Probably I don't understand it.......

Some additional information:

In table A the name ID is used as recordnumber. Field ID has a primary key. In table B there is also the field ID (numeric value, this is the recordnumber from the field ID from table A) and the field IDadd (primary key).

This is how table A looks like:

ID Name
1 John
2 Beth
3 Nathan
4 Bill

Table B can look like:
IDadd ID Extra
1 1 John is vegetarian
2 1 John drinks diet coke
3 1 John does not smoke
4 2 Beth does not smoke

If I select record 1 in table A using form A, form B should show :

John

and in the subform on form B:

John is vegetarian
John drinks diet coke
John does not smoke

(John is a good boy )

What should the subform.sourceobject be? Table B.

Thanks in advance and best regards.

 

[boblarson]

The source object is just the subform itself. The master and child links in this case would be

ID
ID



How about looking at a sample of a form with a subform. Maybe that would help?

 

[Jerome]

I fixed it

Both the tables have the field ID. After I renamed into Table1.ID and Table2.ID the problems where solved.

Thanks for the help.

 

[boblarson]

I would suggest in the future you don't just use ID as the ID. For example, if I have a Customers table and an Orders table, I would have

CustomerID - in the customers table as the Primary Key
OrderID - in the Orders table as the Primary Key
CustomerID - in the Orders table as the Foreign Key

Then, when linking a form subform you would just use

CustomerID
CustomerID

for both master/child and it would work fine.