Page 5 of 5 FirstFirst 12345
Results 61 to 68 of 68
  1. #61
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your form/subform will not work for many to many relationship.

    Apparently I am not able to adequately explain what you must do but I will give it one more shot.

    tblPatients
    PatientID (primary key)
    etc
    (do not put ProviderID field in tblPatients)

    tblProviders
    ProviderID (primary key)
    etc

    tblPatientProvider
    PatientID (foreign key)
    ProviderID (foreign key)

    Set main form RecordSource to tblProviders.



    Set subform RecordSource to tblPatientProvider.

    Have a combobox with unfiltered RowSource listing patients from tblPatients to select existing patient to associate with provider.

    There is a way to show all the related patient info in the subform but could not use this form to add new patient record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #62
    charlesh is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Location
    Ms.
    Posts
    35
    Hi,

    Thanks for of your help. And, sorry if I'm still not able to understand.


    You mentioned
    Your form/subform will not work for many to many relationship
    But you then mention
    Set main form RecordSource to tblProviders.

    Set subform RecordSource to tblPatientProvider.
    You are loosing me on this.
    Are you referring to "Unbound" ?

    As for the
    tblPatients
    PatientID (primary key)
    etc
    (do not put ProviderID field in tblPatients)

    tblProviders
    ProviderID (primary key)
    etc

    tblPatientProvider
    PatientID (foreign key)
    ProviderID (foreign key)
    The tblPatient. Are you referring to a table just with the "Patients" Name or the records for the patients?
    If it's for the record there will be many entries for the "Patients".

    I know you keep suggesting what I need to do and I'd like to be able to do it myself but if possible show me how to do it.


    If you no longer wish to help I'll understand.

    Charles

  3. #63
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The form/subform you have will not work. Your tables and their forms are not properly designed for many-to-many relationship. Three tables are required at minimum for many-to-many relationship. In your case:

    tblProviders
    ID (primary key)
    Has data about providers (name, phone, address)

    tblPatients
    ID (primary key)
    Has data about patients (name, DOB, phone, address)

    tblPatientProviders
    ProviderID (foreign key)
    PatientID (foreign key)
    Associates patients and providers

    I think I already described in previous post how to create form/subform arrangement for this many-to-many relationship. You already know how to create a subform. Fix the tables as described. Set the RecordSource property for each form as described.


    I am NOT recommending unbound forms for data entry.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #64
    charlesh is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Location
    Ms.
    Posts
    35
    Hi,

    I attached a simple form with the "Provider" as the "Main" form and the sub form for the "Patient". The sub form only has a Combo for the Patients name.
    I have what I think is the correct Relation. I have the "Provider" combo loading the way I want. I can also have the "Patient" combo loaded the "Patient names from the table for "Patients".
    But, I'm still having the problem of showing only the "Patients" name associated with the select "Provider". If I select "Prov_14" I would like to see in the "Patient combo.
    "Mike1, Dave and Sue".

    Thanks

    Charles
    Attached Files Attached Files

  5. #65
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I give up. I do not understand why you want to limit the combobox. Doing so makes it impossible to create new provider/patient relationships. If this form/subform arrangement is intended to be a data entry form to create new records that associate providers and patients, then restricting the combobox defeats that purpose.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #66
    charlesh is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Location
    Ms.
    Posts
    35
    Hi,

    Thanks for replying. The form/subform will be used primary to update or add data to the "Patient" Record only.
    The "Add" data will be for the Same patient/provider. It's like a new office visit. It will be added to the Patients Record last row.

    As for the create a new "Patient/Provider" relation the user can if necessary update this.

    If you want I can attach a copy of my Excel file.

  7. #67
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Now that is something new to me, at least I don't recall reading that requirement in previous posts, even so, it should have occurred to me long time ago this was your real goal. If what you really want is to locate an existing patient and create a new visit record, changes a lot. In this case form/subform would be main form bound to patients and subform bound to visits. Use unbound dependent comboboxes in main form header to input search parameters. Select provider in combobox 1 to restrict patients in combobox 2. Main form filters based on selection in patients combobox. The visits subform will synchronize.

    Both comboboxes would have RowSources based on junction table that associates providers and patients. Did we already establish need for junction table? Can each patient be associated with multiple providers?

    That is fine for existing patients but what about brand new patient - need to first create patient record then a record in junction table that associates provider/patient. Then they will be available for selection in the above form/subform.

    How would user go about 'update' to create a new patient record as well as patient/provider association?

    You might consider a form/subform/subsubform arrangement - provider/junctiontable/visit. Filter (or go to record) main (provider) form for specific provider with unbound combobox, junctiontable subform will synchronize. Use unbound restricted list combobox to go to specific junctiontable record in middle form and if patient not in the list, move to new record row and create (bound unrestricted combobox to select patient in new record), visits form will synchronize. View prior visits as well as add new.

    If this is an entirely new patient with no association with any provider, will need to open patients form to add new record which will then be available in the bound unrestricted combobox for selection to associate with provider. The NotInList event of combobox is useful for this.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #68
    charlesh is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2013
    Location
    Ms.
    Posts
    35
    HI,

    Per your previous suggestion I do have tables set for the "Provider" "Patient/Provider" and "Patient".

    But, I may have miss spoken about the "Visit". The Record's are time stamped when the user enter the data.
    As mentioned.
    1 Select the "Provider" from combo.(Provider combo)
    2 Select "Patient" from combo associated with the Provider.(Patient combo).
    3 When you select the "Patient" have the first find for the patient displayed
    4 If required click "Next" to view the Next record for the same Patient.
    5 If record for the Patient need to be update click the "UpDate".

    With the Access file that I previously attached only shows the combo for the Patient.

    I'm attaching a copy of my Excel file for your review.
    When you open it click the "Add or Update Record" button.
    This will show the form.
    When the form is displayed in the "Provider" combo select "Prov_14"
    Go to the "Patient" combo and select any name.
    You will the "Tab" of of the selection.
    This action will produce a MsgBox.
    It will ask if its a New Record. You will select "No". This action will populate the form from the Record source
    which in this case is the sheet MG-MP.

    Now you can review the data and if required correct the info and click the "UpDate" button.

    From this I think you will have a better picture of what I'm try to do in Access.

    Note: This is not the final copy. The final copy has "Private" data.

    Thanks
    Charles
    Attached Files Attached Files
    Last edited by charlesh; 10-03-2013 at 12:10 PM. Reason: added info

Page 5 of 5 FirstFirst 12345
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-21-2013, 10:43 PM
  2. Replies: 12
    Last Post: 10-27-2012, 05:44 AM
  3. Replies: 5
    Last Post: 08-21-2012, 12:30 PM
  4. Use VBA to edit record or create new record in a query
    By ryantam626 in forum Programming
    Replies: 11
    Last Post: 08-09-2012, 02:37 AM
  5. Replies: 22
    Last Post: 06-12-2012, 10:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums