Create Record Database Help

[June7]

Another 'primary key' field is not required in the junction table unless it will have a related child table that will need the pk as fk.



You could set the PatientID and ProviderID as compound PK to prevent duplicate pairings.

[charlesh]

HI,

I created the "SubForm" for my project. I first created a form for the "Provider", then the subform for the "Patient".
The Form for the provider appeared as I would like. However, the subform for the "Patient" looks just like the "tbl" for the patient.
I may or may not be doing this the right way. But, what I was hoping for is the form to look like as I have attached in my first post.
It will look more like a userform in Excel.
If there is a way to do it please proved sample attachment.


Charles

[June7]

A form used as a subform can be set to Datasheet, Continuous, or Single view as long the it doesn't also have a subform. Just set the DefaultView property of the form.

[charlesh]

Hi,

Thanks.
I have the form the way I want. Now to the task of getting the data to be displayed with the selection of the "Provider" and have this selection populate the "Patients" combo with no dupes.

Charles

[June7]

I will offer again that filtering the combobox really doesn't make sense to me. Certainly not if the combobox is bound. The subform records should synchronize with the main form but if you want to be able to create new associations between provider and patient, the combobox should not be filtered.

[charlesh]

Hi,

The ref to dup was just an expression.
I would assume for the Patient combo you would
Select the "Patient" from the table for Patient/Provider to do so you would have the "Provider" selection from the combobox then
look at the tbl for Patient/Provider look for the "Providers" listed. This in turn would then populate the "Patients" combo.

Charles

[June7]

Perhaps I don't understand what you want to use this form/subform and combobox for. If you want to do data entry and want to be able to create NEW provider/patient associations, the bound combobox in subform could list only patients NOT yet associated with provider. Restricting the combobox to list only patients already associated with provider negates the functionality for creating new associations.

However, this combobox will have to use lookup alias and filtered combobox RowSource with lookup alias will not work nice with continuous or datasheet form.

[charlesh]

Hi,

The concept is for the user to "Add" or "Update" existing Data.
The "Add" will be used when the "User" selects an existing "Provider".
They will then enter the Patient name and all related info. Then click the "Add" button. This action will populate the spreadsheet to the last row.
Now, if they need to modify a record for a patient they will select the "Provider" then the "Patient". They will review the data displayed and if a correction is required they will make the correction and click the "Update". If the recored being displayed is not the one they are looking for they will click the "Next" button until the record that needs to updated is displayed. But, they will then be able to go back to the "Previous" record if necessary.

[June7]

My comments about bound combobox unchanged.

[charlesh]

Hi,

I have both forms set to "Single View". Will that pose a problem?
Also, if all else fails I'll assume I can do what I want with code.
If set with code then I could use only one form. Correct?
Other that that what could you suggest to accomplish what I want?


Charles

[June7]

Okay, sounds like you need two comboboxes.

1. an UNBOUND combobox in subform header section that can be used to select a patient for filter criteria. That combobox would have a RowSource limited to patients already associated with provider. The RowSource would be a query that joins Patients and Provider/Patients tables so you will have the patient name for display. The WHERE clause will reference the ProviderID value on main form. Use query build to help construct the SQL statement.

2. a BOUND combobox in the detail section to select patient in new record. Could have code that would lock this combobox if the current record is not NEW.

[charlesh]

HI,

Thanks for the info. I now have the combobox for "Provider" and "Patient" working.
When I select the Provider the Patient combo list all of the "Patient" names.
For the "Patient" name I can see a selection box for them. If I select a patient and when done with the patient I close the form.
When I re open the form I do not want the "Provider" "Patient" name to be displayed.

[June7]

Don't understand. Don't want them to show where? The form opens to existing records. Data will show.

[charlesh]

When the form opens Provider and Patient show show blank.
Also when I select a different Provider I want the Patient combo display to be "".
Currently when Change the Provider I can still see the previous "Patient" name and when I look at the combo for the Patient I can see their name for the current provider.
I would like to cleat the Patient Name when a different provider is selected.

Charles

[June7]

Which combos are you referring to? The filter criteria combobox which should be UNBOUND? Or the data entry combobox which should be BOUND?

Will have to use code in provider combobox AfterUpdate event to set the filter criteria patient combobox to null.