secondary list box selection

[Donna]

Help! I have been trying to get the following to work for hours and keep getting the error message that the OpenForm action was cancelled....
I am trying to get a new form to open based on the selection from a list box. This is the second such screen within the database.
I can get the first one to work, but I'm stuck....

Any help would be greatly appreciated (P.S. I am a complete novice at Access - this is my first database design.) Thanks!

Private Sub cmdView_Click()

Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!lstProjName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstProjName.ItemsSelected
' Grab the Project ID for each selected item
strWhere = strWhere & Chr$(34) & Me!lstProjName.Column(0, varItem) & Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the projects form filtered on the selected projects
gstrWhereProject = "[ProjectID] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmProjects", WhereCondition:=gstrWhereProject
' Hide the subform, but show the slection list and hide buttons
Forms!frmProjects!Phasesubform.Visible = False
Forms!frmProjects!cmdReturn.Visible = False
DoCmd.RunCommand acCmdSaveRecord
End Sub

 

[Emilia Maxim]

Help! I have been trying to get the following to work for hours and keep getting the error message that the OpenForm action was cancelled....
I am trying to get a new form to open based on the selection from a list box. This is the second such screen within the database.
I can get the first one to work, but I'm stuck....

Any help would be greatly appreciated (P.S. I am a complete novice at Access - this is my first database design.) Thanks!

Private Sub cmdView_Click()

Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!lstProjName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstProjName.ItemsSelected
' Grab the Project ID for each selected item
strWhere = strWhere & Chr$(34) & Me!lstProjName.Column(0, varItem) & Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the projects form filtered on the selected projects
gstrWhereProject = "[ProjectID] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmProjects", WhereCondition:=gstrWhereProject
' Hide the subform, but show the slection list and hide buttons
Forms!frmProjects!Phasesubform.Visible = False
Forms!frmProjects!cmdReturn.Visible = False
DoCmd.RunCommand acCmdSaveRecord
End Sub

Donna,

I cannot find anything wrong at first glance (but I might be also
wrong ). Are you sure the project is the first column in the list
box' underlying query? And is ProjectID really a string? If it's
numerical you don't need the quotes around.

Also, you could either set a break point on the line with the DoCmd
statement or insert a MsgBox just before displaying the
gstrWhereProject string. This is always very useful as it shows you
exactly the criteria string created.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Tim Ferguson]

Help! I have been trying to get the following to work for hours and
keep getting the error message that the OpenForm action was
cancelled....

Where exactly does the error occur: have you tried stepping through the
code and working out where it fails?

DoCmd.RunCommand acCmdSaveRecord

I don't understand the point of this line: you have only just opened the
form and there has not been time to make any changes to it. It is even
possible, I guess, that the record has not even been opened in the new form
by the time you get to this line. If this is the line that is failing, I
cannot see what you would lose by simply removing it.

Hope that makes sense.


Tim F

 

[456]

Donna,

this works for me in a test. This assumes that the first column is the bound
column in your list box and that it is a numeric value. When I copied your
code, the first problem was that you hadn't dimmed gstrWhereProject.
Secondly, in your stWhere, I changed it from
Me!lstProjName.Column(0,varItem) to .ItemData(varItem). Thirdly, I removed
the Chr$(34)s, though you will need to add them back if your ProjectID is a
text value.

HTH,
Robert Dale

*** revised code ***

Private Sub cmdView_Click()
Dim strWhere As String
Dim varItem As Variant
Dim gstrWhereProject As String
' If no items selected, then nothing to do
If Me!lstProjName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstProjName.ItemsSelected
' Grab the Project ID for each selected item
strWhere = strWhere & Me!lstProjName.ItemData(varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the projects form filtered on the selected projects
gstrWhereProject = "[ProjectID] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmProjects", WhereCondition:=gstrWhereProject
' Hide the subform, but show the slection list and hide buttons
' Forms!frmProjects!Phasesubform.Visible = False
'Forms!frmProjects!cmdReturn.Visible = False
' DoCmd.RunCommand acCmdSaveRecord
End Sub