Subform to display from another table

[Guest]

Hello,

I have a "Sample" table where my sample information is stored and a "Test"
table where the applicable tests are stored for each sample. They are linked
in a one-to-many relationship according to an autonumber sample ID.

My problem is that I want to display the list of applicable tests for the
selected record when navigating the Sample records on the form. I can't get
the subform to work properly. I'm still quite new to Access, but am learning.
Can someone give me some direction as to how I can accomplish this.

Also, with regard to subforms, is there a way to show the subform without
showing its record navigation buttons? I'd like it to appear like any other
form control, or set of controls.

Thanks for any help you can give,
=Ray=

 

[Guest]

Hi,

See below for my original post. As I've been trying to figure this out, it
seems like a query should do what I need to, but it isn't. Can someone help?

This is the SQL for the query that I have:

SELECT Tests.Test, Tests.SID, Samples.SID
FROM Tests INNER JOIN Samples ON Tests.SID = Samples.SID
WHERE Samples.SID=Tests.SID;

SID is the numeric Sample ID (the Primary Key for the Samples table)
The Tests table has a different primary key, Tests.TID, not referenced in
the query.

Why doesn't this work? Can I do this with a subquery? How would that look?
Thanks,
=Ray=

 

[Guest]

I am assuming you have the following

Samples Table:
SID- PK(AutoNumber)
Other fields....

Tests Table:
SID - PK(AutoNumber)
Other fields...
SampleSID

with the 1 to many relationship <Samples.SID>1---m<Tests.SampleSID> or
something similar.

With your parent form use a query or table to get the Fields from the
Samples table. Your subform should use a queryy or table to get the fields
from the Tests table.

 

[Guest]

Reply is inline....

I am assuming you have the following

Samples Table:
SID- PK(AutoNumber)
Other fields....
Yes.

Tests Table:
SID - PK(AutoNumber)
Other fields...
SampleSID

Yes, effectively. Just different names.

with the 1 to many relationship <Samples.SID>1---m<Tests.SampleSID> or
something similar.
Yes.

With your parent form use a query or table to get the Fields from the
Samples table. Your subform should use a queryy or table to get the fields
from the Tests table.

On the Data tab of the subform control your Link Master Field will be SID
from the Form and your Link Child Field will be SampleSID from the subform.

I had tried this, but the problem is that my subform shows all the possible
Tests, rather than just the ones for the specific SampleSID. It doesn't
update when you navigate through the parent form's records (the Samples).

My main Sample form is based on the Samples table, and the subform is merely
a listbox that is linked to the Tests table. But despite the fact that my
Master and Child Link Fields are set, it doesn't filter the table to display
just the tests for the currectly displayed Sample record.

Thanks,
=Ray=

 

[strive4peace]

Navigation buttons, Document Table Structure
---


Hi Ray,

"Also, with regard to subforms, is there a way to show the subform
without showing its record navigation buttons? "

go to the design view of the subform

turn the Properties on (View, Properties from the menu)

click in the upper-left where the rulers intersect to select the form object

Format tab: Navigation Buttons --> No

~~~

as for your question about the SQL, what is the structure of the 2
tables you are using?

in order to help you with your query, we need to know your data
structure. Here is something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Tests"
ShowFields "Samples"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForTable sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal,

See response inline.

Navigation buttons, Document Table Structure
---


Hi Ray,

"Also, with regard to subforms, is there a way to show the subform
without showing its record navigation buttons? "

go to the design view of the subform

turn the Properties on (View, Properties from the menu)

click in the upper-left where the rulers intersect to select the form object

Format tab: Navigation Buttons --> No

Okay, thanks.

~~~

as for your question about the SQL, what is the structure of the 2
tables you are using?

in order to help you with your query, we need to know your data
structure. Here is something you can do to document that for us:

Here is the table structure:

Tests
==========================
1 TID, 4 (Long), 4
2 SID, 4 (Long), 4
3 Test, 10 (Text), 50

Samples
==========================
1 SID, 4 (Long), 4
2 Sample ID, 10 (Text), 50
3 Processing ID, 10 (Text), 50
4 Location, 10 (Text), 50

The primary key for the "Samples" table is SID.
The primary key for the "Tests" table is TID.
They are related by the SID field from both tables.

I hope that helps!
Thanks for your reply.
=Ray=

 

[strive4peace]

Hi Ray,

when you say that the query is not working ... have you ascertained that
record do indeed exist that should match up? How are you poplulating
SID in the Tests table? Are you sure it is getting the correct number?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

LinkMasterFields, LinkChildFields
---


Hi Ray,

it occured to me that you may not have set the link fields for the
subform control

make a mainform based on your parent table, Samples

make another form, which will be used as a subform, based on the related
table, Tests

make sure SID is a control on both forms and that is also its control name

on the main form, put a subform control and set its sourceObject to the
tests form

1. go to the design view of your main form

2. turn on the properties window
from the toolbar: View, Properties

3. select the subform control
(NOTE: Just ONE click -- just get handles around it
The first click selects the control, the second click goes INTO the
control -- ie: into the subform itself)

4. click on the DATA tab in the properties window

here you will see the name of the object in your subform control and the
link fields

LinkMasterFields --> SID
LinkChildFields --> SID



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*