auto entry of data

[Guest]

I have two tables. First table contains fields lot and grade.
Second table contains fields lot, sample, test, and value.

I have a form for entering data. The main form contains the lot and grade
entry and a sub fomr handles the sample, test and value entry. For each lot
I may have any number of samples from 1 to 5 and for each sample I would have
any number of tests from 2 to 5. HOWEVER, I always have the same number of
tests for each sample in a single lot.

What i would like to do is place two unbound text boxes in the main form
that the user would enter # of samples and # of tests per sample. When the
user switches to the subform it would already be populated with the sample,
and test fileds and he/she would only need to fill in the value for each item.

Any ideas?

 

[strive4peace]

Here are the tables I see from your explanation

*Lots*
LotID, autonumber
Lot, text or number
Grade, text

*Samples*
LotID, long integer
SampleID, autonumber
SampleNum, number -- integer

*Tests*
SampleID, long integer
TestID, autonumber
TestNum, number -- integer
Value, number

The Primary Key in each table will be the Autonumber field

In Samples, define a Unique index on the combination of
LotID and SampleNum

In Tests, define a Unique index on the combination of
SampleID and TestNum

The main form will be based on Lots

The first subform, fsubSamples, will be based on Samples
LinkMasterFields = LotID
LinkChildFields = LotID

you will create a calculated control on the mainform

Name --> SampleID
ControlSource --> = nz(fsubSamples.form.SampleID)

The second subform on the mainform, fsubTests, will be based
on Tests
LinkMasterFields = SampleID
LinkChildFields = SampleID

on the mainform, you will have 2 unbound controls:

Name --> NumSamples
Name --> NumTests

put a command button on the mainform
Name --> MakeSamplesTests
Caption --> Create Samples and Tests

OnClick Event Code -->

'~~~~~~~~~~~
if isnull(me.NumSamples) then
msgbox "You must fill out the number of samples", _
, "Missing Data"
exit sub
end if

if isnull(me.NumTests) then
msgbox "You must fill out the number of tests", _
, "Missing Data"
exit sub
end if

'save main record
if me.dirty then me.dirty = false

if me.newrecord then
msgbox "You do not have a current record", _
, "No Current Record"
exit sub
end if

dim strSQL as string, i as integer, j as integer
dim mSampleID as long
for i = 1 to me.NumSamples
strSQL = "INSERT INTO Samples " _
& "(LotID, TestNum) " _
& "SELECT " & me.LotID & ", " _
& i & ";"
currentdb.execute strSQL
currentdb.tabledefs.refresh
mSampleID = dMax("SampleID", "Samples")
for j = 1 to me.NumTests
strSQL = "INSERT INTO Tests " _
& "(SampleID, TestNum) " _
& "SELECT " & mSampleID & ", " _
& j & ";"
currentdb.execute strSQL
next j
next i
currentdb.tabledefs.refresh
DoEvents
me.fsubSamples.form.requery
me.fsubTests.form.requery
'~~~~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Thanks for the very detailed response. The tables I have are structured a
little differntly, Perhapse I should re think this as well.
*lots*
LOT, text - Primary key
Grade, text

*QC RESULTS*
QC DATA ID, auto number - primary key
LOT, text - linked to LOT in *lots* table
Sample, long Integer
Test, long Integer
Result, double

I think I understand the code well enough to modify it for my purposes. I
will check back if I get stuck and thanks for your help.

 

[Guest]

WOOPS!

I have reached an error that I do not know how to rectify. the code is

strSQL = "INSERT INTO [LOT QC DATA]" _
& "(COMPOUND_LOT, DRUM, SAMPLE)" _
& "SELECT" _
& Me.LOT & "," & i & "," & j & ";"
CurrentDb.Execute strSQL

And I get a Syntax error when the last line trys to execute.

I mis spoke earler on the way the tables are set up. The [LOT] filed in the
QC results table is really COMPOUND LOT with the space. I though that the
underline in the code would address this but it does not seem to help. Any
ideas?

 

[strive4peace]

you're welcome

Aside from not using a text field to link your data, it is
also important not to use a "user" field for linking.

Your results table looks like it will have repeated values
for Sample. Although there will be repeated values for
SampleID when the tables are normalized, this is different.

There may be times when you want to attach notes to a
sample, or other things you want to track about that sample
such as where it came from. You would not want to have to
repeat this information for each test record, you would want
to type it in once into the samples table.

Shouldn't your Lots table include LotDate?

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[strive4peace]

you wrote:

strSQL = "INSERT INTO [LOT QC DATA]" _
& "(COMPOUND_LOT, DRUM, SAMPLE)" _
& "SELECT" _
& Me.LOT & "," & i & "," & j & ";"

'~~~~~~~~~
& "SELECT" -->
& "SELECT "

& "(COMPOUND_LOT, DRUM, SAMPLE)" -->
& " (COMPOUND_LOT, DRUM, SAMPLE) "

you need a space delimiting each part of your statement

'~~~~~~~~~
Me.LOT & "," -->
"'" & Me.LOT & "', "

Since you are storing Lot as text, it needs to be delimited
with quote marks. If sample or drum is stored as text, the
same applies.

'~~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

WOOPS!

I have reached an error that I do not know how to rectify. the code is

strSQL = "INSERT INTO [LOT QC DATA]" _
& "(COMPOUND_LOT, DRUM, SAMPLE)" _
& "SELECT" _
& Me.LOT & "," & i & "," & j & ";"
CurrentDb.Execute strSQL

And I get a Syntax error when the last line trys to execute.

I mis spoke earler on the way the tables are set up. The [LOT] filed in the
QC results table is really COMPOUND LOT with the space. I though that the
underline in the code would address this but it does not seem to help. Any
ideas?

:

Thanks for the very detailed response. The tables I have are structured a
little differntly, Perhapse I should re think this as well.
*lots*
LOT, text - Primary key
Grade, text

*QC RESULTS*
QC DATA ID, auto number - primary key
LOT, text - linked to LOT in *lots* table
Sample, long Integer
Test, long Integer
Result, double

I think I understand the code well enough to modify it for my purposes. I
will check back if I get stuck and thanks for your help.





:

 

[Guest]

Yes I understand what you are saying about separating the samples and the
test tables. But I am very new to this, I decided two weeks ago to write a
database to track this info and I have NEVER written a database before. But
my guess is you konw that. In any case. With your help I got this last item
working.

I seem to have issues at times with naming of fileds and tables so that they
do not conflict. For future reference is there some type of generaly
accepted naming scheme that I could look to for reference?


Again thanks for all your help.

 

[strive4peace]

I am glad you got what you needed.

Before you build another database, here is a link with very
well-written articles on points to consider:

http://www.databasedev.co.uk/general.html

On naming, I like to keep names short and also make sure
that key fields are called the same thing in one table as
they are in another. Always start names with a letter (not
a number) and avoid using spaces and special characters
except for the underscore (_). You do need to make sure you
do not use reserved words (Date and Time are functions, Name
is a property)

Here is a list of RESERVED WORDS

http://support.microsoft.com/kb/q209187/


Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day