I've been banging my head against the wall for a while, so I thought I'd post. It's a bit of explaining, so bear with me. I have a form that show's a patient's information -- name, address, doctor, etc. Right now I'm just trying to get the name and address working, so I have two tables behind the form: a Patient one and an Address one. A simplified version:
When the user hits the Add button, a form pops up where they can choose a patient. That patient's name and address goes into the form. Since I don't think it's possible to update 2 tables in a form (i may be wrong), I put code behind the form to check to see if this address is in the Address table (dbo_Address). If it isn't in there, It creates a new record in the Address table with the new address, and an AddressID is created for it (it's an autonumber field). The problem comes when I want to assign the AddressID to the Patient record. I try doing
Me!txtAddressID = ![AddressID]
but it gives me an error saying "To make changes to this field, first save the record." I try to save the record, but the AddressID is a required field in the Patient table. It says "The Microsoft Jet database engine cannot find a record in the table 'dbo_Address' with matching field 'dboPatient.AddressID."
Would a subform with the patient's address be the solution? I've thought about it, but i can't seem to figure it out. I've used subforms with other one-to-many relationships, but it doesn't seem exactly right in this situation. Any help you could give me would be greatly appreciated.
If you AddressID field is Autonumber then Access will assign new value automatically.
For new addresses you should display blank data entry form and new value of AddressID will be stored in your table, provided you correctly set up link between main form and subform.
Yeah, i guess that makes sense. I've got the subform made, and it receives the correct address for existing clients. But do you have any ideas on how I can assign an existing AddressID to a patient that has an address that's already in the Address table?
Based on two tables structure you posted here, I don't see any significant benefits for re-using patient addresses. Why don't you enter address for every new patient without re-using already entered ones?
How many duplicate addresses do you expect? Several families having the same address?
The problem is that these tables are linked from a separate database program, so their setup can't be changed much. We use smart cards for some of our services, so some of the data has to go in via this secondary database. It keeps track of the patient's charts and such. It's a complicated setup, but we're basically trying to link the smart card database with our Access one. Viewing or changing the data from either the Access or Respironics interface is no problem, it's adding a new record in Access that's killing me.
If i had the choice, I would just put the address in the Patient table and not worry about the duplicate data, but I don't think that's really an option.
When I look at the tables that came with this Respironics database, this Address table is used for the addresses of patients, doctors, insurance providers, etc. Their tables are normalized to all hell -- much more than makes sense, in my opinion.
ok, i've got the address thing to work, more or less. I made up the subform, kept it bound to the Address table and linked to the main form, but did all the insertions of new address via code.
If a user enters a duplicate address, i've made the AddressID on the main form be set to the existing AddressID. Otherwise a new record is made in the Address table and the ID is set in the main form. I then requery the main form to refresh the links to the subform.
The only thing is that when i do a requery to re-link up the subform to the main form, the new record has to get saved and therefore my Undo Record button is basically useless. Is there any way to refresh the subform link without saving the record? Or am i doing something wrong and it's supposed to re-link on its own when i change the value of the AddressID field on the main form?
If i make the subform unbound, the link to the main form wouldn't work then, right? And if that's the case, i guess i'd have to manually retrieve the address when i scroll thru existing records, right? I basically kept the subform bound and linked for ease of use while going thru existing records. The appropriate address then stays with the patient.
But i'll give your unbound form idea a go. Thanx for the help.