Looking up values in Access 2007 for veterinary database

[matt72]

Hi All



I'm not too great with Access, but a few months back I created a small database for a veterinary practice (I've attached the relationships for some of the tables & the names of some tables have been changed for simplicity).

At the moment, the three tables shown to the right of the attachment are not related to any of the other tables, and were just used as reference 'lists' - the user records the name of the drug/service/vaccination in the Patient Drugs/Patient Services/Patient Vaccination table, and checks the price in the corresponding list. The form for data entry uses subforms - Patients as a subform of Transactions, Patient Drugs/Patient Services/Patient Vaccination as a subform of Patients (these three are in datasheet view). Also, the lists do not contain all items to be used, only common ones and their normal price.

However, looking up the lists has become a bit of a pain.

I'll use the drugs list to describe what I'm trying to achieve -
When the user types a drug name in the Patient Drugs table, the Price field in the same table should be set, using the value from the Drug List if the drug is recorded there.

I've tried a few ways to do this with no luck, mainly because:
-The drug price in 'Patient Drugs' may not be the same as in 'Drug List'
-Not all drugs are recorded in the drug list (only common ones)
-The prices must be stored with the patient and transaction to avoid past invoices being changed when the lists are updated.

I'm not sure whether implementing this would need changes to table relationships or lookups or VBA coding, so any help would be much appreciated.

Thanks in advance,
Matt.


(Vista/Win 7 & Access 2007)

[RuralGuy]

Sorry about the delay in responding Matt. Are you still working on this issue?

[matt72]

Hi RG, no I haven't figured it out, any help would be much appreciated

[RuralGuy]

It may take me some time to get familiar with your system. I noticed you have MultiField Primary keys. Those drive me nuts and I've generally found they were not needed. Most people I've helped just did it that way to keep them unique. There are (IMHO) better ways to do that. Any reason *not* to post your zipped up db? It would probably make it easier to assist with the solution but I would understand if you did not want to do that.