Dan said:
I appreciate the help. This is my first venture into
developing a database and I'm finding it none too easy.
I'll explain, as briefly as possible, the design and the
reason for wanting ...what I asked for.
We perform 2 experiments per week. They are called d1,
d2, e4, e5 etc. Each experiment starts with about 300-
400 pieces of plant tissue and requires periodic
maintenance. There are qualities we look for as we
maintain the tissue. These qualities arise over the next
20 weeks. The pieces of plant tissue with these
qualities are kept and given an identity. For example d2-
5, for the 5th individual to come from experiment d2.
The rest are discarded.
On any given day we may give identities to up to 30
individuals (although often far less) per experiment.
With so many experiments running at a time this number
multiplies. I would like to be able to enter a number in
a maintenance form and have it generate that many records
in a Table (I said child form in my last post, but meant
table.inexperience with the lingo). This is simply so
that we do not have to enter each individual ID one at a
time. At the time that the records are created the
individuals are identical but data will be collected for
them over time. This data will be entered into the same
child table.
I have my tables set up so that there is one general
table (Experiment information). The key is the
experiment name. A child table then tracks the
maintenance of each experiment (Transfer information).
Its keys are Experiment name and Date. The Experiment
information table has another child table that records
the individual information (Individuals Table). Its keys
are experiment name and Individual ID. This is the table
that I would like my form to create records in.
There is one other table (a child of the Individuals
Table) which I don't think is pertinent but .just in case.
Thanks for reading all that.
I'm anxious for any replies.
Dan
That sounds reasonable to me, Dan. I don't know whether you need to set
up a separate form for this process or not, so let's suppose you set up
the main ExperimentInformation form so that you can perform this task
directly from that form. If you need to, you can just translate these
instructions to a different form devoted to the process. The main thing
is that the form in question has a control containing the experiment
name, since that's the primary key of the Experiments table. I'll
assume that control is named "ExperimentName". I'm also assuming in the
code below that you want to insert into a table named "Individuals".
Put an unbound text box on the form named "txtAddIndividualCount" and a
command button next to it named "cmdAddIndividuals". The idea is that
the user will enter the number of new individuals to be created in
txtAddIndividualCount and then click cmdAddIndividuals to make it
happen. Code for the button might look like this:
'----- start of code -----
Private Sub cmdAddIndividuals_Click()
' Add records to the Individuals table for the current experiment.
' The number of new individuals to be created is specified by
' the text box txtAddIndividualCount on this form.
On Error GoTo Err_cmdAddIndividuals_Click
Dim db As DAO.Database
Dim strSQL As String
Dim lngLastUsedID As Long
Dim lngAddCount As Long
Dim lngID As Long
' Make sure we've got what we need to do the job.
If IsNull(Me.ExperimentName) Then
MsgBox _
"You must define and save the experiment " & _
"before adding individuals.", _
vbExclamation, "Undefined Experiment"
Exit Sub
End If
lngAddCount = Val(Me.txtAddIndividualCount & vbNullString)
If lngAddCount <= 0 Then
MsgBox _
"First enter the number of individuals to be added!", _
vbExclamation, "Count Needed"
Me.txtAddIndividualCount.SetFocus
Exit Sub
End If
If Me.Dirty Then
RunCommand acCmdSaveRecord
' Note: this will raise an error if the record can't
' be saved.
End If
' All seems well, so let's do it.
' Build a template SQL INSERT statement for adding
' individuals to the Individuals table.
strSQL = _
"INSERT INTO Individuals(ExperimentName, IndividualID) " & _
"VALUES('" & Me.ExperimentName & "', #)"
lngLastUsedID = _
Nz(DMax("IndividualID", "Individuals", _
"ExperimentName='" & Me.ExperimentName & "'"), _
0)
Set db = CurrentDb
For lngID = (lngLastUsedID + 1) To (lngLastUsedID + lngAddCount)
' Insert lngID in the SQL template and execute it.
db.Execute Replace(strSQL, "#", Str(lngID)), dbFailOnError
Next lngID
Exit_cmdAddIndividuals_Click:
Set db = Nothing
Exit Sub
Err_cmdAddIndividuals_Click:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_cmdAddIndividuals_Click
End Sub
'----- end of code -----
