Results 1 to 4 of 4
  1. #1
    MMMiller is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    2

    Need 2 columns from combobox selection to get the record to display on the form

    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.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Why do you have a combined primary key? Just use an autonumber ID field.

    Suggest you add a Boolean (yes/no) field e.g NewAdmission with default value true
    Set value =false if there is a value in AdnitDate field
    Remove NEW from AdmitDate and change it to a Date field
    This will allow you to run queries and reports searching and sorting by date

    The rest of your post should largely be dealt with by doing the above changes

    NOTE: use code to automatically update Boolean field for new records to false when a date is entered
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    MMMiller is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    2
    I think I understand what you are saying but in order to get the correct record from the chartinfo, I need it to match the case from client and I need to know which admit date from the chartinfo table?? How would a autonumber id field help me find the correct record in chartinfo? I need the admit date on the row they selected in the combobox.
    Sorry if I am missing something, again I am so very new to Access/Vb.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Change of plan after re-reading your original post.
    See attached db which should be fairly close to what you need
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-22-2015, 11:01 AM
  2. Replies: 3
    Last Post: 11-05-2014, 02:43 AM
  3. Replies: 2
    Last Post: 02-21-2013, 07:47 AM
  4. Replies: 1
    Last Post: 11-29-2011, 11:17 AM
  5. Display image based on combobox selection?
    By 10 Gauge in forum Forms
    Replies: 2
    Last Post: 09-15-2011, 07:42 AM

Tags for this Thread

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