Trying to create multiple records from a form

[ed_hollywood]

We are quite new to Access 2007 and am trying to make a form that can create many records from a single entry on a form. We have a horses and we want to be able to select a dewormer from a combo box, set the date the horses were dewormed, and click a button that will create records for all the horses that has their name, type of dewormer, and the date they were dewormed.



We are able to create the records one at a time from a form, but are looking to do them all at once as there is about 60-70 horses.

Any ideas?

Thanks in advance,

Ed

[CraigDolphin]

Assuming you have a table for horses, a table for treatments, and a table of medications (dewormers), you would likely use something like:
INSERT INTO Treatments ( HorseID, MedicationTypeID, TreatmentDate )
SELECT Horses.HorseID, [Forms]![FormName]![ComboName] AS MedicationID, Date() AS TreatmentDate
FROM Horses;

However, you might want to replace the Date() function with a reference to a textbox on the form that requires the user to specify the data of the treatment as well as the dewormer type. Make sure to check that both controls on the form contain data before executing the query.

[ed_hollywood]

Thanks Craig! We have a bit different table set up, so we are trying to think our way through making it wotk...we'll keep you posted. Can you explain how the SELECT line works?

Thanks in advance!

Ed

[CraigDolphin]

Let me parse that out so you can understand it:

INSERT INTO Treatments ( HorseID, MedicationTypeID, TreatmentDate )

This part is just saying that, for each of the records returned by the rest of this query, put a record into table treaments which three values to be added into the horseID field, the medication id field, and the treatmentdate field respectively.

 
SELECT Horses.HorseID, [Forms]![FormName]![ComboName] AS MedicationID, Date() AS TreatmentDate
FROM Horses;

This part is creating a list of values.
The From clause tells it to return a record for each record in the source table 'Horses'.
The Select Stament tells it what fields/columns to retrieve or calculate from the information in the source table. Each field is listed separately and separated by commas.
Thus, the first part [Horses].[HorseID] tells the query that the first column should contain the values in the HorseID field in table Horses.
The second part, [Forms]![FormName]![ComboName] AS MedicationID is doing two things.
The 'As MedicationID' bit simply says that this column of the query will go by the name/title of 'MedicationID'.
The [Forms]![FormName]![ComboName] part is a reference to the combo box control, on the form that you will build. The [Forms] part specifies that the db look through the collection of database objects called forms. The [FormName] part is only a placeholder for the purposes of answering your question because you never specified what the name of your form is. You will have to substiture the actual name of your form instead of the word 'FormName'. Likewise, ComboName is just a placeholder for demonstration purposes. you will need to replace that part with the name of the combo control on your form. Access typically defaults combo box names to match the field name, or to something like 'Combo2'. This is a bad practice in the long run IMO, but whatever the name of your combo box is, that's what you will need to use.

Date() AS TreatmentDate is simply outlining the third column returned by the query. As before, the last bit simply says what this column will be called (TreatmentDate). The first bit is calling a built-in Access function that returns the current date. I would imagine that you would be better off replacing this part with a reference to another control on your form which the user first populates with the actual date of the treatment.

So, the query returns three columns, with a row for each record in horses, then it adds those values to the three columns in the table treatments.

Clear enough?

[archzealot]

Hello,

I need to do something similar to the use case above, and so far I was able to understand the INSERT and SELECT statements. But what I don't get is, how would I integrate it into the form?

This is what I have so far:
1. All the controls and the labels in the form
2. An "Add records" button

Now, presumable, pressing the button should trigger the INSERT SELECT statement as stated in the previous reply, but I don't know how to make it so in Access 2007. Every time I right click on the button and choose "Build Event", I get some Macro window to which I'm not very familiar.