Page 1 of 4 1234 LastLast
Results 1 to 15 of 57
  1. #1
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60

    Mutlivalue Jquery/table in subform bound to main form

    I continue to look for a video that will help point me in the right direction on this, but i have myself a little wrapped right now.

    I have Dog Events that have Stakes and Entry Fees.
    EventTbl
    StakesTbl
    StakesFeeTbl



    The Event table only has Event related information. the Stakes table is a list of various Stakes (Open, Amateur, Puppy, Water, Junior, Senior, Master). An event typically has three stakes and each stake has a fee. Sometimes the fees are the same for each stake, sometimes not, and fees vary from year to year.

    The StakesFee table contains the key field StakeFeeID, foreign keys EventID and StakeID, and a field for Fee (currency).

    I have a form to create the Event. I "think" i need to create a subform that allows me to select multipe stakes and assign a fee to each stake. I can create the query to pull the StakeID and EventID together, but i'm stuck as to how to create a subform that grabs the Event ID from the main Event form and stores it in the StakeFeeTbl and allows the user to select various Stakes from a dropdown and assign a fee to each stake.

    DogRebuild3 (2).zip

  2. #2
    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
    JQuery??

    Suggest you tell us what you're trying to do in plain English.

    Sounds like you're trying to sum the relevant fees.....
    Good luck

  3. #3
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Hi Orange,

    No, not trying to sum.
    I'm trying to set up the Event. The event has Stakes (typically 3), and those stakes have an entry fee.

    When setting up the event we need to determine what the entry fee is for each stake. I'm trying to figure out how to allow the user to select which stake(s) are applicable to the event, and enter a fee for each stake.

    does that make more sense? Sorry if i over complicated it the first time.

  4. #4
    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
    Sounds like the following set up (best guess)
    Click image for larger version. 

