Results 1 to 9 of 9
  1. #1
    Oblio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2013
    Posts
    50

    Question Proper Forum to post? Build an expression in Main Form txtbox based on subform records

    Just wondering which Forum you would recommend posting in for the following problem:



    Main Form unbound textbox expression which builds from fields on the Main Form as well as any combination of records in the Subform.

    We are building a very complex file name which can be used with a scanning batch so there are no keying errors in the naming which is crucial.

    If you could advise the best forum for this question I would be very grateful.

    Thanks,

    William

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    usu. the parent form builds the child. Why do you need to do it in reverse?
    what is involved?

  3. #3
    Oblio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2013
    Posts
    50
    Ok, the relationship is a many to many between Courses, and Instructors/Examiners.

    The Main form contains course info, and the subform is the junction table for Instructors and examiners.
    There can be a very different set of Instructors and examiners for each course. Every course has at least 1 Instructor and 1 examiner (these can also be the same person).
    However, we can also have an "Instructor 1, Instructor 2, and Examiner 1.
    We have a maximum of 4 possible instructors and 4 possible examiners, so there are quite a few possibilities.
    I can attach a database if you think this is the best area to post this?

    Thanks,

    Bill

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Just before you want to use the value that will be contained in the text box, use a VBA routine to concatenate the various fields required. For instance, if the file name is going to be used when you click a button, then that would be the right place to create the value.

  5. #5
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    If your original post is accurate when you state: "...combination of records in the Subform.." It strikes me as very unlikely that this could be implemented via code on the form level alone.

    I would suggest relying upon a query of the sub records - that unions them into a single record which would then give you a more straight forward starting point to build your string.

  6. #6
    Oblio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2013
    Posts
    50
    Ok, that makes sense. So if I search VBA to concatenate fields I should see some examples I bet. Thanks very much for that!

    William

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not sure what your table structure is, but I attached a rough data model for consideration.

    On table Course you could build a unique composite index of CourseID, InstructorID, ExaminerID with some associated validation to prevent duplicates.
    Good luck.
    Attached Thumbnails Attached Thumbnails CourseInstructorAnd-OrExaminer.jpg  

  8. #8
    Oblio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2013
    Posts
    50
    Ok, my first instinct was to used nested IIF statements or a query, but for the life of me I wasn't sure how to proceed with a query for the subform records. No problem until I get up to the subform records...

    I will have a look at Union queries to see if I can get one working.

    Thank you for your help!

    William

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Give us more information so we can help you further, what are the steps in your process, what fields do you need to use, etc.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-02-2016, 08:04 AM
  2. Replies: 1
    Last Post: 04-08-2013, 11:58 AM
  3. Replies: 4
    Last Post: 07-11-2012, 10:31 AM
  4. Replies: 3
    Last Post: 12-01-2011, 06:51 AM
  5. Replies: 0
    Last Post: 06-23-2009, 03:01 PM

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