By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

SQL select and listbox

P: n/a
Can I use a parameter from a listbox in a query. Something like

SELECT tblTable.ID
FROM tblTable WHERE Listbox1 LIKE items.selected

Sigurd
--
____________________________________________
KILLSPAM R e m o v e trippleX to reply
to email adress.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Try
Dim SQLStg as string
SQLStg = " SELECT tblTable.ID FROM tblTable "
SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1

Good luck

Phil
"Sigurd Bruteig" <s-********@online.no> wrote in message
news:ti******************@news4.e.nsc.no...
Can I use a parameter from a listbox in a query. Something like

SELECT tblTable.ID
FROM tblTable WHERE Listbox1 LIKE items.selected

Sigurd
--
____________________________________________
KILLSPAM R e m o v e trippleX to reply
to email adress.

Nov 12 '05 #2

P: n/a
Thanks!
I want this to work with a multiselct listbox, thats the difficult issue.

Sigurd

"Phil Stanton" <ph**@stantonfamily.co.uk> skrev i melding
news:3f*********************@mercury.nildram.net.. .
Try
Dim SQLStg as string
SQLStg = " SELECT tblTable.ID FROM tblTable "
SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1

Good luck

Phil
"Sigurd Bruteig" <s-********@online.no> wrote in message
news:ti******************@news4.e.nsc.no...
Can I use a parameter from a listbox in a query. Something like

SELECT tblTable.ID
FROM tblTable WHERE Listbox1 LIKE items.selected

Sigurd
--
____________________________________________
KILLSPAM R e m o v e trippleX to reply
to email adress.


Nov 12 '05 #3

P: n/a
OK, youve changed the rules.

Here is a snippet of code that finds people acording to their smoking habits

Option Compare Database
Option Explicit

Private Sub SmokingIDRelay_AfterUpdate()

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer, SelectedRows As Integer
Set ctlSource = SmokingIDRelay
Set ctlDest = Me!CtlOutput
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
If SelectedRows >= 1 Then
strItems = strItems & " or SmokingID = "
End If
strItems = strItems & ctlSource.Column(1, intCurrentRow)
SelectedRows = SelectedRows + 1
End If
Next intCurrentRow
strItems = strItems & ";"
' Reset destination control's RowSource property.
ctlDest.RowSource = "SELECT HostSurName, SmokingID FROM Hosts WHERE
SmokingID = "
ctlDest.RowSource = ctlDest.RowSource & strItems

End Sub

SmokingIDRelay is a multiselect list box and ctlDest.RowSource holds an SQL
with the hosts names that fit the selected smoking habits.

Hope that helps

Phil
"Sigurd Bruteig" <s-********@online.no> wrote in message
news:Ep********************@news2.e.nsc.no...
Thanks!
I want this to work with a multiselct listbox, thats the difficult issue.

Sigurd

"Phil Stanton" <ph**@stantonfamily.co.uk> skrev i melding
news:3f*********************@mercury.nildram.net.. .
Try
Dim SQLStg as string
SQLStg = " SELECT tblTable.ID FROM tblTable "
SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1

Good luck

Phil
"Sigurd Bruteig" <s-********@online.no> wrote in message
news:ti******************@news4.e.nsc.no...
Can I use a parameter from a listbox in a query. Something like

SELECT tblTable.ID
FROM tblTable WHERE Listbox1 LIKE items.selected

Sigurd
--
____________________________________________
KILLSPAM R e m o v e trippleX to reply
to email adress.



Nov 12 '05 #4

P: n/a
This looks like the right thing. I will try it tomorrow. Thank you again,
and have a nice weekend.

Sigurd

"Phil Stanton" <ph**@stantonfamily.co.uk> skrev i melding
news:3f*********************@mercury.nildram.net.. .
OK, youve changed the rules.

Here is a snippet of code that finds people acording to their smoking habits
Option Compare Database
Option Explicit

Private Sub SmokingIDRelay_AfterUpdate()

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer, SelectedRows As Integer
Set ctlSource = SmokingIDRelay
Set ctlDest = Me!CtlOutput
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
If SelectedRows >= 1 Then
strItems = strItems & " or SmokingID = "
End If
strItems = strItems & ctlSource.Column(1, intCurrentRow)
SelectedRows = SelectedRows + 1
End If
Next intCurrentRow
strItems = strItems & ";"
' Reset destination control's RowSource property.
ctlDest.RowSource = "SELECT HostSurName, SmokingID FROM Hosts WHERE
SmokingID = "
ctlDest.RowSource = ctlDest.RowSource & strItems

End Sub

SmokingIDRelay is a multiselect list box and ctlDest.RowSource holds an SQL with the hosts names that fit the selected smoking habits.

Hope that helps

Phil
"Sigurd Bruteig" <s-********@online.no> wrote in message
news:Ep********************@news2.e.nsc.no...
Thanks!
I want this to work with a multiselct listbox, thats the difficult issue.
Sigurd

"Phil Stanton" <ph**@stantonfamily.co.uk> skrev i melding
news:3f*********************@mercury.nildram.net.. .
Try
Dim SQLStg as string
SQLStg = " SELECT tblTable.ID FROM tblTable "
SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1

Good luck

Phil
"Sigurd Bruteig" <s-********@online.no> wrote in message
news:ti******************@news4.e.nsc.no...
> Can I use a parameter from a listbox in a query. Something like
>
> SELECT tblTable.ID
> FROM tblTable WHERE Listbox1 LIKE items.selected
>
> Sigurd
> --
> ____________________________________________
> KILLSPAM R e m o v e trippleX to reply
> to email adress.
>
>



Nov 12 '05 #5

P: n/a
"Sigurd Bruteig" <s-********@online.no> wrote in message news:<ti******************@news4.e.nsc.no>...
Can I use a parameter from a listbox in a query. Something like

SELECT tblTable.ID
FROM tblTable WHERE Listbox1 LIKE items.selected

Sigurd


Sigurd,

yes you can. You have to loop through the ItemsSelected collection of
the listbox and build your criteria on the fly... See this link:

http://www.mvps.org/access/forms/frm0007.htm
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.