Name:	eventStakeFee.jpg 
Views:	63 
Size:	18.0 KB 
ID:	27781

  5. #5
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Yes, that part i have.

    The wrapping around the axle comes when i try to get that onto a form and enable the user to both pick a stake and enter a fee.

    I'm wondering if i need a subfrom within a subform.

    I know i need a subform on my main form to handle the picking of the stake. i created a subform based on the StakeFeeTbl, made it a continuous form, dropped in a combo box that selects the stakeID from the Stakes table and stores the value in the "StakeID" foreign key field of the StakeFeeTbl. That allows the user to pick the stakes.

    Now i'm trying to figure out how to allow the user to enter a fee amount associated with that Event/Stake combination.

    And i was over-thinking it!! i just figured out that all i needed to do was add the fee from the existing fields of that subform i created.

    My biggest problem with Access always seems to be allowing my brain to run out of control. LOL

    Thank you for the support.

  6. #6
    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
    This was a mockup to indicate:
    -there could be 1 or many Events
    -an Event could involve 1 or many Stakes
    -A given Event and Stake has a Fee
    -An Event could have 1 or many Fees
    -The Fee is dependent on the Event and Stake. That is, Stake does not have a set Fee.

    To add up all Fees for a given Event, you would have to loop through the EventStakeFee table and sum all fees for that Event.

    Where does JQuery fit or was that a typo??

  7. #7
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    JQuery was me wondering if i needed to create a query to combine Events, Stakes and Fees and use that Query somehow on the main Event form. Too much thinking.

    At this point i'm trying to walk through each step of the process, creating the forms that i need along the way.

    I think i have my tables set (except for any Junction tables i have not envisioned as yet)

    The first step in the process is creating an Event. So i had to build a form that allowed the user to create an event. Part of setting up the Event is establishing the Stakes and the fee amount for each stake. That's where i was stuck. The rest of the form's combo boxes tied back to existing tables, or queries, and a few text boxes for transient info not stored in tables.

    The next step, my current step, is creating an "Entry Form". Once the user has set up the Event, they can now accept "entries". The Entry form is a bit trickier because you have to enter a dog. Easy enough if the dog is in the database. But when it's not i have to take them to a new dog form (which i'm building now). The new dog form requires subforms that allow the user to enter Titles and Obedience Titles (the work you helped with with prior to this), not too tough, figured that out.

    However, LOL, now I have to figure out how to create the combobox for the Sire and Dam. And i'm a bit confused here, and this is where i start to get wrapped around the axle; Do i need to simply pull the name from the DogsTbl and set up a "not in list" event that allows them to put in yet another new dog ?

    I shouldn't really say i'm stuck here. i've only just begun working on these embedded subforms for the entries. i'll grind on it for a while before i ask for more help. LOL

  8. #8
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    So here's a question:

    I want the user to be able to select dogs by their Registered Name, but once they select that dog i want them to see the concatenated result of Title&RegisteredName&ObedienceTitle. They need to ensure the dog, even if existing in the table, has the correct titles associated with it. would i need to set up two combo boxes, the first feeding the second? or a 1-line subform with the resulting concatenation?

    Unfortunately, with a combo box, you're limited with what you see after your selection. You can see multiple columns when you drop it down, but only the first of those columns will be the only visible column (unless i'm wrong on this). I'm wondering if i could see the RegisteredName in the first column and the concatenated name in the second then show the concatenated column once the selection is made??

  9. #9
    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
    Hmmm. To me you are into the nitty-gritty of interface design and you don't yet have the scope defined well enough to build a high level model. The analogy (to me ) is worrying about the gold-plated picture frames in the executive suite, before you have determined the artist's concept for the building and before understanding the permits and regulations of the local building code.

    You aren't the first to approach things this way. But, in my view, it is better to determine what is to be included in this application and ensure that your proposed tables and relationships support it. It may lack details, but entities will be defined.
    For each of the subjects in your proposed database --eg Dogs, Owners, Events, Titles you have some basic processes. These are pretty generic in most data bases---Create, Read, Modify and Delete. You will have different requirements when it comes to ensuring you have the data to satisfy reports, forms etc.
    If you read through posts on most forums, you'll see a common theme that getting your tables and relationships identified and tested to ensure they support your requirements is critical.
    Do you have a point form outline for the major "processes" involved in the Dog Breeding and Events "Business"?
    From that can you determine which Entities/Subjects are involved?
    Who is the sponsor and ultimate user(s) of this system?

    You can mockup/prototype some of these Processes and their logic by means of a series of Msgboxs.
    You identify a Process eg: Add a new Dog to the system.
    Interact with Owner; get particulars of Owner/review data if existing Owner; Get Details for Dog; Verify Dog details.....
    Once you get an overview flow, and you know the data required, you can move from MsgBox to real Objects (forms, listboxes, combos..).
    We used to call this sort of approach --"stub processing". You created something that mimicked the Process flow and offered a menu or button selection to do some of the things I listed above. When a button was clicked, a msgbox appeared indicating "This is where we get the Owner and Dog Details", then went to the next process. This was good to show users/mgmt the general flow, and often got early feedback to alter the flow or change a processing sequence etc.
    This iterative approach gets you more familiar with the "business" and the details within and it's all part of development.

    What you want to avoid is months of detailed work as you see things, only to find it doesn't really do what others want/expected.

    Anyway, there are many ways to "skin the cat" --no one answer for all situations.
    If you are unsure of a particular process and how it should/could be implemented, you can mock something up and test it.
    You'll learn some vba and write some simple functions that will be helpful as you progress.

    Others may have different views. No matter what path you choose, the forum will be here to offer advice and assistance.

    I don't remember if I suggested and gave links for BA-Experts who offer short youtube videos related to Requirements gathering, analysis approaches and tools. They're good at concepts and are humorous.

    Lots of combobox info here.
    Good luck

  10. #10
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    A point not lost on me. As a db/sql expert, and likely a professional, i'm sure you're absolutely correct.

    But i have the unfortunate disadvantage of NOT having proper fundamental foundations beneath me and i have to attack the problem in a way that allows me to work my way through it.

    If i had the time and resources to start from the very basics, i'm sure i would be approaching it differently.

    I've been attempting to tackle this job since 2013. Time and again i would get partially into it, learn bits of Access, then get frustrated and toss it. Throughout those attempts I ultimately suffered from exactly what you describe above. I'd get deep into "production" and find out i'm lacking many fundamental pieces.

    This time is a bit different however. I'm starting with much better structure than before. And i'm trying to tackle all of the "boring" structure details before moving forward. Unfortunately for me, i don't know enough to truly know what the entire picture should look like at this point.

  11. #11
    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
    You have learned more than you think if you have been "mulling over" this project since 2013. To me it shows perseverance.
    Perhaps you can focus on one area, mock it up, test it and alter as necessary. You have experienced some issues and know to avoid them now. You don't have to go back to the beginning; you have some things that work and a clearer picture of what you want to do.

    Here is a design approach that has been referenced many times.

  12. #12
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Thanks Orange. i will take a look at the references you've offered.

    I'm knocking off for the night. I'm stuck on this Dog form. I'm trying to create either a Combobox that populates a combobox, or a combobox that populates a subform and cannot get it to work.

    When entering a new dog the user would hit a combobox to select the Sire for the new dog. The Sire value is a dogid that is being stored in the field Sire. By using the criteria [Forms]![NewDogFrm]!SireCbo I should be able to populate a second Combobox that gives me the FullNameTitle from my DogTitleQry. unfortunately it will only give me dogs with titles and for some reason is ignoring dogs without titles.

    So i thought i'd build a subform and let the the "Sire" value produce the the subform result by linking the DogID on the Subform to the Sire on the master, but that is not working for me either.

    The idea here is that the user could select from a list of Registered Names only and when they make their selection the Full Title Registered Name would appear in either the second combobox or subform.

  13. #13
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Ok Orange, I'm at a point of "Uncle".

    I have figured out why I'm having problems generating the Full Name and Title of Sires on my New Dog Form and subform, but i don't know why exactly.

    The problem begins with the fact that my DogFullNameQry is only showing dogs that have Sires and Dams - and i understand why - the relationship is set to only show equal values. The problem is that when i try to change the relationship between the DogTable in this query to show ALL Sires and only those from the Sire table that match (2), i get an error.

    I then tried to copy my db, strip it down, and rebuild some queries to isolate the problem. I think i made a mistake when creating the Obedience portion of the work, (to refresh we're after Titles - FullName - Obedience Titles). So when i create the query to generate the Concatenated_Title & RegisteredName & ConcatenatedObedTitle all is fine. When i try to add a second Dogs table to tie my Sire and leave the relationship between the DogsTbl.Sire and the second instance of DogsTbl set to 1, I only get those dogs that have a sire (as expected). However, when i change that Sire relationship to 2 i get a fatal error that forces me to shut down Access from the task manager. I think it has something to do with the ConcatRelated language as it's referencing DogId.

    At one point Access also told me i had ambiguous relationships that it didn't know how to process so i thought i'd try building it in separate quires (DogFullNameQry and SireFullNameQry), this again is were i run into the same errors.

    Any ideas?

    This is getting to the point where i feel i should be paying you for your time. if that's the case, and you have a mechanism for it, please let me know.

    This image shows the DogFullNameQry that successfully provides the Title & RegisteredName & ObedTitle.
    Click image for larger version. 

Name:	2017-03-09_12-19-54.jpg 
Views:	62 
Size:	93.8 KB 
ID:	27792

    When i try to add the prebuilt SireFullNameQry and tie it to DogsTbl.Sire on a type 2, i get this error:
    Click image for larger version. 

Name:	2017-03-09_12-23-30.jpg 
Views:	61 
Size:	37.7 KB 
ID:	27793

    Including the file
    DogRebuild4.zip

    Additional Attempts::
    So it seems Access is getting hung up on the DogID in the ConcatRelated function. So i tried to build a query with the DogsTbl, JConcatenatedDogsandTitles query, ConcatDogEbedTtlQry, a second instance of the dogs table called Sire, and a third instance of the dog table called Dam. When i run this with the proper relationships i get the full name of the dog including titles, and the registered name of the Sire and Dam and all dogs are included.

    Then i thought i would manually write the ConcatRelated code in this query and substitute the [DogId] from your original work (the thrid argument from the ConcatRelated you wrote) with [Sire].[DogID], but it didn't like that at all. So i tried again and this time substituted [DogsTbl].[Sire] trying to get the ConcatRelated to compare the DogID from the JDogTitles query to either the DogID from the Sire table or the Sire from the DogsTbl. The thought was to get ConcatRelated for the Sire to look for a Sire ID from either table and use that ID to match up to the DogID foreign key in the JDogTitles query you wrote.

    Sire: ConcatRelated("Title","JDogTitles","DogID=" & [Sire].[DogID],"TitleID"," ")
    or
    Sire: ConcatRelated("Title","JDogTitles","DogID=" & [DogsTbl].[Sire],"TitleID"," ")

    Access didn't like either attempt. Same error on both.

    Thought:
    Do i need to create a junction table where Sire is the primary key and dogid is a foreign Key, relate the junction to the DogsTbl, then create a ConcatRelated for the Sire so the ConcatRelated is looking at the primary key of this new junction table? Not sure "junction table" is the correct term here since we're not creating a many to many, just shifting the self-join to an ancillary table. I guess i would have to also link the SireID from the DogsTbl to the SireID Foreign Key in the new table so they stay bound?? Seems odd.
    Last edited by bbilotta; 03-09-2017 at 03:37 PM. Reason: Thought

  14. #14
    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 added a table (data_dictionary) and module(DataDictionary) to your database. If you open the form frmDocumentTablesToDictionary and click the button, it will execute a routine DocumentTables.
    This reviews each table in your database and lists various properties.
    The table is recreated whenever you click the button.

    The revised database is in the ZIP.


    Your Events table is "poorly designed". It contains several hidden entities. People with roles, Secretary, Chairman....Judges.

    StakeFeeTbl is poorly named. The Fee is associated with an Event and Stake. Better name would be EventStakeFeeTbl. It's minor, but it hides a fact that is important to you and this database.

    I don't think you need all the subforms. It seems to me (who doesn't know the details) that you could use combo or list boxes on forms to support the Form involved. Need to know more.

    Here is a list of your tables:

    CubTbl
    DogObedTtlJTbl
    DogsTbl
    DogTitleJTbl
    EntriesTbl
    EventsTbl
    ObedienceTbl
    PeopleTbl
    StakeFeeTbl
    StakesTbl
    TitlesTbl

    I recommend you write a 2-3 line description of each to help with your design and development; and for communicating with others.

    It also seems you have created a number of queries to do specific things, and to be used for other purposes. My view is that if you get your tables and relationships set up to support your "overall business" you may reduce the number of queries required.

    I'm not sure how /why you have so many Reports at this time.
    For your own info, and for communication, you might want to mock up such reports. This can be very useful when identifying Recordsources (data in - data out). I found most of them do not have the associated record source defined. Perhaps your reports are your "preliminary ideas for such reports".

    In general you have done a lot of work. You're focused on how to do certain things and using Access constructs in your discussion

    Do i need to create a junction table where Sire is the primary key and dogid is a foreign Key, relate the junction to the DogsTbl, then create a ConcatRelated for the Sire so the ConcatRelated is looking at the primary key of this new junction table?
    ConcatRelated is a function that can be called when needed. You don't necessarily need to keep queries that use ConcatRelated.

    I think the underlying requirement here is the Sire and Dame for each Dog has to be identified and recorded. It will be needed in some reports/forms... in future. This is the kind of thing that needs to be identified. These are the facts that lead to table design and relationships.

    An Event is..... For each Event there is a Location, there are committees with members. The committee members have responsibilities. Judges....

    Anyway, it's getting late, so I'm done for the night.

    Update: Mar 10

    I have added a draft data model based on posts and best guesses.
    Click image for larger version. 

