open form datasheet view with defined number of prenumbered blank records

  • Thread starter Thread starter jcz
  • Start date Start date
J

jcz

I am building a database to store data for biological sampling of fish.
I have a trip form with all the data for individual trips (includes a
calculated unique trip number that links the subforms to it). A
subform frmTripCatches that shows catch data (including species,
lifestage, TotalCatch and tripnumber) I would like to put a field in
the catch subform - was fish measured (yes/no). When yes is selected,
I would like for it to open the frmFishData form in datasheet view.
Where do I put the macro or expression to do this (and how do I write
it)?

When frmFishData opens, I would like it to fill in species code,
lifestage, and prenumber records from 1-TotalCatch (TotalCatch being
the number from catch record that triggered the form to open). Where
and how do I write these macros and/or expressions?

This will allow the user to have the prenumbered records that looks
like the paper datasheet in front of them and they can enter any data
that goes along with that particular fish without having to go through
the task of numbering each record and entering the same data many times
(i.e. if they caught 150 fish it would take forever to number 150
records and enter the same species and lifestage just so they can enter
lengths and weights for individual fish) I hope I explained this well.
Any help will be appreciated.
 
jcz said:
I am building a database to store data for biological sampling of fish.
I have a trip form with all the data for individual trips (includes a
calculated unique trip number that links the subforms to it). A
subform frmTripCatches that shows catch data (including species,
lifestage, TotalCatch and tripnumber) I would like to put a field in
the catch subform - was fish measured (yes/no). When yes is selected,
I would like for it to open the frmFishData form in datasheet view.
Where do I put the macro or expression to do this (and how do I write
it)?

When frmFishData opens, I would like it to fill in species code,
lifestage, and prenumber records from 1-TotalCatch (TotalCatch being
the number from catch record that triggered the form to open). Where
and how do I write these macros and/or expressions?
Click to expand...

You're probably going to want to have two fields that handle this: FishID,
an autonumber that uniquely identifies each fish, and FishOrder, the
1-whatever for that trip. You may find that the actual order of the fish is
unimportant, so you could probably eliminate that field, but if you want to
prepopulate it, on the currentrecord event for the frmFishData, you can put
code like Me.FishOrder = Me.CurrentRecord.

I wouldn't bother with trying to prepopulate the records based on
TotalCatch. In fact, I'd calculate TotalCatch from the number of records
input into frmFishData. As the user moves through the form adding new
records, there will always be a blank one at the bottom to add.
This will allow the user to have the prenumbered records that looks
like the paper datasheet in front of them and they can enter any data
that goes along with that particular fish without having to go through
the task of numbering each record and entering the same data many times
(i.e. if they caught 150 fish it would take forever to number 150
records and enter the same species and lifestage just so they can enter
lengths and weights for individual fish) I hope I explained this well.
Any help will be appreciated.
Click to expand...

To carry default values forward from previously entered text, try something
like

Me.species.defaultvalue = Me.species.value

in the BeforeUpdate for the form.

You may also find comboboxes help for this.

HTH;

Amy
 
fish order does not matter as you said, but I just wanted to create an
entry page for the "simple" user that looked like the actual paper
datasheet that has fish number on it. FishNumber is used to create a
unique "JoinID" for that fish because other tables are linked to that
fish. Thanks for your help. I'm sorry if this is confusing.
 
I'd still keep an autonumber fishID for the join. If you want to provide a
number because the average user needs them, you don't need to actually store
that information in the database---you can create an unbound control that's
populated as I described above.

Sorry if I conveyed the impression I was confused. Your problem is actually
fairly typical.

HTH;

Amy
 
I would design the database so that you have a unique Id autonumber field in
the TripCatches table such as "TripCatchId". Then I would create (if you
haven't done so already) a "Fishes" table in which you would store the
measurements of each fish. This "Fishes" table would have a "TripCatchId"
field to relate to a record in the "TripCatch" table. Then you wouldn't have
to store the same species and lifstage for each fish in the same TripCatch.
The "TripCatch" table already has the information, just reference it. I
would also do what Amy suggested in her reply and set a "FishOrder" field to
Me.CurrentRecord. I tried it and it works just fine. When I first started
writing programs I wrote them for computer illiterate people and found in
alot of cases it worked better to get them used to using a computer and used
to how a computer typically works then to try to create a program that was
just like the paperwork system that they were used to. If you really want to
open the form with pre-filled numbers, you can write a for next loop in code
(ex. for i = 1 to TotalCatch next i) to add records to the fishes table. You
can either open a recordset or use the "DoCmd.RunSQL" method to add a record
during each pass of the loop, setting the value of "FishOrder" to i and
setting the TripCatchId. Either way, you can trigger the "frmFishData" form
to open in the AfterUpdate event of the "Fish Measured yes/no" control. Just
write code to check if the value is "yes" and if so use the "DoCmd.OpenForm"
method to open the form. Hope this helps.
 
I agree with Amy. I tried setting Me.CurrentRecord equal to Me.FishOrder and
it works just fine. If you really want to populate the numbers ahead of
time, you could, before the FishData form is opened, use a for/next loop (ex.
for i = 1 to TotalCatch) and either open a recordset or use the
"DoCmd.RunSQL" to add a record to the "Fishes" table for each pass through
the loop, setting "FishOrder" to i. This sounds unnessarily complicated
though. I think her idea would work just fine. I would, however put a
"TripCatchId" field in the "Fishes" table. This way you won't store the same
information (species, lifestock, etc.) in both tables, just reference the
information. Hope this helps.
 
Back
Top