473,320 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL select and listbox

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
5 8751
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
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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: headware | last post by:
I have a <select> control that contains many entries. It allows the user to multi-select a group of them, click a button, and store the selected data in a database. Normally they do this starting...
14
by: Jos? | last post by:
This one droves me completely mad. I did not succeed to exploit the track given to me by Bob. I have : three tables : Clubs, Persons and ClubsPersons that join the two first in a many to many...
4
by: Alienz | last post by:
I have a subform where I have a subform with 20 options to select from. When I set the multiselect property to simple and select multiple options, nothing is stored. I have another table with...
5
by: Lisa | last post by:
Hello, I am new to using recordsets, and i am completly stuck with this one. I am trying to use a multi select list box to write records to a table. Something in my code is causing the same...
1
by: Ahmet Karaca | last post by:
Hi. myds.Reset(); mycommand.SelectCommand.CommandText= "Select att1 from Ing as Ingredient, Pro as Product "+ "where Pro.ad='apple' and Pro.id=Ing.id"; mycommand.Fill(myds, "Product"); // Here...
5
by: Matthew Wells | last post by:
I have a listbox set to simple multi select. For this example, users only select one item at a time. I have command buttons on the form for First, Previous, Next, Last, New (record). The form...
1
by: Sunray | last post by:
I have a form called the sales form and i have 2 sets of listboxes So what happens is. i add items form the bottom set of list boxes which are bound to a data base to the top set of list boxes which...
2
by: billa856 | last post by:
Hi, My Project is in MS Access. In that I have one form in which I have some textboxes,comboboxes and listboxes. Now when I select value from 1st combobox(CustomerID) then it wil generate list for...
4
by: Vincent | last post by:
I have a list box that is bound to a table with many records. I have a select all button that when clicked, obviously, selects all of the items in the list box. However, it takes a fairly long...
17
by: trose178 | last post by:
Good day all, I am working on a multi-select list box for a standard question checklist database and I am running into a syntax error in the code that I cannot seem to correct. I will also note...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.