First let me say that I am very new to Access and I have been tasked with a project starting new. So I apologize in advance if the answer is obvious or the question not expressed correctly.
I have 2 tables, client and chartinfo. The primary key in the client table is case number. The primary key(s) for the chartinfo table are 2 fields: case number and admit date. It is a one to many relationship. I have a single form that displays several fields from the client table and the chartinfo table. I have a combo box that will load the case number & patient name from the client file as well as the admit date from the chartinfo table where the case number matches. I also load a record for the client that has a NEW admit date. Example:
Case # Name AdmitDate
1 name1 04/01/2017
1 name1 NEW
2 name2 01/01/2017
2 name2 12/12/2016
2 name2 NEW
3 name3 NEW
4 name4 11/01/2017
4 name4 NEW
Once the user selects a record from the combo box, I want to do 1 of 2 things. If the record they select has an admit date = NEW, I want to fill the form with the info from the client table. If the record they select has an admit date not NEW, then I need to read the chartinfo table to get the correct record. So I must read the chartinfo file using the value in the Case # column and the value in the AdmitDate column. I then want to display that information from the client table and the chartinfo table on the form. I have yet to figure out how to do this. Without any special coding, the information displays on the form once a selection is made from the combo box but it is always the first record where the case matches.
I don’t know if this is important to know but once I get this accomplished, the user will be allowed to modify certain fields and if the info came from a record in the chartinfo where there was an actual admit date then I will perform an update on that record. If the info came from a record in the chartinfo where the admit date was NEW, I will then add a record to the chartinfo table.