Add multiple records at the same time

[NeilT123]

I have a database which I use for recording applications of fertiliser to my clients crops.

The structure of my database is
Farmer Table
links to
Field Table
links to
Crop Table
links to
Fertiliser Application Table.

A farmer may have 50 fields of which a significant number may receive the same fertiliser applications so currently I filter down to the field and then the crop and then enter the fertiliser applications. There may be 3 or 4 items entered.

I then copy those records, move to the next field and then crop and then paste the original records. I then move to the next record and paste again and so on.

There must be a way to select the relevant crops and then add the new applications to all of the records at the same time but as I am self taught in Access I am getting a bit out of my depth. The other problem is I don't know all of the correct terminology and so am struggling with how to do this and where to start.

Can anyone give me any pointers as to how to start with this?

Thanks

 

[dhookom]

select the relevant crops

This generally is accomplished with a multi-select list box on a form. The form might also include a list box for the applications and possibly a combo box of farms.

The append would then involve code to loop through the selected items in the list box(es) and perform the append SQL.

This might be "out of your depth" unless someone here has a fair amount of free time to write and you provide the control names, table and field names, etc.

Duane
Hook'D on Access
MS Access MVP

 

[ClydeData]

Easier to show this than explain

a 3 minute sort of sample of what to do

http://www.box.net/shared/svtuzg7kzb

Hope this helps

Jimmy

 

[NeilT123]

Thanks for the pointers and the example.

So far I have created a form which is split in two. At the top I can filter by field name or crop type and therefore select the fields I want to apply the fertiliser to. I have added a yes/no box to the cropping table which I am going to use as a trigger.

The bottom of the form is based on a query which is based on a dummy fertiliser applications table so I can create the fertiliser applications (or copy and paste them from a previous record).

That is as far as I have got. My next step is to create an append query which grabs the cropping number from the filter form and the detail from the dummy fertiliser application table and creates new records in the actual fertiliser application table.

Two questions at this stage.

Am I progressing down the right route with this or should I be doing it differently?

If there are 4 fields selected, each receiving 3 applications of fertiliser will the append query create 12 new records or does it not work like that?

 

[dhookom]

I expect this might work as you want. You would be creating a cartesian query where 3 records from one table and 4 from another (not joined) will create 12 records.

Duane
Hook'D on Access
MS Access MVP