Matty
...the Myth Buster
- Local time
- Today, 05:47
- Joined
- Jun 29, 2001
- Messages
- 395
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.
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.
