Populating ListBoxes and ComboBoxes with an SQL Query

[ChrisPatterson]

I'm building a form and I'm having trouble with my SQL query to properly populate a list box. In this database, I have a table of families and then a table of patients within that family (Relationship is one to many, linked by a FamilyID field). The form displays the Family's demographic information, and I'd like to include a ListBox that includes all of the Patients within that Family (which I then hope will allow the user to navigate to the forms associated with each Patient). I set up a query to gather the FamilyID, PatientIDs and other demographic information that I wanted to include, and used it to populate this ListBox.

Here is the SQL text from the Row Source of the List Box:

SELECT 
   [queryFamilyPedigree].[FamilyID], 
   [queryFamilyPedigree].[PatientID],
   [queryFamilyPedigree].[Expr1],
   [queryFamilyPedigree].[DOB], 
   [queryFamilyPedigree].[Expr2], 
   [queryFamilyPedigree].[Expr3], 
   [queryFamilyPedigree].[Expr4] 
FROM [queryFamilyPedigree] 
WHERE FamilyID=[Form]![FamilyID] 
ORDER BY [PatientID];

It doesn't seem to matter what I do, the ListBox is always blank (The column headers appear properly, and I don't get any error messages). To make sure that I had gotten my SQL query built correctly, I created a ComboBox and copied the SQL text into its Row Source (its exactly the same) and the Combo Box populates properly with all of the Patients for that family. Although the the options in the Combo Box remain the same when I switch records to a new family (I figure this will be fixed by a requery event, but I haven't progressed to the stage of implementing VBA scripts quite yet). Does anyone have any ideas? Is there a property on the List Box that I'm missing that might be causing the issue? I'm at a loss.

Danke,
Chris Patterson

[zmbd]

Chris,
There are several things that can cause you some issues, normally it is because the SQL is either faulty or there are no records being pulled.

However, as the SQL is good in the combobox then I suspect this has to do with the List box properties.

Under the format tab, make sure you have the column count set correctly, the column widths set correctly, and that the width is set correctly (or you cut of the ends :) ).

I have some code that will pull the properties out... let me hunt it down and I'll post it in a minute if the above doesn't help.

-z

[twinnyfo]

Chris,

Although I admit that I am not exactly certain what you are asking..... You might be answering your own question when you say, "Although the the options in the Combo Box remain the same when I switch records to a new family (I figure this will be fixed by a requery event, but I haven't progressed to the stage of implementing VBA scripts quite yet)."

This is a very simple operation using VBA, on the After Update event when you select a new family:

Me.ComboBoxName.Requery
 

If you have no experience at all using VBA, you may want to do some research.... I would provide some links to get you started, but I am away from my desk right now.....

[ChrisPatterson]

Thanks for the quick replies. I don't very much experience with Visual Basic, but I have done some C++ programming, and I've got a couple of books on VB and Access that I'm working with to help me with the stuff I don't know. When I can't find the answer in there, I tend to look online, so any sites you want to recommend, I would greatly appreciate. I have read about events and a couple sections for programming them, but as I said earlier, I'm taking this one step at a time. This is the first database that I've constructed, but I've worked with them for many years. I will give your ideas a shot and get back the results after the weekend.

Danke.

[zmbd]

Twinney,

Unless Chris is changing the recordsource at runtime in either the onload or onopen events, or there is some other control changing the recordsource during one of its events, then the the SQL in OP should populate the control and work without a requery of either the form or the control.

HOWEVER

You did force me to take a more careful read of the OP...
My thought is that Chris should actually be using a subform... ( I just don't do my best work when trying to talk to the wife, cook dinner, herd the kids into the tub... oh wait... to the table... THEN the bathtub! :) )

http://bytes.com/topic/access/insigh...filtering-form

What I can see happening here is
- Parent form with the Demographic information as OP using the table families as it's record set
- Child form based on the SQL string in OP shown in datasheet (or directly on the table depending on the data types and user friendlness of the table).
- If the Child form is left open for updates and additions that could be useful... the form could actually be locked and a command button used to unlock if needed.
- The user can then select the record within the Child form.... a few designs... I've used an unbound control with an onclick event to launch code... or a command button on the parent form depending on what is being done.

Just a thought!

-z

[twinnyfo]

Z,

I agree, that based on the query, it appears that the best solution is a form-subform setup, in which selecting the family automatically filters the subform. However, the original question appears to want to populate a list box based on the value(s) on the form, which, I believe, would require a requery of the control.

----------------

Chris,

Could you please explain exactly what it is you are trying to do here? Either Z, or I, or any of the other gurus here would love to help, but, as you can see, even Z and I are approaching things from two different directions.

If you can, provide some additional details and we all will work toward a solution.

[zmbd]

Twinny,
Partially right... this is simply not my best work from home...

K... so... I missed the "where" clause in the OP sql...
Placing the code tags and stepping the SQL should have been my first step in helping OP.
Also, I missed the fact that OP was talking about two different comboboxes and a list box in the final paragraph... this is where I finally chose the door with the lion (or was that a tiger... claws and teeth either way!)

Do we really have two questions? Or a single question that could be solved by a requery.

For this one... I will have to go with Twinny and say try the requery on the listbox in the afterupdate_event of the ([form]![FamilyID] control).

I still stand by my suggestion for a form/subform. Done correctly, a lot of VBA code can be eliminated and the work pushed to the SQL engine.

-z

[ChrisPatterson]

Okay, I'm following along with the form/subform suggestion. I had tried that previously, but didn't like the presentation of the data as much and I had issues with not all of the records showing up (which I've now determined was because I didn't understand the INNER JOIN vs LEFT JOIN statements in the SQL query). I couldn't follow the link that you posted for the "Example of Filtering on a Form", but I'd still like to see it to look at other ideas for better organizing this data. Also, forgive me, I'm not sure what the term "OP" refers to.

Twinny's suggestion for the requery script, did work with some minor changes. It didn't work under the After Update event for the FamilyID control on the form, but I think that is because the field isn't being used to navigate to a specific record, so it doesn't get updated. However, when I put the same script under the On Current event for the form, it updated both the list box and the combo box appropriatedly for that record, and it changes with each record as I scan through them one at a time. SO IT WORKS!!!

I still need to put in some navigation tools in to better access a specific record (I'm currently clicking through each record one at a time). Now that the List Box is populating correctly with all of the Patients within that family, I'd like to set up a couple of command buttons "New", "Update", and "Delete", where "New" opens up the a blank version of the FORMPatientData (but with the FamilyID field prepopulated with the current FamilyID being viewed on the FORMFamily), "Update" opens the FORMPatientData to the record selected in the List Box we just set up, and "Delete" deletes the patient record selected in the List Box.

How do I reference the selection that was made in the List Box? I figure that the process of creating the VBA script will be a much longer discussion. Should I continue it here or should I create a new thread to ask these questions?

[ChrisPatterson]

Also would posting screen shots of the form design help?

[zmbd]

Chris,

Fixed the link in the post... don't know what happened there; however, it does appear to be working now.
If you still can't get there from the link... go all the way to the bottom of this page, click on the "Microsoft Access / VBA Insights" link... the article is #165. You might also find the cascading combobox referenced in this article to be usefull (#39) (and you'll also get some help refernceing the listbox/cbo selction there)

Record movement can be a pain... the cascading cbo may be usefull there... topic for another thread.

As for the remainder of your post, buttons, navigation, etc... these questions deserve their own thread and we will gladly help you work through those questions as you post them (and it's in the rules ;-) )

-z