Form to run query based on another query

[Steve D]

I am trying to create a form that will allow me to choose multiple sites from
a list of sites which is pulled from a query and then run a second query
based on the sites selected. So my first form is one that allows me to choose
which state I am looking for. I would like the results of that to be all of
the locations we have in that state from one master table. I would then like
to choose which locations I want to pull data for from a different table.

So if I choose NY from the first table, all NY locations appear
I then want to select multiple locations from that list and run a query
based on my selection. I am thinking check boxes but I am looking for all
solutions.

Any help is greatly appreciated

Thanks,
Steve
--

 

[Danny J. Lesandrini]

Maybe I didn't understand the entire question, but below is a scirpt
for looping through a list box that exposes multi-selected SiteIDs
and builds them into a SQL statement. That SQL could then be the
source for a query or form recordset.

Dim strList As String
Dim strSQL As String
Dim varItem As Variant

' Loop through, concatonating selected values
With Me!lstSites
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & "," & .ItemData(varItem)
Next
End If
End With

' strip off the final comma.
' alternatively, you could just add a zero, since that
' would not return any records.
If Right(strList, 1) = "," Then
strList = Left(strList, Len(strList) - 1)
End If

' Update the SQL Statement
strSQL = "SELECT * FROM tblSite WHERE [SiteID] IN (" & strList & ")"

 

[Steve D]

Thanks Danny, This is the answer to the second part of my question. Now I am
looking for how to populate that listbox with the results of a query. Can you
help with that as well?
--
Thank You,
Steve

Maybe I didn't understand the entire question, but below is a scirpt
for looping through a list box that exposes multi-selected SiteIDs
and builds them into a SQL statement. That SQL could then be the
source for a query or form recordset.

Dim strList As String
Dim strSQL As String
Dim varItem As Variant

' Loop through, concatonating selected values
With Me!lstSites
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & "," & .ItemData(varItem)
Next
End If
End With

' strip off the final comma.
' alternatively, you could just add a zero, since that
' would not return any records.
If Right(strList, 1) = "," Then
strList = Left(strList, Len(strList) - 1)
End If

' Update the SQL Statement
strSQL = "SELECT * FROM tblSite WHERE [SiteID] IN (" & strList & ")"

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

I am trying to create a form that will allow me to choose multiple sites from
a list of sites which is pulled from a query and then run a second query
based on the sites selected. So my first form is one that allows me to choose
which state I am looking for. I would like the results of that to be all of
the locations we have in that state from one master table. I would then like
to choose which locations I want to pull data for from a different table.

So if I choose NY from the first table, all NY locations appear
I then want to select multiple locations from that list and run a query
based on my selection. I am thinking check boxes but I am looking for all
solutions.

Any help is greatly appreciated

Thanks,
Steve

 

[Danny J. Lesandrini]

Steve:

The list box has a property named RowSource. So if the listbox is named
lstResults, you would add this code to the bottom of my code below ...

Me!lstResults.RowSource = strSQL

This should effect a requery of the data as well, displaying the new results.
I'm not sure what will trigger this line. Do you want it to update every time
the list of sites is clicked? Is it on a Refresh button? That's up to you
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Thanks Danny, This is the answer to the second part of my question. Now I am
looking for how to populate that listbox with the results of a query. Can you
help with that as well?
--
Thank You,
Steve

Maybe I didn't understand the entire question, but below is a scirpt
for looping through a list box that exposes multi-selected SiteIDs
and builds them into a SQL statement. That SQL could then be the
source for a query or form recordset.

Dim strList As String
Dim strSQL As String
Dim varItem As Variant

' Loop through, concatonating selected values
With Me!lstSites
If .ItemsSelected.Count > 0 Then
For Each varItem In .ItemsSelected
strList = strList & "," & .ItemData(varItem)
Next
End If
End With

' strip off the final comma.
' alternatively, you could just add a zero, since that
' would not return any records.
If Right(strList, 1) = "," Then
strList = Left(strList, Len(strList) - 1)
End If

' Update the SQL Statement
strSQL = "SELECT * FROM tblSite WHERE [SiteID] IN (" & strList & ")"

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

I am trying to create a form that will allow me to choose multiple sites from
a list of sites which is pulled from a query and then run a second query
based on the sites selected. So my first form is one that allows me to choose
which state I am looking for. I would like the results of that to be all of
the locations we have in that state from one master table. I would then like
to choose which locations I want to pull data for from a different table.

So if I choose NY from the first table, all NY locations appear
I then want to select multiple locations from that list and run a query
based on my selection. I am thinking check boxes but I am looking for all
solutions.

Any help is greatly appreciated

Thanks,
Steve