Subform Combo Box Source Data

[dmo]

Hello...and thanks in advance for any help.

I got a form with fields: Class ID (unique), Class Date, Class Name
with a subform with fields: Class ID (linked to the same field in Master), OwnerName, DogName

The subform is based on a query; and the query is based on a table. The link between the subform/query/table works fine; if I enter new data in one, it shows up in all. My challenge in the "method" I have to use to enter data.

The fields in the subform are currently text boxes, requiring me to "type" in the new data. If I change to a combo box or list box linked to the query that is the source of the subform, my only "list" options are those entries that are all ready in the source table, which limits me adding "new" info. If I change the record source to another query which has the full data set (such as the source table for Owner or Dog Name), the subform fields give me the full list but are then no longer linked to one another (i.e. I can pick an owner name from the drop down but, it doesn't the limit the DogName field to only those associated with that owner...it still gives me the ENTIRE list of dog names).

Hopefully I've provided enough info on the problem. If not, please let me know what else I need to provide. (Note: I generally work in the form design mode and with macros as I'm not great with writing the code directly).

Thanks, again.

 

[vbaInet]

So your main problem is linking the combo box to the Control Source but maintaining the full list of records and allowing for updateability?

Perhaps you can upload your db for us to see.

 

[dmo]

Your summary of the problems sounds right. I've attached a "modified" version of the database...basically I just deleted records with actual client data and left in the few "test" clients that I had created when trying out data-entry on some of the forms I created. The Form that this particular question relates to is Form "F2_Attendance_Tracking".

Note: this is my first time posting a file; if I've done something wrong, my apologies and any hints on how to correct my mistake would be appreciated.

Thanks for any additional help.

 

[dmo]

One quick note...the "method" currently shown in form F2 for bringing in the data is probably not the most straight forward. I've been playing around with options to "work around" this problem and so have been adjusting and readjusting the form/subform layouts and sources. Also, right now the list of "classes held" and attendees are pulling from one table; at one point I had this split into two (as you can see from the table list). Again, this was just me trying to find a work around to this problem. The method for this "combo box" question will likely drive the final format in terms of how to setup the form/subform as well as the source table(s).

(hope this additional post clarifies what you're looking at when you get into the db)

thanks...again.

 

[vbaInet]

Talk me through how to replicate the problem on the db so I better understand what's happening.

 

[dmo]

Open db; one of the forms is set to open on db startup (close it); open form "F2_Attendance_Tracking"; just to the right of the text box labeled Dog 1, there is a combo box.

Right now, if you click on that combo box, it lists all the dog/breed combinations that are currently in subform "SF2b_OwnersDogs" (source query Q2b_Dog&Owner). However, it's not "linked" to anything else. That is, it doesn't update the owner info, or fill in the Dog ID in the main form. If I change it to a text box, I can link it up with the Dog ID but, then the user can't easily "find" a dog; they have to manually enter Dog IDs (which isn't convenient)

