How do i use VBA to apply a value to multiple records

[shenty]

I have been developing several databases over the last couple of years, and whilst i wouldn't consider myself a genius i am pleased with what i have learnt and achieved in this time. In the course of this development i have found a couple of times that i wish to apply a similar value to multiple records. Now is the time to learn how......with someones guidance though.

This particular database is record of medicine purchased and administered on animals on a farm. Usually i would enter the ID of the animal then select which medicine has been administered. This is OK but now with over 200 cattle on the farm and TB testing etc, there has been several occassions where i want to record the same medicine to all cattle.

What VB code (or keywords) would i use for this. I am guessing on some kind of FOR, NEXT loop that reads each record from the query/table, applies the necessary value to it, then moves on to the next record.

Any help would be great. Thanks in advance.

 

[midmented]

It sounds like you should just use an update query using criteria to choose the records.

 

[gemma-the-husky]

effective a sql statement is

update tablename set fieldname = newvalue

this will update every record

--------
alternatively

update tablename set fieldname = newvalue where animalage > 2

will update a subset of the set

--------
forming the correct sql is a bit of an art, and will take some getting used to

you can update several fields at the same time, and join other tables together to modify the selection to be updated


Rather than writing sql you can do most things in the Access query design panel

if you design a query using the access design pane, you can see what the sql looks like by changing the view to sql view

 

[shenty]

Thanks for the replies so far - not sure if i explained myself very well.

I'll use the northwind db as a basis for trying to explain what i want to do. Afterwards, if you could tell me whether the suggestions already made will work then i'll work on it.

Using the relationships in northwind, the "Order Details" table has 2 primary keys. One from "Orders" and One from "Products". My db is similar for the farm, "Order Details" is my "Medicine Administered", "Orders" is my "Animals" and "Products" is my "Medicine Cabinet".

Using the form "Orders" in northwind i have similar for administering the medicine. 'Bill to' is my Animal ID (all cattle have unique names). The subform "Orders Subform" is my list of medicine administered.

If i am administering some medicine i pick the animal, that gives me a list of all medicine administered to that animal, the dosages and dates etc. and allows me to add a new line to the subform.

What i want to be able to do somehow is create a form "Batch Administer" that allows me to pick a medicine and batch administer it to a selection of animals. I thought a multiple select list box with some filters above it for things like sheep, cows, cats, dogs etc. I could then have a button at the bottom that says "Administer selected medicine to selected animals". Its the code behind that button i need to work out.

The "Order Details" table in northwind has 2 primary keys, both with duplicates OK. How would a sql statement as suggested do that for me ?

Many thanks again for your advise.