Dogs and Titles - wrapped around the axle a little

[bbilotta]

Hello,


I am not completely unfamiliar with Access, but I use Excel heavily and my linear thinking tends to get me wrapped around the axle in Access.

I am setting up a db for our local dog clubs (2 clubs). right now i'm stuck at the dogs and their titles. I have a dogs table (among others), a TitlesTbl, and a DogTitleTbl to create a many-many relationship between dogs and titles as each dog may have many titles and each title will be listed across many dogs. There are two types of titles, those that come before the dog's name and those that are listed after the dog's name (Trial titles and conformance titles).

If i'm missing something in the structure set up please feel free to let me know.

I need to assign titles to the dogs and i'd assume to do that in a form. I assume the form will have a combo box for the title. I believe i need a TitleID line in my dog's table. Then i think i need to run a query that pulls the dogs and their titles where the titles are filtered by type. Then i can build a concatenated field to combine the dog's name with pre-name titles and post-name titles. So my dog's full name with titles would then simply be a matter of adding a combo box that pulls from that query based on the DogID. I can then add combo boxes to pull that full name on to any forms or build that field in any queries that may need to show that name? And what's the correct way to filter those titles? the TitleTbl has three fields, TitleID, TitleName and TitleType.

Does this basically sound correct?

I am also having an issue with a subform. My db also has an EventTbl that holds our field trial information. So i have many dogs in many trials, another many-many relationship so i have a DogEventTbl to join the two together. I have an EventEntryFrm that keys off of the EventID and DogID. When i pull a dog from the drop down in the DogID combo box i want that ID to populate the sub-form that contains dog information. My problem is that I cannot get it to update on the change. I'm sure there's a piece of event code i cant figure out.
Any suggestions would be greatly appreciated.

[KathyL]

“If i'm missing something in the structure set up please feel free to let me know.”


In any relational database, you can NOT have a many-to-many relationship. What you have to do, is have an intermediary table between the two tables, so that you have many-to-one, and one-to-many.

You're asking a whole lot of design questions. Perhaps too many to be answered here. I'd probably spend over an hour with you as a consultant, to get the best design figured out. Just not something I'd try to answer back to you as you have presented it, as I see some issues to work around.

Kathy/Access I.T. Consultant

[bbilotta]

Kathy,

Thank you and I understand if it is beyond your comfort level. For the purpose of clarification for those who may be able to lend any advice, let me just add the following:

When i refer to the "many-many" i am referring to having an intermediary table. The "DogTitleTbl" for instance is the intermediary that connects the DogTbl and TitleTbl and serves to provide the "many-many" relationship of Titles to Dogs.

[ssanfu]

I am setting up a db for our local dog clubs (2 clubs). right now i'm stuck at the dogs and their titles. I have a dogs table (among others), a TitlesTbl, and a DogTitleTbl to create a many-many relationship between dogs and titles as each dog may have many titles and each title will be listed across many dogs.

Sounds good so far.

Then i can build a concatenated field to combine the dog's name with pre-name titles and post-name titles. So my dog's full name with titles would then simply be a matter of adding a combo box that pulls from that query based on the DogID. I can then add combo boxes to pull that full name on to any forms or build that field in any queries that may need to show that name?

I would not store the concatenated Title-dog name in a field. This is a calculated name that would be better off being concatenated in a query.

Any one dog can have many Trial Titles (TTitle) and many conformance titles (CTitle)?
In "DogTitleTbl", I would have (besides other fields) "DogID_FK", "TTitle_FK" and "CTitle_FK" (and maybe EventID_FK??). Then you can make the name look any way you want. If you want to have a list of titles (T or C or both) it is easy to list the titles.
Or maybe "DogID_FK", "TitleID_FK", "TitleType". Don't know which would be best. Would have to know more about your project....

When i pull a dog from the drop down in the DogID combo box i want that ID to populate the sub-form that contains dog information. My problem is that I cannot get it to update on the change.

Do you have the Parent- child links set between the main form and the sub form using the correct fields?
In design view, double click on the sub form control to see the linking fields.

My $0.02 ...

[June7]

One thing at a time. I'll go with the last item. Not sure what you mean by 'cannot get it to update on the change' - exactly what is not updating? The form/subform setup is main form bound to EventTbl and subform bound to DogEventTbl with Master/Child linking on EventID fields and combobox to select dog in subform?

