Confused...whats new. Carrying a field over multiple forms.

[ConfusedA]

Hi there,
This question is probably going to sound more complicated than it actually should be, but I will attempt to make it as clear as possible.

I am attempting to make a simple database to track chain of custodies for laboratory samples.

What I have:
3 Tables: Table1- SiteLocation, SiteAddress, Site#
Table2- Some basic criteria fields, date, sitelocation
Table3- Specific Sample Information
3 Forms:
Form1- Listbox (SiteLocation...populated from table1), Textbox (Date)
Form2-a mixture of listboxes and textboxes with data, 2 locked textboxes for Sitelocation and Date to be posted <All information associated with Table2>
Form3- a subform located on Form2, with textboxes for sample criteria <Information associated with Table3>

What I need:
For the sitelocation and date information from Form1 to be able to carry over to form2 and form3, Note: form3 will have multiple records to be associated with one location and one date (the same as form1, and form2)

Why I need this:
To create a report which contains all the information gathered at a site on a certain day. I do not want to have the date/location require repeat entries by the user.

My Question: Is this possible? What is the best way to go about doing this?
Any help to point me in the proper direction would be appreciated.
Thanks for the help in advance.
ConfusedA

 

[Scooterbug]

I'll give this a whirl for ya

From what I gather, you want to track samples as the go from point A to point B. To do this, you want to use 3 tables. I would set it up like this:

tblSiteLocations
pkSiteID (Primary Key)
SiteNumber
SiteLocation
SitePhone
etc, etc

tblSampleInfo
pkSampleID (Primary Key)
SampleName
etc, etc

tblLocationTracking
pkLocationTrackingID (Primary Key)
fkSiteID (Foreign Key)
fkSampleID (Foreign Key)
DateArrived
DateLeft
LoggedBy
etc, etc

On one form, I would have all of the Sample Info listed. Once you have the sample information, use a combo box to select the where the sample has gone. Have a subform based off of the tblLocationTracking create a new record and fill in the info needed for the LocationTracking table.

Generating a report for all activity for a given site from here would be a matter of creating a simple query to show the data by Site Location.

Hope this doesn't sound too vague...I'm not the best of teachers when it comes to explaining things. I can do up a quick little db illustrating this if you would like.

 

[ConfusedA]

Haha well, you have the jist of my total plan, my current problem lies with the sampleinfo part of it. We take multiple samples from each site, but they go out as one set. So I'm trying to set up a report that will let me record multiple samples for each site by using a subform for each sample and a 'basic information' main form for the site information (for the recorded sample) but I need to be able to associate the time/site location (as put in to the main form) to the sample info (as put into the sub form) without retyping date/location multiple times for each sample.

Thanks for your input on the project as a whole, that is indeed what I am looking to do. For the most part I am capable of doing that, it's just this little snag that has me right now. Thank you for the mock tracking table though, I wasn't sure the best way to set it up.

 

[Scooterbug]

Well, off the top of my head there are two ways to do what you need to do. One, you could assign a batch number to each sample. The multiple samples from the same site would have the same batch number.

As for the wanting to cut down on the data entry, you could do the following. Once the samples are put into the database, you could have a form that lists the site info. Then, have a combo box that displays all the samples (either by individual sample or by batch number if going that route). You would go to the site record, and using the combo box, select the sample. Have a command button that will take that sample and assign the siteID to it along with the time and date. If you wanted all the samples to have the same time and date, you could have a text box on the form with that data and grab that info, or could use the now() function to record the time/date as the command button is pushed.

 

[ConfusedA]

Ok, I understand. Thanks for the help!