Form with Table and Related Table Combo Box search

[Leonidsg]

Hello,

So I have a Form that has fields with a main table named "Demographics" and related tables including "PastSurgeries", "Medications", and "PhysicalExam". The "Demographics" Table shares a one-many relationship with the the other tables. I would like to do 2 things:

1. I would like to do if possible is create a ComboBox which finds the record the fills in the information for the fields pulled from the "Demographics" table -- I actually know how to do this part but I wanted to mention it incase the second part requires changing the first part.
2. Since the "PhysicalExam" table has many records for one "Demographics" record I would also like to add an additional combobox the search for a record based on a date found in the "PhysicalExam" table.

Essentially: I would like to have two comboboxes, one that finds a record in regards to the "Demographics" table and the other that finds a specific date in regards to the "physicalexam" table. I would like the physical exam part of the form to be in form and not datasheet view.

Please let me know if this doesn't make sense.

As always thank you very much for your help.

Lenny

[June7]

Sounds like you want dependent (cascading) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

[Leonidsg]

Does this work if one combobox is searching a parent table and the other is searching a child table.

Thank you
Lenny

Sounds like you want dependent (cascading) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

[June7]

Should. Let us know if you have issue implementing.

[Leonidsg]

Hello I apologize for bothering you, however I am a little clueless where to start. I have looked all over the internet for cascading combo box but I cannot find a good example to follow. As I mentioned I have a main form which has a record source "Demographics" table, so my combo box will search that table and that works well. But I don't know how to set a record source for an additional combo with a record source of the child table "PhysicalExam". As I mentioned I apologize for bothering but I would greatly appreciate your help. Thank you

Should. Let us know if you have issue implementing.

[June7]

Think link I provided in earlier post is the best I've seen.

[Leonidsg]

So I am still confused, I tried to follow the video but it doesn't all make sense. Currently I am trying to make it work for one table. Later I would like it to work for multiple related tables. I have attached a simpler version of my document, if you wouldn't mind taking a look. In my Dexa form the first combo box searches for an existing record and the second one is to narrow down by date. I have the form set up to be blank on load so I can enter a new record but I also have the combo boxes set up so the same form can be used to edit an existing record. Thank you again for the help. Lenny test.mdb

Think link I provided in earlier post is the best I've seen.

[June7]

Dependent combo or list box is a method to constrain the RowSource list of one based on value in another. For instance, select Ford in one box then only Ford models will display in second (dependent) box.

From what I see of your table, you want to select MRN in first box and constrain the second to dates associated with the selected MRN. However, the RowSource of the second box has a filter referencing itself (cmbDate) - this makes no sense. Try:

cmbMRN
SELECT DISTINCT [DEXA].[MRN] FROM DEXA;

cmbDate
SELECT RecordID, DEXADate FROM DEXA WHERE MRN=[cmbMRN];

Since the MRN is not unique in DEXA table, I presume the DexaDate is required to identify a unique record. Instead of dependent comboboxes, you can use one that shows both the MRN and DexaDate fields. Probably should set these two fields as compound primary key to avoid duplicate pairs and also as required. You have records with no date. I expect this should not be allowed.

[Leonidsg]

Correction it is not working but I am closer, it seems that no matter what MRN I select I still get the same Dates. I dont think my AfterUpdate requery is working correctly. Any suggestions.

[Leonidsg]

testworking2.0.mdb.zip Okay so I was able to get the combo boxes to work, kind off, but now the bound controls are not being populated. Any suggestions for that. Thank you.

[June7]

The cmbDate control no longer has code in AfterUpdate. The embedded macro is gone.

[Leonidsg]

OI-Database-version3.0.accdb.zip So with all your great I finally got it to work. Do you mind helping me with the next step. Its the same process but now the form contains a couple of linked tables. I have everything almost working except when I change the Date for the respective MRN the data in the Follow Up tab which correlates to my Followup visit table doesn't change. Thank you again for all of you help.

[June7]

Is primary purpose of this form to enter new FollowUp record? If that is the case, then edits should not be allowed in the fields from FirstVisit. Set them as Locked Yes, TabStop No. Also the query should not be an INNER JOIN. Should be "Include all records from FollowUp ..."

If you want data entry into both tables need a form/subform arrangement http://office.microsoft.com/en-us/ac...010098674.aspx
This will change the dynamics of the dependent comboboxes.

RecordID is set as primary key in FirstVisit but saving MRN as the related value in FollowUp. MRN should be primary key in FirstVisit.

[Leonidsg]

I was under the impression that you don't have to use the subform necessarily, I thought you can use it as a single form. Also in regards to the MRN being the primary key I thought it is generally good practice to use a autonumber for the primary key.

Thank you,
Lenny

[Leonidsg]

I changed it into a subform with single format.