How to view records selected from a listbox when opening a form?

G

Guest

Hi guys,

I hope you can help me out here.

I've got a simple multi-select listbox on a form.
I'd like to pass the selected records (as link criteria) so that another
form opens with only the selected records displayed. I'm stumped I'm afraid.

Can you explain how to achieve this?

I've tried various methods including this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" & Me![lstVisits].ItemData(i) & "'"
Next i

stDocName = "frm_InspectionReports"

stLinkCriteria = "[ReportID]=" & Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be really appreciated!
 
K

Ken Snell \(MVP\)

You're doubling up on listing the "[ReportID]=" in the call to OpenForm.
Just use this:

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
G

Guest

Thanks Ken,
But I'm now getting the message 'The OpenForm Action was cancelled'. I
presume I've got to have a dig around the form that's being opened to see
what could be causing that but have you any ideas what might be the culprit?

Regards,

Lee

Ken Snell (MVP) said:
You're doubling up on listing the "[ReportID]=" in the call to OpenForm.
Just use this:

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>


Baby Face Lee said:
Hi guys,

I hope you can help me out here.

I've got a simple multi-select listbox on a form.
I'd like to pass the selected records (as link criteria) so that another
form opens with only the selected records displayed. I'm stumped I'm
afraid.

Can you explain how to achieve this?

I've tried various methods including this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" & Me![lstVisits].ItemData(i) &
"'"
Next i

stDocName = "frm_InspectionReports"

stLinkCriteria = "[ReportID]=" & Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be really appreciated!
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

That suggests that something in your criteria string is not correct for the
form's RecordSource query -- and likely it's because you're not stripping
the trailing " OR " text from the Criteria string. Try this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" & Me![lstVisits].ItemData(i) &
"'"
Next i
If Criteria <> "" Then Criteria = Left(Criteria, Len(Criteria) - Len(" OR
"))

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>


Baby Face Lee said:
Thanks Ken,
But I'm now getting the message 'The OpenForm Action was cancelled'. I
presume I've got to have a dig around the form that's being opened to see
what could be causing that but have you any ideas what might be the
culprit?

Regards,

Lee

Ken Snell (MVP) said:
You're doubling up on listing the "[ReportID]=" in the call to OpenForm.
Just use this:

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>


Baby Face Lee said:
Hi guys,

I hope you can help me out here.

I've got a simple multi-select listbox on a form.
I'd like to pass the selected records (as link criteria) so that
another
form opens with only the selected records displayed. I'm stumped I'm
afraid.

Can you explain how to achieve this?

I've tried various methods including this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" & Me![lstVisits].ItemData(i)
&
"'"
Next i

stDocName = "frm_InspectionReports"

stLinkCriteria = "[ReportID]=" & Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be really appreciated!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Ken.

Well after much twiddling about I've solved the problem. The criteria
string did not need the apostrophes wrapped around each Report ID.

So, in case someone else should view this, the correct code is:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]=" & Me![lstVisits].ItemData(i)
Next i

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Kind regards,

Lee

Ken Snell (MVP) said:
That suggests that something in your criteria string is not correct for the
form's RecordSource query -- and likely it's because you're not stripping
the trailing " OR " text from the Criteria string. Try this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" & Me![lstVisits].ItemData(i) &
"'"
Next i
If Criteria <> "" Then Criteria = Left(Criteria, Len(Criteria) - Len(" OR
"))

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>


Baby Face Lee said:
Thanks Ken,
But I'm now getting the message 'The OpenForm Action was cancelled'. I
presume I've got to have a dig around the form that's being opened to see
what could be causing that but have you any ideas what might be the
culprit?

Regards,

Lee

Ken Snell (MVP) said:
You're doubling up on listing the "[ReportID]=" in the call to OpenForm.
Just use this:

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>


Hi guys,

I hope you can help me out here.

I've got a simple multi-select listbox on a form.
I'd like to pass the selected records (as link criteria) so that
another
form opens with only the selected records displayed. I'm stumped I'm
afraid.

Can you explain how to achieve this?

I've tried various methods including this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" & Me![lstVisits].ItemData(i)
&
"'"
Next i

stDocName = "frm_InspectionReports"

stLinkCriteria = "[ReportID]=" & Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be really appreciated!
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

That would have been my next suggestion < g >. After reviewing your code in
the morning (it was night previously), I see that your code doesn't leave a
trailing " OR" text string in the Criteria value -- good job.

--

Ken Snell
<MS ACCESS MVP>

Baby Face Lee said:
Thanks Ken.

Well after much twiddling about I've solved the problem. The criteria
string did not need the apostrophes wrapped around each Report ID.

So, in case someone else should view this, the correct code is:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]=" & Me![lstVisits].ItemData(i)
Next i

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Kind regards,

Lee

Ken Snell (MVP) said:
That suggests that something in your criteria string is not correct for
the
form's RecordSource query -- and likely it's because you're not stripping
the trailing " OR " text from the Criteria string. Try this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" & Me![lstVisits].ItemData(i) &
"'"
Next i
If Criteria <> "" Then Criteria = Left(Criteria, Len(Criteria) - Len("
OR
"))

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>


Baby Face Lee said:
Thanks Ken,
But I'm now getting the message 'The OpenForm Action was cancelled'. I
presume I've got to have a dig around the form that's being opened to
see
what could be causing that but have you any ideas what might be the
culprit?

Regards,

Lee

:

You're doubling up on listing the "[ReportID]=" in the call to
OpenForm.
Just use this:

stDocName = "frm_InspectionReports"

stLinkCriteria = Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>


message
Hi guys,

I hope you can help me out here.

I've got a simple multi-select listbox on a form.
I'd like to pass the selected records (as link criteria) so that
another
form opens with only the selected records displayed. I'm stumped
I'm
afraid.

Can you explain how to achieve this?

I've tried various methods including this:

Dim stDocName As String
Dim stLinkCriteria As String
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstVisits].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[ReportID]='" &
Me![lstVisits].ItemData(i)
&
"'"
Next i

stDocName = "frm_InspectionReports"

stLinkCriteria = "[ReportID]=" & Criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help would be really appreciated!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OLE Server error 3
Criteria when opening form 1
Report not opening correctly 4
If Statements 2
stLinkCriteria 2
Again: Counting of Records Before openning a Form 1
Counting of Records in a Before opening a Data Form 3
Code error when opening a form 2

Top