Multiple Selections List Box in a Form

[Tamrak]

Hello,

I browsed through several topics in the thread. The closet one that I came out was: thread702-1109346.

My issue is very similar to the previous post. I created a form, "frmProject", with an unbound listbox. This listbox has a column count of 2, Project_ID and Project_Name. I had already changed the "multi select" to "extended" per the thread. The data comes from table, tblProjects.

Proj_ID's property is numeric and Proj_Name is String. The first column is the Proj_ID and second column is Proj_Name.

I also created a button to run the code, which I tried to replicate from the previous thread.

************************
Option Compare Database
Option Explicit

Sub TestProjects()

Dim sProject As Double
Dim i As Integer
Dim sSearch As Double ' Uncertain whether to use the string or double.

sSearch = Null ' uncertain regarding "" from the examples.
For i = 0 To Me.List2.ItemsSelected.Count - 1
'sSearch = sSearch & Chr(34) & Me.List2.ItemData(Me.list2.ItemSelected(i)) & Chr(34) & ","

(I think the sSearch is incorrect. Need help on this as well.)

Next i

sWhere = "((tblProjects.Proj_ID, tblProjects.Project_Name) in (" & sSearch & "))"
DoCmd.OpenReport , "rptProjectsReport", acViewPreview, , sSearch

End Sub

***************
Requests:

1. When I tried to execute the code, the program does not recognize "Me," (Invalid use of Me Keyword.) Please correct.

2. Please do not link to other Microsoft threads or outside the forum because the codes are too long and they are very confusing.

Thanks.

 

[PHV]

In the Click event procedure of the button:
Dim varItem
Dim sSearch As String, sWhere As String
For Each varItem In Me!List2.ItemsSelected
sSearch = sSearch & "," & Me!List2.ItemData(varItem)
Next
sWhere = "tblProjects.Proj_ID In (" & Mid(sSearch, 2) & ")"
DoCmd.OpenReport , "rptProjectsReport", acViewPreview, , sWhere

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

Hi PHV,

This might sound a little bit dumb. I got an error on the line:

For Each varItem In Me!List2.ItemsSelected

It highlighted "Me" as "Invalid use of Me Keyword". Do I need to replace it something?

My unbounded listbox name is lstProjects, in the form, frmProjects. Look like it does not recognize the Me. Do you have any suggestions? Thanks for your time.

 

[PHV]

In the Click event procedure of the button

Isn't the button in the same form as List2 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

Hi,

It is me again. I think it compiles. But the last line had been highlighted.

"DoCmd.OpenReport , "rptProjectsReport", acViewPreview, , sWhere "

The error indicated, "Argument Not Optional." Sorry, it took a lot of your time. Thanks for reading.

 

[PHV]

Sorry for the typo:
DoCmd.OpenReport "rptProjectsReport", acViewPreview, , sWhere

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

Hi PHV,

Thank you again for your prompt reply. Unfortunately, I think something is wrong with the codes / db. I am now creating the testDB that will include only a few fields and test them from there.

Believe it or not, I have an error 438, Object doesn't support this property or method. I checked the references on the VBA coding. I have five checked.

* Microsoft Activex Data Objects 2.1 library
* Microsoft DAO 3.6 Ojbect Liveray
* OLE Automation
* Microsoft Access 11.0 Object Library
* Visual Basic for Applications

I am uncertain whether I missed anything. I will try to troubleshoot and find out of what is wrong. The procedures and codes seem to be simple enough to do the task. Thanks again for your time.

 

[PHV]

What is your actual code and which line raises the error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tamrak]

Hello again PHV,

I extracted some information and created the development db at this moment.

I have the form created. The unbounded listbox named "list2". The button name is "Exec". The form name is "frmProjects". The table name is still the same, "tblProjects."

I created a simple query, to have it show the table information. If the user selected three project numbers from the form, it should display three projects in the datasheet.

The last line of the codes was highlighted and stated "Run Time Error '13' Type Mismatch," when I clicked Debug.

DoCmd.OpenQuery "QryTestMultipleSelections", , sWhere

(The field property of Proj_ID is Number and Project_Name is Text.)

I do wonder whether sWhere string should be Numeric to match the Proj_ID. I am uncertain.

I still cannot figure it out. I missed a lot of things. Thank you for your tenacity.

Thanks again.


********************************************
Option Compare Database
Option Explicit

Private Sub Exec_Click()

Dim varItem
Dim sSearch As String, sWhere As String
For Each varItem In Me!List2.ItemsSelected
sSearch = sSearch & "," & Me!List2.ItemData(varItem)
Next
sWhere = "tblProjects.Proj_ID In (" & Mid(sSearch, 2) & ")"
DoCmd.OpenQuery "QryTestMultipleSelections", , sWhere
End Sub
******************************************

 

[PHV]

I've never suggested you OpenQuery ?

 

[Tamrak]

Good afternoon,

I used your ideas and generated the results successfully. We have another similar issue. However, I think this one might be a little bit easier.

A friend of mine assisted in building these routines.


*************************************

Option Compare Database
Option Explicit

Private Sub Command6_Click()
DoCmd.OpenQuery "Query1"
End Sub

Private Sub List2_AfterUpdate()

Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As QueryDef

strSQL = "DELETE tblProjects.Proj_ID FROM tblProjects"

For Each varItem In Me.ActiveControl.ItemsSelected
strTemp = strTemp & "((tblProjects.Proj_ID) = " & Me.ActiveControl.ItemData(varItem) & ") Or "
Next

strSQL = strSQL & " WHERE (" & Left(strTemp, Len(strTemp) - 4) & ");"


' On Error Resume Next
Set db = CurrentDb
With db
.QueryDefs.Delete "Query1"
Set qry = .CreateQueryDef("Query1", strSQL)

End With


End Sub

**************************************************************

When the form popped up, it will allow the user to select the multiple projects (numbers) to be deleted. When the user clicks the button, Command6, the series of actions will be executed.

So far, everything is working great.

What I would like to do is to add a few more actions to it after the projects have been deleted from the tblProjects.

1) When the projects have been deleted from the table, I would like to run two additional queries. Let’s say, Query2 and Query3, after that.

2) I try to use the DoCmd.OpenQuery “Query2” and DoCmd.OpenQuery “Query3”, they did not seem to work because they popped up right away. (I added the codes toward the end.)

3) Query2 is to create a new table and Query3 is to update some information in a newly created table. Query4 is another query, not related to this issue. It is what Query2 obtaining the data from.

4) The SQL code for the Query2 is something like “SELECT DISTINCTROW Query4.Projects, Query4.Project_Name……. INTO tblNewProjects…”

5) The SQL code for the Query3 is something like “UPDATE tblNewProjects INNER JOIN tblProjects ON tblNewProjects.Proj_ID = tblProjects.PROJ_ID …..”


Some of the commands that should be added to the routines:

DoCmd.SetWarnings False
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
MsgBox “Done", vbInformation + vbOKOnly, "Everything Is Done"

Question is, where should I add these additional commands into the current sub procedures? I tried to insert toward the last line. They were executed right away, which were different from my intention.

Please advice. Thank you.