Multipick (Access 2000)

Topic

New Reply

Looks as though your example didn’t get attached. Be sure to attache the file as the last step. And you will probably need to zip the file before attaching it.

Reply | Quote

Replies

I would like to extent my question:
i have copied a wonderful example from Access 200 developers handbook and i need help to modify it to my needs.I am attaching the example.In this example i get the list of the products according to
the grade.I want however to show also the size and the pack, as obtained in the query qryProducts. I have tried the following but i got red letters:
mmp.SetData “qryProducts”, “Grade”,”size”,”pack”

I want to open a report based on the selection in the list box of a form.Can i do it ? According to my attachement, the form is called frmMultick and the listbox is called LstSelected.
In the OnClick event i have written the folloiwng :
Dim aSelected() As Variant
Dim varItem As Variant

‘ Get an array filled with the selected items.
aSelected = mmp.SelectedItems
For Each varItem In aSelected
strShowIt = strShowIt & varItem & vbCrLf
Next varItem
DoCmd.OpenReport “Rpt1”

Reply | Quote

See if the attached example does what you want. Look specifically at the code in the list box’s after update event.

Reply | Quote

Thank you fvery much for your attachement.I think your suggestion will perfectly suit me.Would you have a look at what i have done? I have some mistakes but if i can correct them that will met my targets.
Thank you in advance

Reply | Quote

Would you describe what the mistakes are.

Reply | Quote

My attachement contains the mistakes. The mistake is items are not found in the collection.Obiously i have not copied correctly the code suggested by NYIntenisty

Reply | Quote

I have made some corrections, don’t override your old database with this one, save the old one so you can compare the changes I made.

I hope it does what you want.

Reply | Quote

I wouldn’t use the After Update event of the list box, and I wouldn’t create a query. It can be done in a much more simple way in the On Click event of the command button:

Private Sub Command2_Click()
Dim varItem As Variant
Dim strWhere As String
For Each varItem In Me.lstProducts.ItemsSelected
strWhere = strWhere & “, ‘” & Me.lstProducts.ItemData(varItem) & “‘”
Next
If Not strWhere = “” Then
strWhere = Mid(strWhere, 3)
strWhere = “grade in (” & strWhere & “)”
End If
DoCmd.OpenReport “RPT1”, acViewPreview, , strWhere
End Sub

See attached version.

Reply | Quote

i beg to be apologized, i have sent my reformulated question before having seen your both anwers.So please do apologize me. I study them now.
Thank you very much indeed

Reply | Quote

Works great ! imply superb !

Reply | Quote

Hans,

Thanks for that great bit!

Reply | Quote

Dear Hans,

The code works great! . i came accross the following details..If i have products with one and the same name but with different sizes and packs, then even i choose one product, i get all the products in the report.For example, if the first three products are called pears,but with different sizes,then on choosing only one products all the three products appear on the report.How can i avoid that ?

Reply | Quote

You can set the Bound Column property to 1 instead of 2, and change the code behind the command button as follows:

Private Sub Command2_Click()
Dim varItem As Variant
Dim strWhere As String
For Each varItem In Me.lstProducts.ItemsSelected
‘ Note the absence of quotes, since ProductID is a number field
strWhere = strWhere & “, ” & Me.lstProducts.ItemData(varItem)
Next
If Not strWhere = “” Then
strWhere = Mid(strWhere, 3)
strWhere = “ProductID In (” & strWhere & “)”
End If
DoCmd.OpenReport “RPT1”, acViewPreview, , strWhere
End Sub

See attached version

Reply | Quote

I am having the same error ‘items are not found in the collection’. Would someone mind please taking a look at the attached to see what I am doing wrong? At this point all I am trying to do is pass the variants to the query.

Reply | Quote

You shouldn’t use the After Update event of the list box, because it fires each time the user selects or deselects an item. Instead, you should put a command button on the form and put the code in the On Click event of the button.

Your query is based on tblCurrent only, but you try to set a condition on the Job field from tblJoblist. I think you want a condition on the Activity field instead.

If an error occurs, the query qryRTM1 may already have been deleted. Next time, the instruction db.QueryDefs.Delete “qryRTM1” will cause the error message you mention.
You don’t really need to delete the query, you only need to change its SQL statement. Or put a line On Error Resume Next before the delete statement.

You should build in a check for the situation where the user hasn’t selected any items.

Here is revised code:

Private Sub cmdProcess_Click()
Dim varItem As Variant
Dim strTemp As String
Dim strSQL As String
Dim db As DAO.Database
Dim qry As DAO.QueryDef

On Error GoTo ErrHandler
If Me.lstProducts.ItemsSelected.Count = 0 Then
MsgBox “Please select one or more products!”, vbInformation
Exit Sub
End If
strSQL = “SELECT Activity, Cost_Code, Date, Timesheet_Date, Hours FROM tblCurrent ”
For Each varItem In Me.lstProducts.ItemsSelected
strTemp = strTemp & “Activity = ‘” & Me.lstProducts.ItemData(varItem) & “‘ Or ”
Next varItem

strSQL = strSQL & “WHERE ” & Left(strTemp, Len(strTemp) – 4)
Set db = CurrentDb

On Error Resume Next
db.QueryDefs.Delete “qryRTM1”
On Error GoTo ErrHandler
Set qry = db.CreateQueryDef(“qryRTM1”, strSQL)
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
End Sub

Reply | Quote

Works great, thank you so very much for your help.

Reply | Quote

>>You shouldn’t use the After Update event of the list box, because it fires each time the user selects or deselects an item.

What’s the problem with that? I’ve used this method successfully for over a year…wondering what the eventual downfalls (if any) will be?

Reply | Quote

This is a multi-select list box. Normally, you want to do something after the user has finished selecting items, not each time the user selects or deselects an item.

Reply | Quote

But will any problems rise?

Reply | Quote

That depends on what you’re doing in the After Update event procedure, but there is no intrinsic reason it should cause problems.

Reply | Quote