Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8

    Question Unanswered: adding record to Address table

    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.

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Using subform with the patient's address can be a very nice solutiuon to your problem.

    Igor

  3. #3
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8
    Thanx for the reply.

    The reason it's a one-to-many relationship is that the address could potentially be used more than once. It's a general address table that's used for patients, doctors, insurance providers, etc.

    OK, if i have a subform with address info, with the linking field being AddressID, what's gonna happen when i enter a new patient without an AddressID yet? what will show up in the subform?

    also, if i enter a new address in the subform, will it automatically put the AddressID in the patient table?

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    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.

    Igor

  5. #5
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8
    Yeah, that makes sense. It's all coming back to me now...

    But i guess i'm gonna hafta run a check in code to see if the address entered is in the Address table. If it is, i'll just set my address subform to the existing address record.

    If i link the form (Patient) and subform (Address) by AddressID, and scroll to an existing address record, will the AddressID in Patient be set to the existing AddressID?

  6. #6
    Join Date
    Nov 2001
    Posts
    336
    If you link the form (Patient) and subform (Address) by AddressID you won't be able to scroll to much, since AddressID supposed to be unique.

    Igor

  7. #7
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8
    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?

  8. #8
    Join Date
    Nov 2001
    Posts
    336
    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?

  9. #9
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8
    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.

    Gosh, i love normalization.

  10. #10
    Join Date
    Nov 2001
    Posts
    336
    In such case you can always make you subform unbound. When user finishes entering patients address you have to validate it and update all your table using code.

    Hope that helps, Igor

  11. #11
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8
    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?

  12. #12
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    I mentioned unbound forms mainly because they don't save data automatically, since they don't have datasource. Using them may simplify coding of your application.


    just my two cents

    Igor

  13. #13
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8
    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.

  14. #14
    Join Date
    Sep 2002
    Location
    Manitoba, Canada
    Posts
    8
    sweet deal!! i got it to work!

    I did just what i said in my last post, and use code to write a new Address record, and to bring up the address for current clients. Thanx for all your help.

    But this is only part of the Patient form. I still need to show each patient's insurance information, and their doctor(s). But now that i have part of it working, the rest shouldn't be as painful.

    Thanx a bunch!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •