Using a Form to input multiple copies of a record

[enolan]

Hello AccessForums Community,
I am currently searching for a way to input multiple instances of the SAME information into a table by using a form.


For instance, in order to keep track of specimens, I must have a record of each single individual. Each specimen record contains a [collection number], [species], and [gender] (e.g.: 070607-01, Fallicambarus fodiens, Female) with the species and gender being selected from a drop-down box.
Ultimately these link back to sites where they were collected via a many-to-one relationship using the [collection number]...not that it matters.
Rather than input this same data 10 times, is there a way to input the appropriate data, select a number of iterations, and press a button to save these as 10 records?
Thank you all for your help in advance--this is driving me nuts.
-Joe

[jzwp11]

Welcome to the forum!

Absolutely nothing changes for each of individuals within the group? Not even a tracking number? If so, how would you distinguish them?

I would recommend using an unbound form with the text and combo boxes you mentioned. Add a button and the code behind that button would run an append query based on the number of iterations the user enters via a text box on the form.

The basic code in the on click event of the button would look something like this (air code, not tested). You will have to delineate values for text fields with single quotes and # signs for date fields. Numeric values do not need to be delineated.

dim mySQL as string
dim i as integer

mySQL= "Insert into tablename (field1, field2, etc) "
mySQL= mySQL & "Values (" & me.combobox1 & ", " & me.combobox2...")"

For i=1 to me.NameOfTextboxWithIterations

currentDB.execute mySQL dbfailonerror

Next i

[enolan]

JZWP11,
Forgive me for the brainfart, can you explain "unbound" in regards to the form? These specimens will be stored in jars, ultimately in a museum, and cataloging them via MSAccess is required (for reasons beyond my control). I don't agree with the logic of the State DNR, I just grin and bear it :-)
I'll give your code a try and report back by the end of the day. Thank you for all the help!
-Joe

[jzwp11]

An unbound form is just a form that is not bound to a table or query. Most typical data entry/review forms in Access are bound to a table so that you can scroll through the individual records in a table. For an unbound form, you would not see any data & you will not be able to scroll through records since you are not bound to a records source (table/query).

[enolan]

JZPW11,
I have to thank you immensely, the code worked wonderfully--however in playing around with it, I found that it does not like Boolean values. This tangential information aside, is there a way to have the form fields clear after the button is pressed? As the presence of the [collection number] is required, if even that field were the only one automatically cleared upon clicking the button, then unintentional writes could be avoided.
Thanks again for your crucial help!!

[jzwp11]

..is there a way to have the form fields clear after the button is pressed

After the FOR...NEXT loop, you could set the textbox controls on the form to NULL


me.textboxcontrolname=Null

I found that it does not like Boolean values

How so?

I'm guessing that you have to check whether the check box (I assume) is checked (value =-1 ) or not checked (value=0 or any value other than -1)

mySQL= mySQL & "Values (" & IIF(me.checkboxname=-1,-1,0) & ",...)"