Name:	DogEventStake_Toad_0.jpg 
Views:	61 
Size:	74.5 KB 
ID:	27809
    Attached Files Attached Files
    Last edited by orange; 03-10-2017 at 10:14 AM. Reason: Added a draft data model (toad)

  15. #15
    bbilotta is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2013
    Posts
    60
    Orange,

    I appreciate all the feedback, good or constructive.

    I don't understand what you're saying about the Event table. I'm not disagreeing with your thought that it is poorly designed, it may very well be and i don't know enough to recognize it. An event has a chair, a secretary and a comittee with 4 or 5 members. Are you saying i should not store these roles in the event table? Since they are unique to each event, i would have thought that to be acceptable.

    I agree with your statement about the name of StakeFee table, and i have improved in that area as i've progressed. Still make mistakes once in a while, but i see what you're saying.

    I'm sure you're correct about the subforms, and it's still a work in progress. i'm finding my way using comboboxes instead of subforms, but i ran into a problem trying to get the combo box to populate when i assign a value from another combo box. the combo box seems to correctly display the item I'm looking for when i hit the dropdown, but you don't see it unless you hit the drop down, so i reverted to a subform to display the one line of information. Additionally i'm sure there's still some trial-and-error forms in there that need to come out.

    And yes, with respect to the issue that started that novel i wrote above, i finally figured out that i don't need to build out the entire query with full titles and names of Dogs, Sires, and Dams - that i can call them on on a subform and display them in my main form. Still sticks in my head however that i'm not able to combine them in a query without error. i'm sure its a coding issue, just one that's way outside my ability to diagnose right now.

    i'm sure it gets difficult for someone of your ability to watch someone like me flounder on conceptual issues you've mastered. I'm still looking at minute "how-to" issues while you're able to see the entire board, as it were - from an overall SQL perspective.

    BTW the reports that you see are remnants from the previous version of this db. if you saw it you wouldn't even laugh, you would just shake your head. they were basically using Access as a spread sheet. all the dogs, owners and handlers were repeated in two or three tables and added again and again as needed. No Event table at all, they would simply copy and paste the entire file for the next event. So each event, 3 per year, 20 years, are all in separate files. Of those reports, really only the Catalog-Open, Amateur, Puppy, and the Owner report are the only ones being used. And those are exported to Word so they can be put together into a catalog. Not my work, just holding it there as a placeholder.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-04-2015, 07:25 AM
  2. How to filter bound subform from unbound main form?
    By ittechguy in forum Programming
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  3. Replies: 1
    Last Post: 01-16-2015, 09:28 AM
  4. Replies: 6
    Last Post: 11-30-2013, 02:41 PM
  5. Replies: 2
    Last Post: 08-01-2011, 11:35 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