Results 1 to 12 of 12
  1. #1
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24

    Many to Many data entry on a form (and report output)

    I have a table of religious denominations and another table of books. Each has as its primary key an alphanumeric code for the denomination or the book. All books are associated with a particular denomination or have the "ND" code for non-denominational. Most of the denominations have more than one book associated with them. Some of the books are associated with more than one denomination, sometimes a surprising number of them.

    I set up a junction table between the book and denomination tables to handle the many to many relationship. It was my first attempt at this type of thing and, to my surprise, it seems to work.



    I have started on a data entry form to add books to the database. It has gone along fine until getting to this many to many situation. Is there a "standard way" to handle this when it is not know how many associations there might be ahead of time?

    I have a vague concept of creating a subroutine to call a separate "add denominations" form. When that form is closed, It needs to add the new associations to the junction table and return flow to the "Add Books" form. I then would like the form to show the codes for the associated denominations along with graphic image representing the denomination. I am at a loss as to how to do this.

    The only solution that occurs to my troubled mind is a brute force solution that creates plenty of blanks that can be filled in but this seems wasteful of space and, I have no doubt that if I design for 10, some odd case will show up that demands 15.

    I suppose what it boils down to is amending the form at run time but my imagination tells me that is beyond me. I'm hoping y'all will know of a "standard way" to solve this situation.

    I'm also hoping that similar methods can be used to generate the report when I get that far.

    Thanks,

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Conventional designs:

    1. single form bound to the junction table with comboboxes to select denomination and book

    2. main form bound to Denominations and subform bound to junction table with combobox to select book

    3. main form bound to Books and subform bound to junction table with combobox to select denomination

    Can use combobox NotInList event and code to enter new items 'on-the-fly' during data entry. Common topic. Many examples.

    Data entry has nothing to do with generating report. Reports are built from data entered. Report RecordSource would be a query that includes all 3 tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24
    Quote Originally Posted by June7 View Post
    Conventional designs:

    1. single form bound to the junction table with comboboxes to select denomination and book

    2. main form bound to Denominations and subform bound to junction table with combobox to select book

    3. main form bound to Books and subform bound to junction table with combobox to select denomination

    Can use combobox NotInList event and code to enter new items 'on-the-fly' during data entry. Common topic. Many examples.

    Data entry has nothing to do with generating report. Reports are built from data entered. Report RecordSource would be a query that includes all 3 tables.

    I appreciate the reply. You've given me an approach to start with and I'll start looking into it.

    I understand that reports are built from the data but the approach for dynamically creating space to hold the information is what I was specifically wanting to know about. Perhaps I'm missing something but I still don't see how to do this at run time.

    The United Methodist Hymnal(1989) will have a single denomination, UMC, associated with it. It would be easy to place an image and a text box and bind them to a single result. Some resources, though, have been approved by multiple governing bodies. If a resource has n endorsing bodies, how do I place n text boxes and n image frame on the form when I won't know the magnitude of n until the selection is actually made at run time? And then there is the case where a governing body adds an endorsement after due consideration (which can take years) and the entry for the books has already been made. The reverse, where a book loses an endorsement, is rare but does happen as well, usually when a denomination fragments into smaller bodies.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not understanding issue. Enter records via form(s).

    A single textbox is bound to a field. The form or report will 'replicate' the textbox for each record. This is how dynamic display of n records is handled.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24
    Quote Originally Posted by June7 View Post
    Not understanding issue. Enter records via form(s).

    A single textbox is bound to a field. The form or report will 'replicate' the textbox for each record. This is how dynamic display of n records is handled.
    I'll try to be clearer.

    frmAddBooks is bound to tbBooks.

    frmAddBooks has textboxes bound to individual data items such as BookID, BookTitle, BookSubtitle, BookPublisher etc. As I fill in the text boxes, those values remain in the text boxes.

    I get down to the "denominational area" and, if I'm understanding correctly, I'll be able to use a combo box to to select multiple denominations. The combo box will be bound to the junction table. As an example, the LuBW (Lutheran Book of Worship) will get three new entries in the junction table: LUMS, ELCA and LUWS.

    The denominational area on frmAddBooks has a text box and an image frame that need to be bound to the data newly entered into the junction table but there are three different DenomIDs and three different DenomLogos. Do the new text boxes and new image frames just magically appear? That has not been my experience. Even if they did, what tells them which entries in the junction table to select? LuBW is a single record because it is a single book. Am I supposed to enter the data in the first part of frmAddBook n times for n denominational associations? The permutations would get very complicated on the parts of the form that have not been coded yet where the multiplicities can get extreme.

    Does this make any sense?

    As an aside, I do appreciate you putting up with me. I like to pretend that I used to be fairly bright on technical matters but since the brain tumor in 2002-2003, I feel like I am constantly struggling to attain the status of moron.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The denomination logos should be in the Denominations table. If you want to display associated logo during data entry, the subform RecordSource will have to be a query that joins the junction table with the Denominations table. I don't really see value for displaying during data entry. Display logos on report.

    The junction table should save the DenomID. Are you using autonumber field as primary key? If yes, then this value should be saved as foreign key in junction table. The combobox can be setup to save the ID but display descriptive text. So textbox not really needed.

    You enter the book once in the main form then create as many denomination records as needed in subform. The BookID primary key will automatically save to each record as foreign key as long as subform container Master/Child links properties are properly set.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24
    Quote Originally Posted by June7 View Post
    The denomination logos should be in the Denominations table. If you want to display associated logo during data entry, the subform RecordSource will have to be a query that joins the junction table with the Denominations table. I don't really see value for displaying during data entry. Display logos on report.

    The junction table should save the DenomID. Are you using autonumber field as primary key? If yes, then this value should be saved as foreign key in junction table. The combobox can be setup to save the ID but display descriptive text. So textbox not really needed.

    You enter the book once in the main form then create as many denomination records as needed in subform. The BookID primary key will automatically save to each record as foreign key as long as subform container Master/Child links properties are properly set.
    The logos, or rather paths to the logo files are in the denominations table. You are correct in that the logos are much more useful in the reports than here but, based on my experience, I wanted to include them for when records are updated. The DenomIDs are the primary key but they are not autonumbers. They are alphanumeric mnemonics, and I use that term with reservation. Some, are used so frequently that I have no trouble remembering them. UMC = United Methodist Church has been a part of my daily awareness for 50 years. ELCA = Evangelical Lutheran Church of America is one I deal with often enough that it is firmly rooted in my feeble memory. MCSA = Methodist Church of South Africa, however, does not pop up for me too often and I had to look it up to write it just now. I am, however, much more familiar with all of the logos including some rather obscure ones. They just stick in my mind better. I have found that is the case with some my staff but not all; some remember the mnemonics better. I wanted to provide both.

    The junction table does save the DenomID. I probably should own up to the fact that it started as denominations when I began my work years ago but I also use the term to refer to other sponsoring groups such as religious orders and ecumenical associations. The textboxes were, like the logos, to show what has already been entered, mostly for going back and updating things.

    "You enter the book once in the main form then create as many denomination records as needed in subform." This is how I was thinking of it conceptually. The book is entered from the main fmBooks once. This has been done with a limited set of data for testing purposes. The data that has gone into the junction tables has been put in manually in the datasheet view of the table. I am just getting to this point to enter it properly now.

    It is your last sentence that I am not yet understanding. "Subform container" is not a term I am familiar with. I think I need to spend some time brushing up on the idea of subforms. I am aware of them but have not done anything with one and last really read anything about them c2005 or so when I read Allison Balter's book. I never got around to actually trying to use Access until a few weeks ago. When I started this thread, I was using "subform" to mean a form that gets called by code from a button on another form. Now, I am realizing/remembering that it has a more distinct meaning as something actually embedded within another form. I need to go read up on the rules.

    I'm going to be shutting down for now as I prepare for tomorrow's service. I will check back tomorrow evening when I get back to San Antonio.

    Thanks again for the time you are taking with me.

  8. #8
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24
    it has taken me some time to make some progress but progress has been made. It has been painful progress and many of the early attempts were utter failures. For that matter, what I have now can't be considered a success. It's just farther along that I was and I might be able to see the completion of this task from here.

    Below is the code for what I have come up with. It still has major problems that I have yet to overcome and it's definitely not pretty. It's just a start.

    Option Explicit

    Dim DB As Database 'database this pertains to
    Dim RS As Recordset 'recordset reading the lookup table query and gathering all the current denominations and groups
    Dim frm As Form 'form being created at runtime by this on click event
    Dim ctlCheck As Control 'checkbox to be checked if denomination of group has adopted the book
    Dim intCheckX As Integer 'X coordinate of the checkbox
    Dim intCheckY As Integer 'Y coordinate of the checkbox
    Dim intIDX As Integer 'X coordinate of the denominational code
    Dim intIDY As Integer 'Y coordinate of the denominational code
    Dim intDenomX As Integer 'X coordinate of the denominational name
    Dim intDenomY As Integer 'Y coordinate of the denominational name
    Dim intLogoX As Integer 'X coordinate of the denominational logo
    Dim intLogoY As Integer 'Y coordinate of the denominational logo
    Dim intBkLogoX As Integer 'X coordinate of the book logo
    Dim intBkLogoY As Integer 'Y coordinate of the book logo
    Dim intBkLabelX As Integer 'X coordinate of the book title
    Dim intBkLabelY As Integer 'Y coordinate of the book title
    Dim rsCount As Integer 'number of records in the recordset; used to determine how many entries to put on the form
    Dim intFrmLabelX As Integer 'X coordinate for form instruction
    Dim intFrmLabelY As Integer 'Y coordinate for form instruction
    Dim intAcptBtnX As Integer 'X coordinate for accept button
    Dim intAcptBtnY As Integer 'Y coordinate for accept button
    Dim intEscBtnX As Integer 'X coordinate for escape button
    Dim intEscBtnY As Integer 'Y coordinate for escape button
    Dim ctlIDLabel As Control 'label giving code for denomination
    Dim cltDenomLabel As Control 'label giving name of denomination
    Dim ctlFormLabel As Control 'label giving the form instructions
    Dim ctlBkLabel As Control 'label giving the book title
    Dim ctlDenomLogo As Control 'image holder for the denominational logo
    Dim ctlBookLogo As Control 'image holder for the book
    Dim ctlAcceptBtn As Control 'buttom to exit the form and update the DB
    Dim ctlEscapeBtn As Control 'button to exit the form without changes
    Dim blDenomChecked As Boolean 'is the option checked
    Dim strDenomID As String 'Code for denomination
    Dim strDenomLabel As String 'Full name of denomination
    Dim strDenomLogo As String 'path to denomination logo
    Dim strBkLogo As String 'path to book logo


    Private Sub btnAssignDenominations_Click()

    ' set db, recordset as per examples
    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("qryluDenominations", dbOpenDynaset, dbSeeChanges)
    Set frm = CreateForm
    'query comes from lookup table of all currently valid denominations and groups
    frm.RecordSource = "qryluDenominations"
    'move to the last record and find out how many choices there will be
    RS.MoveLast
    rsCount = RS.RecordCount
    'set beginning coordinates for form elements; established by trial and error
    intCheckX = 100
    intCheckY = 1800
    intIDX = 350
    intIDY = 1800
    intDenomX = 1200
    intDenomY = 1800
    intLogoX = 5000
    intLogoY = 1800
    intFrmLabelX = 100
    intFrmLabelY = 100
    intBkLabelX = 200
    intBkLabelY = 500
    intBkLogoX = 5000
    intBkLogoY = 100
    intAcptBtnX = 7000
    intAcptBtnY = 200
    intEscBtnX = 7000
    intEscBtnY = 800
    ' the following hardwires an example book just to make sure the concept works. will need to assign the variable to the correct path for whatever book is chosen
    strBkLogo = "D:\OneDrive - JAL3\Worship\UBPH - United Brethren Publishing House\UBC Hymns (1867)\UBC Hymns (1867) logo 2.jpg"
    ' label gives purpse of form
    Set ctlFormLabel = CreateControl(frm.Name, acLabel, , 1, "Assign Denominational or other Group Endorsements", intFrmLabelX, intFrmLabelY)
    ' give information about the book currently under consideration; this is to be in the header section of the newly created form
    Set ctlBkLabel = CreateControl(frm.Name, acLabel, , 1, "BOOK TITLE GOES HERE", intBkLabelX, intBkLabelY)
    Set ctlBookLogo = CreateControl(frm.Name, acImage, , 1, strBkLogo, intBkLogoX, intBkLogoY, 1440, 1440)
    ' create a button that will accept and process the inputed assignments and another to discard them and go back to the previous form
    Set ctlAcceptBtn = CreateControl(frm.Name, acCommandButton, , 1, "Accept", intAcptBtnX, intAcptBtnY)
    Set ctlEscapeBtn = CreateControl(frm.Name, acCommandButton, , 1, "Escape", intEscBtnX, intEscBtnY)
    ' move to the begining of the recordset to begin iterating through the various choices; the following to be in the default "detail" section of the form
    RS.MoveFirst
    Do Until RS.EOF
    ' add controls to subform
    ' create checkbox and set to false
    Set ctlCheck = CreateControl(frm.Name, acCheckBox, , "", "", intCheckX, intCheckY)
    blDenomChecked = False
    ' get the ID code and name of denomination or group and the path to the denominational image
    strDenomID = RS!DenomID
    strDenomLabel = RS!DenomName
    strDenomLogo = RS!DenomLogoSource
    ' create a label for the denominational code and set it to the current value
    Set ctlIDLabel = CreateControl(frm.Name, acLabel, , "", strDenomID, intIDX, intIDY)
    Debug.Print strDenomID
    ' create a label for the denomination or group name and set it to the current value
    Set cltDenomLabel = CreateControl(frm.Name, acLabel, , "", strDenomLabel, intDenomX, intDenomY)
    Debug.Print strDenomLabel
    ' create and image holder for the denominational logo and set its source to the path to the image
    Set ctlDenomLogo = CreateControl(frm.Name, acImage, , "", strDenomLogo, intLogoX, intLogoY, 450, 450)
    Debug.Print strDenomLogo
    ' move to the next record and update the cordinates for the next in the list
    RS.MoveNext
    intCheckY = intCheckY + 600
    intIDY = intIDY + 600
    intDenomY = intDenomY + 600
    intLogoY = intLogoY + 600
    Loop 'Do Until RS.EOF


    There are still plenty of problems and I don't really understand a lot of what is happening as well as I would wish.

    1. From what I have been able to learn, adding the controls only works in Design View. My intention after the form is created is to save it and then reopen it in form view. This seems like a very kludgy approach. Is there something better I should be pursuing?

    2. This procedure is called when the assign denomiations button is clicked on the AddBooks form. As such, "Me" refers to the new form being created. I need to pass information to that new form such as the book being worked on, the code for that book and the path for the image file associated with the book. Nothing I have tried has been successful and there have been several weeks worth of attempts. I am unclear as to how to refference the field of the form calling this procedure.

    3. Although the form successfully creates with the correct information, it is very ugly and needs formatting. I have not yet figured out how to call the properties of the newly added controls. The names of the controls change depending upon how many times the procedure has been run already and how long the list from the lookup table is. I need to better understand how to address the buttons, checkboxes, labels and images.

    4. The information for the book being worked on is put in the header section and the table of choices for the available denominations is in the detail section of the form. Programatically, that seems to work but there is nothing to visually distinguish the header section from the detail section. Even attempts to just draw a line (set new control as acLine) have been stymied by my inability to specify the line weight, direction, origin, terminus, etc.

    5. everthing thus far just creates the new subform. Implementing the code for what happens when the accept or escape buttons is clicked has not even been contemplated yet. I can't even get text to appear on the two buttons created let alone generate events for them. I am again hampered by my inability to know ahead of time the names the buttons will have so I don't know how to address them.

    6. It was a fairly quiet night at the hospital last night as I covered the overnight shift and I came up with many, many more questions as I wrestled with this thing. Doubtless, many more have not yet occured to me but it seemed time to beg for help again as I just seem to be going in circles at this point.

    Thank you,

  9. #9
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24
    It seems that my efforts at replicating the indentations were for nought. I apologize for the lack of readability for the above as well!

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Seems like a lot of work to rearrange the form with code, and probably not necessary.
    Take a look at the attached. It uses a master form bound to tblBooks and a subform bound to the junction table.
    This is essentially point 3 in June7's post #2.
    The subform is formatted as continuous forms, and will expand to accommodate any number of denominations for the book.
    You can expand the subform row content with whatever you need, like the logo images.
    This is just a concepts design with no attempt to aesthetics. It took about 15 minutes to complete.
    Last edited by davegri; 07-15-2018 at 06:55 AM. Reason: credit

  11. #11
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24
    Quote Originally Posted by davegri View Post
    Seems like a lot of work to rearrange the form with code, and probably not necessary.
    Take a look at the attached. It uses a master form bound to tblBooks and a subform bound to the junction table.
    The subform is formatted as continuous forms, and will expand to accommodate any number of denominations for the book.
    You can expand the subform row content with whatever you need, like the logo images.
    This is just a concepts design with no attempt to aesthetics. It took about 15 minutes to complete.
    I've got the zip file you posted and have opened it to take a brief glance. I'm hoping to study it further and better understand it tonight as the shift wears on at the hospital but it's Saturday night and so far, it seem like all of Bexar County is behaving badly. Things should slow down about 5am or so, I hope.

    You'd think people would have more sense than to get seriously ill when such a mean and grumpy chaplain is on duty!

    Thanks. I do appreciate it.

  12. #12
    JAL3's Avatar
    JAL3 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Location
    San Antonio, TX
    Posts
    24
    Quote Originally Posted by davegri View Post
    Seems like a lot of work to rearrange the form with code, and probably not necessary.
    Take a look at the attached. It uses a master form bound to tblBooks and a subform bound to the junction table.
    This is essentially point 3 in June7's post #2.
    The subform is formatted as continuous forms, and will expand to accommodate any number of denominations for the book.
    You can expand the subform row content with whatever you need, like the logo images.
    This is just a concepts design with no attempt to aesthetics. It took about 15 minutes to complete.
    The hospital was unkind last night and my wife treated demands for my attention similarly today when I wasn't sleeping.

    I HAVE been studying what you sent me and trying to make it work but have struck out. I could not get my setup to mimic yours and logged back on with more questions. In doing so, I re-read the text of your post and believe I have located at least one source of my problem. I had bound the subform to a query generating an alphabetized list of the denominations instead of directly to the junction table. A bit of light begins to appear out of the murkiness and I need to explore this further. I shall likely spend a little time fiddling with it before going to bed else I'll not be able to get to sleep at all. I'm up at 4:30 to head to Austin for a dental appointment then back to San Antonio for another shift at the hospital before returning to the coast where my church actually is. It's not likely that I will be back online until Tuesday.

    I do appreciate the time and effort you have expended. I very much want to make this project work.

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

Similar Threads

  1. Replies: 5
    Last Post: 01-09-2015, 03:58 PM
  2. Replies: 3
    Last Post: 12-29-2014, 01:04 PM
  3. Replies: 2
    Last Post: 03-18-2013, 03:03 PM
  4. Open report from data entry form
    By pbuecken in forum Forms
    Replies: 5
    Last Post: 01-14-2012, 12:07 PM
  5. Run report or sub form during data entry
    By wasim_sono in forum Programming
    Replies: 0
    Last Post: 03-09-2006, 05:40 AM

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