creating a number of records by entering a number on a form

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I want to be able to enter a number on a form and have it
create that number of records in a child form. The records
will all be identical at first except for a numerical ID.
Other data will be entered into each record separately at a
later time.

Further, upon entering a new number at a later date, I
would like the records to continue numbering from where it
left off.

Is this possible? Super complicated?

I'm grateful for any help I can get.
Thanks,
Dan
 
Dan said:
I want to be able to enter a number on a form and have it
create that number of records in a child form. The records
will all be identical at first except for a numerical ID.
Other data will be entered into each record separately at a
later time.

Further, upon entering a new number at a later date, I
would like the records to continue numbering from where it
left off.

Is this possible? Super complicated?

I'm grateful for any help I can get.
Thanks,
Dan
Click to expand...

It's not hard to do, but I'd like to ask first about your reasons for
doing it, to make sure it's a good idea. If any significant part of the
records to be created will remain identical, you may want to reconsider
your table design. Could the duplicate part be split off as a separate
table, related one-to-many to a table containing the varying parts?
Since you refer to a child form, I guess that you're talking about a
main form/subform situation, in which the one-to-many situation may
already exist. Were you planning to duplicate information from the main
form? Normally you would not want to duplicate anything but the main
form's primary key in the subform records.

If you're satisfied that you do in fact need to create these multiple
similar records, where would the data for the duplicated fields come
from? Would it be hard-coded, or already present in controls on the
main form, or copied from elsewhere?
 
You may also number the new records by reading the previous high number
entered on the ID by using the DMax("[field]","table") function.
This way, you may continue to number new records with a unique incremental
number.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_
 
Well it can be done. How easy is a relative term.

The question I have is the same as Dirk. It sounds like you are
planning an poor table design, but from what you said, it is not possible to
tell. It also is not possible to tell what might be the best way of
accomplishing what you want.
 
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
 
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
Click to expand...

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 -----
 
Thank you Dirk,
I haven't had a chance to try implementing the code you
sent but when I do I'll get back to you with all my
questions (I haven't worked with the vb editor yet...)

dan
 
Back
Top