Data entry Form - confused and lost

[Dave]

I have 3 tables: "Beads" containing a consistent 30 records,
"Samples"(Field:Sample Date) and "Results" (Field:Result). Beads has a
one to many relationship to Results, as does Samples. So Results is
effectively a linked table from the many-to-many relationship between
Samples and Beads.

In any case, I need a data entry form based on the sample with a
datasheet subform for Beads and Results. But I would like the Bead/
Result datasheet to show all 30 records with a blank spot to fill in
the results, rather than picking each of the 30 beads from a combobox
and entering the result. Is this even possible?

Thanks in a advance

Dave

 

[John W. Vinson]

I have 3 tables: "Beads" containing a consistent 30 records,
"Samples"(Field:Sample Date) and "Results" (Field:Result). Beads has a
one to many relationship to Results, as does Samples. So Results is
effectively a linked table from the many-to-many relationship between
Samples and Beads.

In any case, I need a data entry form based on the sample with a
datasheet subform for Beads and Results. But I would like the Bead/
Result datasheet to show all 30 records with a blank spot to fill in
the results, rather than picking each of the 30 beads from a combobox
and entering the result. Is this even possible?

Thanks in a advance

Dave

Try creating a query joining Beads to Results, but use a Left Outer Join:
select the join line in the query grid and choose the option "Show all rows in
Beads and matching rows in Result". This will show all 30 rows, whether or not
there is a matching result, and it will be updateable.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Dave]

Try creating a query joining Beads to Results, but use a Left Outer Join:
select the join line in the query grid and choose the option "Show all rows in
Beads and matching rows in Result". This will show all 30 rows, whether or not
there is a matching result, and it will be updateable.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thanks John,

I get the query, and it works. How then do I build the form though? If
I have the Sample on the main form and the Beads/Results as subform
the beads don't show up. Propably because of the master/child linkage.

Dave

 

[John W. Vinson]

Thanks John,

I get the query, and it works. How then do I build the form though? If
I have the Sample on the main form and the Beads/Results as subform
the beads don't show up. Propably because of the master/child linkage.

Dave

Well? What IS the linkage? What are the Recordsources of the main and subform?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Dave]

Well? What IS the linkage? What are the Recordsources of the main and subform?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Let me try to clarify things here.

I'll have 3 tables

tblBeads with PK and Beads field which includes 20 bead entries

tblSample with PK and Sample Date field

TblBeadSample as a join for the tblBeads and tblSample many-to-many
relationship with Beads FK, Sample FK and Result

The form, I imagine in my head, has a text box to input a new Sample
Date and a subform that will populate with all 30 beads awaiting the
"Result" for each bead. Like:

Bead Result
Bead 1 _______
Bead 2 _______
Bead 3 _______
....
Bead20 _______

Each sample will always have the 20 beads.

Thanks again,

Dave

 

[John W. Vinson]

tblBeads with PK and Beads field which includes 20 bead entries

tblSample with PK and Sample Date field

TblBeadSample as a join for the tblBeads and tblSample many-to-many
relationship with Beads FK, Sample FK and Result

The form, I imagine in my head, has a text box to input a new Sample
Date and a subform that will populate with all 30 beads awaiting the
"Result" for each bead. Like:

Bead Result
Bead 1 _______
Bead 2 _______
Bead 3 _______
...
Bead20 _______

Each sample will always have the 20 beads.

Thanks again,

Dave

Don't use an autonumber for the Bead ID if the numbers are meaningful, you
can't edit or control its value. If the Beads field is a unique, stable,
numeric ID, just use it directly as the PK.

You could have a Form based on Samples, with a Subform based on a query

SELECT tblBeads.Beads, tblBeadSample.Beads, tblBeadSample.SampleID,
tblBeadSample.Result
FROM tblBeads LEFT JOIN tblBeadSample
ON tblBeads.Beads = tblBeadSample.Beads;

As soon as you "dirty" the subform record by adding a value in Result it will
automatically fill in the bead number.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

You can display tblBeads.Beads and tblResult.Result

 

[Dave]

Don't use an autonumber for the Bead ID if the numbers are meaningful, you
can't edit or control its value. If the Beads field is a unique, stable,
numeric ID, just use it directly as the PK.

You could have a Form based on Samples, with a Subform based on a query

SELECT tblBeads.Beads, tblBeadSample.Beads, tblBeadSample.SampleID,
tblBeadSample.Result
FROM tblBeads LEFT JOIN tblBeadSample
ON tblBeads.Beads = tblBeadSample.Beads;

As soon as you "dirty" the subform record by adding a value in Result it will
automatically fill in the bead number.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

You can display tblBeads.Beads and tblResult.Result- Hide quoted text -

- Show quoted text -

Except that all 20 beads need to be displayed on the subform to match
up the result with the bead.

Thanks John for taking the time to help.

Dave

 

[John W. Vinson]

Except that all 20 beads need to be displayed on the subform to match
up the result with the bead.

That's exactly what the query I posted should do. Doesn't it?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Dave]

That's exactly what the query I posted should do. Doesn't it?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

No, it doesn't. Unless I'm doing something wrong, which wouldn't
surprise me.
The subform has only one blank row. When I go into the subform it will
automatically populate the sample ID but no beads are displayed.

Dave

 

[John W. Vinson]

No, it doesn't. Unless I'm doing something wrong, which wouldn't
surprise me.
The subform has only one blank row. When I go into the subform it will
automatically populate the sample ID but no beads are displayed.

Dave

Hrm. I'll try some experiments tomorrow. I would have expected it to work...

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com