As I noted in previous post, I know the current "layout" isn't ideal; it's just the last iteration I was at when I posted this. I was just trying different form/subform approaches that would get me what I wanted (and haven't gotten it yet). Also, the source table in use right now has "classes" and "attendees" all listed in one place - originally I had these in two tables - I merged them as part of my trial-and-error approach to trying to solve this problem.

My "goal" is to have a form where a user can select a dog/breed from a drop down list, have fields that populate with owner info so they can be sure they picked the right dog, and the tables associated with class attendance will be populated. The trick is, I need to be able to add multiple dogs on one form since most classes have a number of dogs in attendance.

Does this help?

 

[vbaInet]

My "goal" is to have a form where a user can select a dog/breed from a drop down list, have fields that populate with owner info so they can be sure they picked the right dog, and the tables associated with class attendance will be populated. The trick is, I need to be able to add multiple dogs on one form since most classes have a number of dogs in attendance.

You can't use the same combo box control to search for a dog and save the selected dog into the bound table. It's one or the other.

What some people do is have the search combo box at the Header of the main form and have a subform that links to that combo box. The subform then displays a list related to that dog ID and you can add/edit records.

 

[dmo]

UGH! That's basically what I thought. The problem is, if I use the approach you suggest, I have to go to the page for each individual attendee (i.e. each dog), and say that it was in that class. Not very user friendly since in some cases there are A LOT of attendees in each class. My preference is to have a form that says, for Class1, these dogs attended, for Class2, these dogs attended. Which I can do. The problem is, that the attendance tracking in that setup is based on DogID and no one has the ID numbers memorized. Is there no way to do it in this format but, while the underlying table will "save"/"be based on" the DogID, the user "identifies" the attendees based on their names rather than their ID numbers? I.e., multiple combo boxes, I select and attendee name in each of the combo boxes, each box links to a new record in the Attendee table, storing that attendees ID number? (Goodness...I never realized writing what you want a form to do could be so hard to do without writing a book).

I thank you again for your multiple responses. Any additional thoughts you might have would be GREATLY appreaciated. This is driving me nuts. The main purpose of this database is to make attendance tracking easy and, if I can't get this right, the rest of it is almost useless.

 

[dmo]

sidenote: I'm currently scouring this Forum based on a search about "attendance" and have come up with a number of threads related to attendance tracking questions. (I actually see your name offering solutions to some of these questions too )

Perhaps I'll find something in the search results that helps but, any thoughts you have in the meantime are also appreciated. thanks!

 

[vbaInet]

(Goodness...I never realized writing what you want a form to do could be so hard to do without writing a book).

... and confusing because I didn't completely understand what you wrote

Is there no way to do it in this format but, while the underlying table will "save"/"be based on" the DogID, the user "identifies" the attendees based on their names rather than their ID numbers? I.e., multiple combo boxes, I select and attendee name in each of the combo boxes, each box links to a new record in the Attendee table, storing that attendees ID number?

Having the dog's name show but saving the ID to the source of the bound form is not a problem. That's easy. But your form is based on dogs and hence will not allow duplicate records.

 

[dmo]

Let's say I adjust back to the original structure where the form is based on "ClassesHeld" (including fields ClassID, ClassName, ClassDate), and a subform with Attendance (including fields ClassID and DogID). In this way, the main form doesn't include the DogID field (only the subform includes this field) - AND, since a particular DogID would only attend a particular ClassID once, there would be no duplication. Could I then format in some way to allow "determination" of the DogID in the subform based on user selection of the dog name? (I don't know if this would be accomplished through adding a combo field in the subform linked to another query/table that supplies the DogName; or adding one or multiple combo boxes to the main form, or something else altogether; or its just not possible and I need to give up on this approach and figure something else out altogether)

(I promise, I'll drop this thread after this if this last roudn of questions doesn't cause you to have a brilliant suggestion on how to do this)

 

[dmo]

vbaInet!!! I did it!!! (or...about 95% of the way there)
I had the Form based on ClassesHeld (ClassID, ClassName, ClassDate) in SingleForm View so I'm looking at one class at a time. Then there's a subform (SingleForm view) with Dog/Owner info (not linked to Main Form) which allows user to pick the dog from a combo box that then populates the subforms other fields with the owner info. Then I put a button on the MainForm to "Add Attendee" which uses SelectControl to select a second subform (continuous form view) with "attendee" info (this 2nd subform is linked to the main form based on ClassID). The button SelectsControl for the subform, goes to a new record, the does SetValue (linking DogID and ClientID from the first subform and ClassID from the MainForm).

Since the 2nd subform is continuous view, I can see all the DogIDs that have been added to the class that is selected in the main form. The only thing I still have left to do (and hopefully will be pretty easy) is to add fields/subform that are either "in" or, by adjusting the form layout, "next to" the the 2nd subform so that the user can see the DogName associated with each DogID.

Getting close!!

 

[vbaInet]

I'm still struggling to understand what you've just done, maybe because I'm not very good with dogs

Perhaps you can show us a screenshot of what you've just done.

I think you're over complicating matters.

 

[dmo]

See attached screen shot...I wouldn't be surprised to hear I didn't do this in the most straight forward method...(hopefully the VBA/Access short course I'm trying to get my company to pay for will pay off and I'll learn some tricks of the trade).

Note: in addition to what you see in the screen shot, there is a subform "hidden" in the form footer which contains ClientID, DogID, and ClassID - this is the subform that ties all the other forms together.

Any additional thoughts you may be to streamline this would be greatly appreciated (but, at least in the meantime, it's "functional"!)

 

[vbaInet]

Alright I see it.

So what is the top row for? Filtering the subform below?

 

[dmo]

That's correct, pick a name in the combo box in the top row...click the "Add" button, and it adds the name to the list below. I haven't figured out how to get the "Remove" button to work yet but, goal is to use that to remove names if the wrong name is accidentally added.

what do you think? is there and easier (or cleaner) approach I should adopt?

 

[vbaInet]

For your own purposes, that looks good. Good job!

So when a record is added below, it relates to the ClassID in the main form and the DogID in the combo box?

Also, do the Owners get auto populated as well on that particular selection line?