How do I base a forms' recordset on the values of a listbox? 1

[Varga]

How do I base a continuous forms' recordset on the miltiselect column(0) values of a (.rowsource query) listbox?
I Have a list box that fills in with contract numbers in the first column, and details about each contract in the following columns. I want the user to be able to "Multi Select" a few contract numbers within the listbox, and as they select each contract number, that value is added to the forms' (SELECT...WHERE...) query so that the forms continuous detail section contains all the matching records to the values selected in the "Contract #" listbox.
I am fairly new to all this so please bear with me. This is the code so far as I have it.

Private Sub Contract_#_Click()
With Me
.RecordSource = "SELECT * FROM [CONTRACT LIST]" & _
"WHERE [INFO Contract Number] =" & Me.Contract_#
.Refresh
End With
End Sub

I don't know if I should use ".Refresh, .Requery, .Recalc" to refresh the forms information? When I try to run this code the details section just goes blank.

I'm using a single select listbox rightnow just to try and get the form working, and I don't know how the "contract #.Items Selected" property will work when using the multiple values of a field at the end of a "SELECT" query.

Even if I leave out the WHERE part, I get all the records displayed on the form; but, each field of each record says "Name?".

How can this be done?
Someone please help me out,

Thanks,
Blair

 

[KenReay]

Hi

You need to build the selected values in a list, by iterating thru the selected items of the list box, do you know how to do that?

then you need to make your WHERE clause look at that list so

WHERE [INFO Contract Number] In" & strMyList

finally you need to use .Requery Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Varga]

I'm sorry but this is the best I can do. I still have some trouble integrating SQL queries and Form control values, especially when there is more than one value selected in the listbox. I think an "IN List" statement needs a ("___","___&quot format because if I try to run this code it gives a runtime error message of "In operator without () in query expression '[INFO Contract Number] IN'." I don't really understand the "FOR EACH" structure yet. I don't understand how just using "Dim ContNum" tells access that "For Each ContNum In Me.__.ItemsSelected" means "For Each RECORD In Me.__.ItemsSelected". I might need a little explaination when it comes to the "For Each" of the ".ItemsSelected" and putting thoses values into a list variable that can be used in a "WHERE [___] IN..." SQL '.RecordSet' statement.


Private Sub FRMContract_List_Click()
Dim ContNum, strMyList As String
For Each ContNum In Me.FRMContract_List.ItemsSelected
Let strMyList = strMyList &
Me.FRMContract_List.Column(0, ContNum) & ";"
Next
Me.RecordSource = "SELECT [%] FROM [AUDIT LIST]" & _
"WHERE [INFO Contract Number] IN" & Me.FRMContract_List
Me.Requery
End Sub

Even if I try using the folowing code:
Me.RecordSource = "SELECT * FROM [AUDIT LIST]"
Me.Requery
I don't get any records displayed in the form. I've been having trouble with this, I was trying to use a recordset before, and using 'currentdb.OpenRecordset("AUDIT LIST&quot' would return a large recordset count; however, if I tried using 'Currentdb.OpenRecordset("SELECT * FROM [AUDIT LIST]&quot' there were no records.

Am I using * instead of % ?
Should I use *, '*', %, or '%' for the SELECT All part of the query?
How do I properly assign a '.RecordSet' SQL query and '.Requery' the form so that the records are displayed on the form?

If you need any clarification, please let me know,
Thank you for your help,
Blair

 

[KenReay]

Hi

You indicate you need "--","--",...etc

THis is correct if the value you are looking for is a string

so

Dim ContNum, strMyList As String
strMyList = ""
For Each ContNum In Me.FRMContract_List.ItemsSelected
Let strMyList = strMyList & "'" &
Me.FRMContract_List.Column(0, ContNum) & "',"
Next
strMyList = Left(strMyList,len(strMyList)-1) ' take off last ,

In you comments about * and % in SELECT, * is shorthand for 'all columns' and is what you need, % is a wild card character used in LIKE constructs (when using ADO), if you are using DAO and JEt, then the equivalent wild card is ?
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Varga]

Hi Ken,
Thank you very much for your help, at least I'm on the other side of this hurdle.
I finally got it to work.
I had to recreate the form and base it on the "AUDIT LIST" table, then remove the table from the forms 'RecordSource' property, to be able to define the 'RecordSource' with code.
Here it is:

Private Sub FRM_Contract_List_AfterUpdate()
Dim ContNum, strMyList As String
strMyList = ""
For Each ContNum In Me.FRM_Contract_List.ItemsSelected
Let strMyList = strMyList & "'" & _
Me.FRM_Contract_List.Column(0, ContNum) & "',"
Next
'(take off last ,)
strMyList = Left(strMyList, Len(strMyList) - 1)
With Me
.RecordSource = "SELECT * FROM [AUDIT LIST]" & _
"WHERE [INFO Contract Number] IN (" & strMyList & &quot"
.Requery
End With
Me.Caption = " " & _
Me.FRM_Contract_List.ItemsSelected.Count & _
" contract(s) selected. "
End Sub


I've filled the "FRM Contract List" listbox with contract numbers using (Me.FRM_Contract_List.Rowsource = "SELECT...FROM...&quot in the forms load procedure. I made "FRM Contract List" a MultiSelect listbox and added the above code to it's 'After_Update' procedure. The form displays all matching records for the selected contracts in the "FRM Contract List" listbox, and the caption displays the number of contracts selected.
I can now go forward from here.
I thank you, Ken, once again for all your help.
You've made this possible.
I have to also praise this site, I am glad I have become a Tek-Tips member.
All the best,
Thank you very much,
Blair Varga