Listboxes and SQL Queries 1

[burritonator]

I have a form with 2 Listboxes. I use a simple SELECT query to populate the first Listbox with values from a table called "Departments". The second Listbox will be populated with records from a table called "Devices", which displays a list of all computers, printers, etc. that are located in a particular department.

Here is the problem that I am having: The values displayed in the Devices Listbox need to be based on which department is selected in the Department Listbox. However, I can't figure out a way to directly reference the Value property of the Department Listbox in the SQL query that populates the Devices Listbox.

I currently have the above scenario implemented in the following way, and it works for the most part, although I am having problems getting the Listboxes to behave exactly the way I want when the form is used to add or modify records.

Currently, I have the Departments Listbox located on a subform that is embedded into the main form. The subform has the Departments Table set as its Record Source. The Devices Listbox is located directly on the main form, which has the Devices Table as its Record Source. When the user clicks a department in the Departments Listbox , I am actually writing the value of the Listbox's Value property to a table called "DataHolder" that only holds one record at any given time. I then use the value from the record in DataHolder in the query that populates the Devices Listbox. This works to an extent. Clicking a department in the Departments Listbox does cause the Devices Listbox to be populated properly. However, I am having unpredictable results when it comes to setting both Listboxes to the proper values when a new record is added, etc.

I am assuming (or at least hoping) that there is a better way to accomplish what I've described above, I just haven't been able to figure it out yet. The bottom line is that I hope to find another way to reference the Value property of the Departments Listbox in the query that populates that Devices Listbox without having to deal with the subform and the "DataHolder" table. All help will be greatly appreciated.

Thanks,
Burrito

 

[Crowley16]

put in your 2nd list box's recordsource:

"SELECT blah, blah FROM tblBlah WHERE Department Like Forms![frmName]![ListBoxName];"

or something to that effect...

 

[Crowley16]

Oh, forgot to mention...

you'd probably want to put a 2ndListBoxName.refresh somewhere in the 1stListBoxName_AfterUpdate() event

 

[burritonator]

Well, it seems that in most cases, whether I work on a problem for a few minutes or a few days before posting, I find the answer shortly after posting. In this case, it seems that I was just overlooking something simple. I thought I had tried every way imaginable to reference the value of a Listbox in a query, but apparently I had been making it too complicated. I did some more experimenting, and it turns out that I was able to acheive the desired result by doing away with the subform, moving the first Listbox back to the main form (so that both Listboxes are on the same form), and using the following query:

"SELECT tblDevices.NetworkID, tblDevices.DevicesKey, tblDevices.Location FROM tblDevices WHERE tblDevices.Location=lbxDepartment.Value ORDER BY tblDevices.NetworkID;"

I'm as shocked as you probably are that I didn't figure that out 2 days ago, but I thought I had already tried it, which is why I had moved on the more off-the-wall methods of solving the problem.

Burrito

 

[Crowley16]

lol, happens to everyone, probably...

probably still a good idea to put the refresh in though...