Update a Table depending on Form Entry

[carterlangley]

Hi everyone,



OK, so here is my problem.
I have a form where i enter the name and quantity of medication that has been dispensed (Schedule Form)
I have made a new table where the dispensed medication and the quantity is to be recorded. this is linked to the medication table and to the client table. i need to keep a record of the medication dispensed to a client, both date and time as well as quantity and medication.

I have a add new schedule form which allows me to add a new schedule to the schedule table. but i also now need the information for medication to be entered into the medication dispense table once i press the save button on the new schedule form.

Is this as clear as mud?

[June7]

Right - as mud.

So you enter a record in schedule table. Then don't you need to do data entry into Dispensed table when it is actually dispensed?

[carterlangley]

yep, as mud.
if I enter the record using the schedule form, then it must enter the medication dispensed records into the dispensed table. I don't want to have another sub-form or another separate form to enter dispensed information when, hopefully, i can do this programatically using information already available when entering a new schedule. The dispensed table is not part of the data source for the schedule form though. so code it might have to be on the save button.

[June7]

But you only want to create the dispensed record when the medication is actually dispensed? Yes, code behind a button can insert a record into table. Don't know your db so can only provide rough example.

CurrentDb.Execute "INSERT INTO Dispensed(SchedID_FK, DateDisp) VALUES(" & Me.ScheduleID & ", Date())"

Do you want to permit multiple entries of same meds on same date?

[carterlangley]

Ahhhh, currentdb.execute
didn't know about that command. So, I can use that and structure the sql normally. This will make life a little bit easier. Thank you for your help.
There will be multiple entries of the same med on the same date but on different times. eg. morphine is taken multiple times a day but at 4 hour intervals do each dispense of morphine will be its own seperate entry. this way i can also control the amount of medication remaining, which means i can let the database tell me when it is time to order more medication.

i think you have solved my initial problem and have, at the same time, pointed me in the right direction to add further functionality to the database. This database is a work in progress and i keep on adding functionality to it as i go along so it is busy growing arms and legs in all directions.