[Dal Jeanis]

Your overall strategy sounds fairly good. I usually suggest that new folks pop over to Access MVP Roger Carlson's site at http://www.rogersaccesslibrary.com/ and review his tutorials on database design and application design.

As far as titles, since a dog can earn any number of them, the records of which dogs have earned which titles obviously have to be in a separate (junction) table. You'll probably need precedence rules for the titles as well, to determine the order. I'd suggest just having a TitlePosition integer for each title, with negative numbers being before the name and positive after it or vice versa. When initially setting up the titles, number the TitlePositions by hundreds or something to be sure to allow for inserts between them.

[bbilotta]

Steve,

Thanks for the reply.

I could have been more complete in my explanations but didnt want to drag on too long. I planned on the concatenated fields to be calculated, or built, in queries, not in a table field, so i think you're supporting what i was thinking. I think my terminology is simply not up to speed just yet.

Any one dog can have many Trial Titles (TTitle) and many conformance titles (CTitle)? Yes, that's correct.

In "DogTitleTbl", I would have (besides other fields) "DogID_FK", "TTitle_FK" and "CTitle_FK" (and maybe EventID_FK??). Then you can make the name look any way you want. If you want to have a list of titles (T or C or both) it is easy to list the titles.
Or maybe "DogID_FK", "TitleID_FK", "TitleType". Don't know which would be best. Would have to know more about your project....

OK, great, this is where i start to get a headache, from getting wrapped around the axle. I went the same way. My DogTitleTbl does have DogID (a FK), and TitleID (a FK). Currently my TitleTbl houses 18 items, 8 of the TitleType "Trial", and 10 of TitleType "Conformance". I too started thinking maybe i needed separate tables of TrialTitles and ConfTitles, but i think that was only because i couldn't figure out how to show them separately in a query. The only reason I need them separated is to show the Trial Titles ahead of the dog name and the Conf. after the dog name. They are not used to designate dogs in any way (I assume that might be something you'd be wondering in terms of needing to know more about my project).

Do you have the Parent- child links set between the main form and the sub form using the correct fields?
In design view, double click on the sub form control to see the linking fields.

When you refer to Parent/Child link i assume you're referring to telling the cboDogID on the Form to feed the DogID on the subform, and if so then yes. The subform will update if i leave that record and come back to it, but it will not update immediately when i make the change within the DogID field on the Form. I was thinking i need an "on Change Event" to force the cboDogID to feed the DogID on the subform as soon as i make a selection in that cbo.

Thanks again for your help. I'm gonna love this site and plan to scroll thru as many of the posts as i can.

[bbilotta]

June7,

Not exactly. my Form is bound to the DogEventTbl, a table to join the DogTbl and EventTbl (Many dogs will be in many events and one dog may actually be listed twice in the same event). So the Form is bound to the DogEventTbl. The subF is bound to the DogTbl and is used to provide dog information. I'm using DogID to to link the two. cboDogID on main form feeds DogID on the subform which then pulls appropriate Dog information on to the subform making it visible to the user.

But if that's wrong, please feel free to let me know.

The issue is that the subform will not update upon the change of DogID on the Form until you leave that record and return to it. All existing records would appear correct if you scroll thru the records, but when you enter or new one, or make a change to an existing record, the subF will not update until you then leave that record and come back to it. when you come back to it, it will show the correct dog information on the subF.

[bbilotta]

Dal Jeanis,

Excellent advice on MVP Roger Carlson's site I'm sure. And i'll be sure to add it to my list, thank you.

I do have a junction table between DogTbl and TitleTbl called DogTitleTbl. Titles are not precedent necessarily. there are some that precede others, but a dog is given one title at a time in those cases. Senior Hunter (SH) precedes Master Hunter (MH). However, a dog will either have an SH title or an MH title. We don't track the status of any particular dog, we're only trying to make sure we state the title(s) they do have.

[Dal Jeanis]

bbilotta -

Post #8 sounds like you need a refresh or requery. For instance, Me.Requery and/or Me.[SubformControlName].Form.Requery should handle that.

Replace [SubformControlName] with the name of the subform control on your main form, and put the code in some appropriate place in the main form.

[bbilotta]

Dal,

thanks for that. I'll give it a try.

