adding record to Address table

[Hildegoat15]

Hi,

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:

dbo_Patient

PatientID
FirstName
LastName
AddressID

dbo_Address

AddressID
Address
City
Province
PostalCode

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

with rstAddress
Me!txtAddressID = ![AddressID]
end with

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. -Matt

http://www.ranamedical.com

 

[thornmastr]

You have already identified your problem. The address id on your patient record should not be a required field for a number of reasons. The patient may be a transient in which case he has no address. He may be between addresses. He may be living with a friend. His current address may be a prison which is not his real address.

A person does not need an address to be a patient.

Without that field being required you can update the patient record before even creating the address record,then you can keep the primary key value of the patient record, and then after you have created the address record, you can use the patient primary key and update the address forward key.

Something to keep in mind. Currently you can walk up and down your patient table and pull current addresses. You have no way to walk the address table to find a patient. Just something for you to consider later.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com