Results 1 to 13 of 13
  1. #1
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15

    Possible to have cascading data entry fields?

    Hello,


    I have been working on a database on and off since the winter, and I'm at a really good place now. However, I think I've discovered a flaw in my design. I am trying to design a database where my team can track the different orthopedic referral requirements by surgeon - because every surgeon doesn't operate on every body part, and even if you have 5 surgeons who all do the same procedure, they may have different imaging requirements and other necessities (e.g. must have patient history, does not accept pediatrics, does not accept knee problems if arthritis present, etc).

    So I have a tbl_PrimaryProblem (main body part like ankle, knee, spine, etc) which is connected to tbl_SubProblem (meniscus tears, bunions, hardware revision, etc). I also have another table called tbl_ReferralRequirements (x-ray within 6 months, MRI sufficient if no OA, no imaging required, etc). The subproblems and referralrequirements are joined to a junction table called tbl_Process where each Process primary key identifies the unique process by surgeon/problem/subproblem/requirements.

    I have created 2 types of forms:
    1) Search by sub-problem. It has 2 cascading combo boxes (primary problem, then secondary problem) which then produces a list of surgeons who does the specified problem, and then we can look down the list to see who requires what. I've locked this record so no one can accidentally change the data in the tables.
    2)Search by surgeon, but this form is for entering a new "process" per surgeon. You search by name, and it brings up their unique processes in a subform. So for example, I learn that Dr. X is now doing ACL surgeries when he wasn't previously, I can enter the new subproblem, referral requirements, etc. in this form and the data gets saved to the tables.

    THE PROBLEM: In the subform, I have fields for PrimaryProblem_FK and SubProblem_FK, however 1) it seems redundant to list the primary problem since it's already linked to the subproblem in the backend, BUT, it's a good prompt for those using the database to remember what primary problem they are entering for and 2) it's leaving room for huge error as I could enter "Knee" - "bunion" which do not match at all.

    Is there a way to choose a primary problem, and have that limit the options in the subproblem field? Sort of like cascading combo boxes, but within a data-entry subform?

    Thanks in advance, let me know if I need to explain more.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    subforms have a recordsource, same as combo's have a rowsource so you can change that in much the same way. however not clear to me where your combos/subforms are. is the 'primary combo' on the main form and the secondary one the subform or a combo on the subform? and what is the subproblem field? do you mean a combo control?

    Without really knowing what the scenario is - are you aware that the linkchild/master properties of the subform can have multiple values such as

    primaryproblem, secondaryproblem

  3. #3
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Quote Originally Posted by Ajax View Post
    subforms have a recordsource, same as combo's have a rowsource so you can change that in much the same way. however not clear to me where your combos/subforms are. is the 'primary combo' on the main form and the secondary one the subform or a combo on the subform? and what is the subproblem field? do you mean a combo control?

    Without really knowing what the scenario is - are you aware that the linkchild/master properties of the subform can have multiple values such as

    primaryproblem, secondaryproblem
    Thanks Ajax,
    I need to digest some of your questions about the linkchild/master properties as it isn't something I'm well versed in yet. In the meantime, I've attached a snapshot of my database relationships (I've highlighted the tables involved in what I'm trying to accomplish, and the tbl_Process which is my junction table), as well as the Form and Subform that are giving me issues.

    The Form is called frm_OrthoSubProbList which has a single combo box in the header. It brings up the orthopedic surgeon record and subform sfrm_OrthoSubProbList which is pulling from a query - this is where I would like to enter specific details about a process the surgeon does do, as well as their imaging requirements, and that info gets saved in the tables in the backend. I'm not sure if the SubProblem Field should be a combo control or not, as a subproblem is linked to a primaryproblem. That is to say, a subproblem cannot exist without a primary problem (e.g. a bunion belongs to a foot, it cannot have a blank primary problem). I DO NOT want that someone could enter a primary problem and a secondary problem that don't belong together, which is where I'm hitting a wall and maybe they should be a combo control?

    I mentioned the cascading combo boxes in my first post which belong to another form which I don't have any problems with (yet), sorry for adding any confusion.
    Attached Thumbnails Attached Thumbnails OCI DB relationships.JPG   Ortho SubProbList form and subform.JPG  

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The way I see it is not
    Sort of like cascading combo boxes, but within a data-entry subform
    but exactly cascadind combo boxes in the data entry subform. CHange the two controls (for the primary and secondary problems) to combos, allow the primary to select all from table, restrict the secondary to just those for the selected primary (in the row source) then in the Enter event of the secondary problem combo issue a requery of itself.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Hello,
    Thank you to those who replied, I think I have figured out part of my problem. In the picture above, I wanted people to input data directly into the subform, and I was hoping there was a way to restrict options in the Subproblem field based on what is first entered into the PrimaryProblem field. But I see that form fields are a better way to prevent bad data entry, so the above form I posted last week is now just for looking up the surgeon to see what problems they accept (no data entry allowed on this form).

    I have created a new form for adding a new process (image attached below). One thing I didn't mention in my initial post is that we also track the processes of Triage Assessment Clinics (TACs), and they are linked to the Process junction table. The top 2 combo boxes are just for looking up to see if a process already exists, either by orthopedic surgeon, or by TAC. If the process doesn't exist already, then the user can proceed down and enter a new Process. I'm still hitting the same issue though, where I can't turn my PrimaryProblem and SubProblem combo boxes into cascading combo boxes. I mean I can, but after I've set it all up, the SubProblem field goes blank. I've tried moving the fields onto a subform, but the problem still exists.

    - PrimaryProblem combo box re-queries SubProblem after click
    - SubProblem combo box has following expression in the PrimaryProblem_FK field of the query: [Forms]![frm_AddNewProcess]![cbo_PrimaryProblem_NewProcess]

    Do you think it could be because I have 2 other combo boxes that are querying different things, ortho or TAC, on the same form? I don't know if it's an issue with linking the master and child fields either...?

    Ultimately, I would like users to be able to look up ortho or TAC processes to make sure they don't already exist, then be able to choose a primary problem when entering a new process, which then limits the options in the subproblem combo box. Everything on this form works otherwise, it's just when I try to turn the 2 combos into cascading combos that it stops working.

    Thanks!
    Attached Thumbnails Attached Thumbnails AddNewProcess.JPG  

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would be helpful if you would post your dB. Only need a few records to illustrate the issue. Change sensitive info - use names like Mickey Mouse, Fred Flintstone, etc.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not seeing the point of a primaryProblem foreign key in Process. If it's a key field, it ought to be related to something, otherwise there's no point? However, I don't know enough about the process to make a suggestion but it looks like it is not needed at all.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Quote Originally Posted by ssanfu View Post
    It would be helpful if you would post your dB. Only need a few records to illustrate the issue. Change sensitive info - use names like Mickey Mouse, Fred Flintstone, etc.
    Hi ssanfu,

    I attached the zipped folder below. Any advice would be appreciated

    FYI, since my first post, I've added a yes/no check box field in the Process table, I'm trying to come up with a way to show which processes the surgeons have either stopped doing recently, or have suggested they are not orthopedic issues but need to go to another specialist. So just ignore the checkbox field for now as it isn't pertinent to my main question.

    Thanks!
    Process database - sample.zip

  9. #9
    Emmers is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2020
    Location
    Canada
    Posts
    15
    Quote Originally Posted by Micron View Post
    I'm not seeing the point of a primaryProblem foreign key in Process. If it's a key field, it ought to be related to something, otherwise there's no point? However, I don't know enough about the process to make a suggestion but it looks like it is not needed at all.
    Hi Micron,

    I've actually been wondering about that as it is actually joined through the SubProblem table - is that an accurate assumption? I want the Primary Problem to be visible in the Process table however, so perhaps I should just be pulling in the PrimaryProblem field and not the foreign key?

    Thanks for picking up on that, hopefully you can send me in the right direction

    Em

    P.S. edited comment to include snippet - I removed the PrimaryProblem_FK from the Process table, and instead made the primary problem visible with the SubProblem in the lookup... is this a more appropriate solution? I want the primaryproblem visible just for clarity - this list is going to get very long with lots of medical terminology so I want it as a visual aid.
    Attached Thumbnails Attached Thumbnails withoutPrimaryProblem_FK.JPG  
    Last edited by Emmers; 09-24-2020 at 09:15 AM. Reason: added picture and additional comment

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Would you please have a look at the attached file, wasn't that what you were trying to achieve?

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Check out the Add New Process Form in the attached.

    I changed the structure of the tblSubProblem so that the Many Side of the relationship is on a Number Data Type vice the Text you previously had.

    Looks like Vlad beat me to it.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It takes a thorough understanding of the hierarchies and the relationships of the entities and their attributes (i.e. the business model) for one to answer the question of 'is it correctly structured'. I don't have that. I map out my tables and relationships on a large sheet of paper and try to think 'if I need this record over here, how can I get at records that might be 2 or 3 levels up?' If the following assumption is correct, then it looks like the field is not needed as noted, but if it is, it ought to be part of a relationship link somehow since it's a key field.

    PrimaryID 6

    subID 1 primaryFK 6
    subID 2 primaryFK 6

    processID 1 subFK 1 Notes1
    processID 2 subFK 1 Notes2

    It seems that one can work backwards from Notes2 value and retrieve the correct PrimaryProblem data, but that is a simplistic take on it. Only testing the model will tell.

    I would NOT use table level lookup fields, but you are. You are asking for trouble.
    http://access.mvps.org/access/lookupfields.htm
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have several suggestions on how to proceed.
    I made changes to the Ortho search form as an example of a different way to have a main form/sub form.


    I changed the look up fields (in the tables) to text boxes, added "_PK" suffixes to the PK fields, converted Macros to VBA code, added a couple of buttons.
    I also changes the report record source - removed the combo boxes in the report.
    On the main form I hid all but 2 buttons.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 1
    Last Post: 03-20-2019, 12:28 AM
  2. Data entry form with 2 'lookup fields.
    By Zombai in forum Forms
    Replies: 5
    Last Post: 12-12-2017, 10:48 PM
  3. Replies: 4
    Last Post: 12-21-2015, 10:57 PM
  4. Replies: 1
    Last Post: 12-06-2012, 06:11 PM
  5. Missing data entry fields in my subform
    By Nashskye13 in forum Forms
    Replies: 7
    Last Post: 07-30-2012, 03:22 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