Right now i'm wrestling with a DLL error. Not sure what has caused it to start occurring, but it's affecting all the control wizards. i've tried re-registering the DAO and Msado. i've done a compact and repaire, i've run MS diagnostics and gone directly into the "repair installation" option on the install disks - all yielded nothing.

Doesn't tell me what specific DLL issue it's having other than just immediately kicking out a "Error in loading DLL" message when i try to load a button.

At this point i'm down to removing and reinstalling MS Office 2007 which of course requires backing up Outlook, tons of fun. It's also giving me the error on a new table as well.

[June7]

Your form/subform arrangement sounds backwards to me. Consider options:

1. main form bound to DogTbl and subform bound to DogEventTbl with combobox to select event

2. main form bound to EventTbl and subform bound to DogEventTbl with combobox to select dog

In each option, if the desired item is not in the combobox list, use NotInList event to open data entry form to add record and update the combobox. Review: http://www.blueclaw-db.com/access_no...ed_example.htm

[bbilotta]

June7,

thanks for the input.

The purpose of the form is to enter dogs in an event. In light of that, i generated the form off of the DogEventTbl (the junction between DogTbl and EventTbl). The record created by saving that form or generating an entry will be stored in the junction table under its own EntryID. Each EntryID contains dog information, thus the link to DogTbl, and event information thus the link to EventTbl. I do use a cbo to create the drop down to choose the event for which this entry applies, and a cbo to choose the dog being entered (pulling from a DogQry via DogID(FK)). On this main form you also enter other information related directly to the entry such as EntryFee, PmtType, Check#, and the stake being entered Open, Amateur, or Puppy (Dogs can enter more than one stake - Open, Amateur and Puppy are individual Check Box fields in the DogEntryTbl)

So these items make up the "entry" information. When you create the entry and choose the dog i'd like to show the dog's information (DOB, Sex, Owner, Breeder, Sire, Dam.....) and this is where the subform comes into play. My intent was to pull that subfrom info from the DogID chosen above, in the main form, via the cbo pulling from the query. And it works, just not as well as it should.

The form is not meant to drive "Event" information, nor is it intended to create or change "Dog" information (though if a dog is not listed the information must be input at the time of entry - a sub-project i'll take on later). There are seperate forms to create an event and to create a new dog.

Does that clarify the reason for binding the form to the DogEventTbl or do you still think i'm approaching it incorrectly?

But like i said above, my real challenge right now is the fact that i've got this DLL issue i cant seem to solve.

[June7]

I think option 2 applies.

A dog can be in an event more than once? Different divisions?

Another possibility doesn't involve subform. Include EventTbl and DogTbl in the form RecordSource, join type "Show all records from DogEventTbl ...". Bind textboxes to the fields from EventTbl and DogTbl, set them as Locked Yes to prevent edit. This will display the related information and no code required to refresh after dog or event selected in comboboxes.

However, would still need the NotInList event code if desired item not in either combobox.

[bbilotta]

Yes, Many dogs will be in many events, and a dog can actually enter an event in the Amateur stake and in the Open stake. Theoretically a dog could enter all three stakes, though that's not very realistic.

Professional dog handlers cannot be involved in the Amateur stake, but amateur handlers can be involved in the Open. So it's not relative to the dog so much as who's handling the dog. And this brings up a point not yet discussed, but not giving me problems at least not yet. There is a PeopleTbl that contains owner information, breeder info.... Any "people" involved in the clubs or events in any way are stored in the PeopleTbl. One of the fields in this table is "ProfHandler". This field will also be pulled into the form via PeopleID(FK) and cbo. (probably will be pulled from a query used to build a "FullName")

I like the idea of binding text boxes directly on the form from cbo boxes and locking the text box. the cbo used to pick dogs uses DogID and the DogID could then be the feed for the text boxes, correct? Cleaner than a subform. Some of the info i would need to pull in comes from a query that was used to concatenate information regarding the breeder and owner (sometimes there's more than one of each, and some of those are husband and wife while others are two seperate first and last names. Had to use IIF statements and concatenation to develop these name fields correctly).

In any event, can i bind the text boxes to the query?

Then, as a shortcut for now, i could stick a button on the form that would allow a user to create a new dog record in case the dog they're entering is not already part of the DogTbl. (I'm sure there's a more elegant way of entering a new dog right from the cbo but i'll work on that later).

I can't tell you how much I appreciate having found this forum and your generosity in helping me out. Thank you again.