Open selected records via listbox

[Julia]

Hi,

I don't have a lot of experience with Access. I would like to open records
based on a selection in a list box and then make a report. This can also
happen in one step.

I have records for projects. Each project has a product, and one product can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the Microsoft
web page in which you use the Filter property of a form, but I cannot seem to
get either one to work. I have gotten further with the Microsoft option, but
I get an error message "You cancelled the previous operation". This method
also is not very clear about where to put the Command to open the form based
on the filter criteria. I can see from the code that my selection is getting
through but for some reason I am having difficulties opening the form based
on the filter criteria. This could be because I am using the wrong syntax to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Arvin Meyer [MVP]]

I have done something similar. I use a listbox's click event to fill a
textbox (txtSelected) with items to open a continuous subform containing the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill the form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID, Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" & Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL

 

[Julia]

Hi Arvin,

Can you see what the problem is with my code? I get a message box asking me
to select a parameter value, then if I press OK I get the form opening with
no records in there. If I remove the OpenForm line then the form that I have
the OnClick criteria form has the number of records for which I have selected
products. This should be a simple thing but I can't understand where it is
going wrong!

Julia

I have done something similar. I use a listbox's click event to fill a
textbox (txtSelected) with items to open a continuous subform containing the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill the form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID, Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" & Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to open records
based on a selection in a list box and then make a report. This can also
happen in one step.

I have records for projects. Each project has a product, and one product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the Microsoft
web page in which you use the Filter property of a form, but I cannot seem
to
get either one to work. I have gotten further with the Microsoft option,
but
I get an error message "You cancelled the previous operation". This method
also is not very clear about where to put the Command to open the form
based
on the filter criteria. I can see from the code that my selection is
getting
through but for some reason I am having difficulties opening the form
based
on the filter criteria. This could be because I am using the wrong syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Arvin Meyer [MVP]]

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause to use
multiple criteria. Try the code I supplied in the click event of the list
box. It will fill a hidden textbox (in this case txtSelected) and separate
the individual items, removing the last comma at the end. It will also
remove items if they are clicked off. The code I supplied would work if the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box asking
me
to select a parameter value, then if I press OK I get the form opening
with
no records in there. If I remove the OpenForm line then the form that I
have
the OnClick criteria form has the number of records for which I have
selected
products. This should be a simple thing but I can't understand where it is
going wrong!

Julia

I have done something similar. I use a listbox's click event to fill a
textbox (txtSelected) with items to open a continuous subform containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID, Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to open
records
based on a selection in a list box and then make a report. This can
also
happen in one step.

I have records for projects. Each project has a product, and one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the
Microsoft
web page in which you use the Filter property of a form, but I cannot
seem
to
get either one to work. I have gotten further with the Microsoft
option,
but
I get an error message "You cancelled the previous operation". This
method
also is not very clear about where to put the Command to open the form
based
on the filter criteria. I can see from the code that my selection is
getting
through but for some reason I am having difficulties opening the form
based
on the filter criteria. This could be because I am using the wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Julia]

Hi Arvin,

I am having some trouble entering the strSQL part of your code. There are
four brackets going this way ( but only three going this way ) and it is
causing problems. I am not experienced enough to see where the extra bracket
is supposed to go.

Julia

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause to use
multiple criteria. Try the code I supplied in the click event of the list
box. It will fill a hidden textbox (in this case txtSelected) and separate
the individual items, removing the last comma at the end. It will also
remove items if they are clicked off. The code I supplied would work if the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box asking
me
to select a parameter value, then if I press OK I get the form opening
with
no records in there. If I remove the OpenForm line then the form that I
have
the OnClick criteria form has the number of records for which I have
selected
products. This should be a simple thing but I can't understand where it is
going wrong!

Julia

I have done something similar. I use a listbox's click event to fill a
textbox (txtSelected) with items to open a continuous subform containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID, Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to open
records
based on a selection in a list box and then make a report. This can
also
happen in one step.

I have records for projects. Each project has a product, and one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the
Microsoft
web page in which you use the Filter property of a form, but I cannot
seem
to
get either one to work. I have gotten further with the Microsoft
option,
but
I get an error message "You cancelled the previous operation". This
method
also is not very clear about where to put the Command to open the form
based
on the filter criteria. I can see from the code that my selection is
getting
through but for some reason I am having difficulties opening the form
based
on the filter criteria. This could be because I am using the wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Arvin Meyer [MVP]]

Try this, but the code I'm using is for illustration only. It needs to be
changed for your usage:

strSQL = "SELECT CostCode, ItemID, ModelID, Cost "
strSQL = strSQL & "FROM tblItem "
strSQL = strSQL & "WHERE ModelID In (" & Me.txtSelected & ")"
strSQL = strSQL & "Order By CostCode;"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am having some trouble entering the strSQL part of your code. There are
four brackets going this way ( but only three going this way ) and it is
causing problems. I am not experienced enough to see where the extra
bracket
is supposed to go.

Julia

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause to use
multiple criteria. Try the code I supplied in the click event of the list
box. It will fill a hidden textbox (in this case txtSelected) and
separate
the individual items, removing the last comma at the end. It will also
remove items if they are clicked off. The code I supplied would work if
the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box
asking
me
to select a parameter value, then if I press OK I get the form opening
with
no records in there. If I remove the OpenForm line then the form that I
have
the OnClick criteria form has the number of records for which I have
selected
products. This should be a simple thing but I can't understand where it
is
going wrong!

Julia

:

I have done something similar. I use a listbox's click event to fill a
textbox (txtSelected) with items to open a continuous subform
containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID, Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to open
records
based on a selection in a list box and then make a report. This can
also
happen in one step.

I have records for projects. Each project has a product, and one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the
Microsoft
web page in which you use the Filter property of a form, but I
cannot
seem
to
get either one to work. I have gotten further with the Microsoft
option,
but
I get an error message "You cancelled the previous operation". This
method
also is not very clear about where to put the Command to open the
form
based
on the filter criteria. I can see from the code that my selection is
getting
through but for some reason I am having difficulties opening the
form
based
on the filter criteria. This could be because I am using the wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Julia]

Hi Arvin,

I am sorry but it still doesn't seem to work. With the code for the
listbox's click event, I get an error message when I click on any of the
items "Method or data member not found", and opening the code window shows
the second Me.txtSelected (ie the one just above "End If") highlighted. Then
if I try to open the form, it returns all records and I get yet another error
message, although this one is probably my problem in not being able to
translate your example to my database. I have tried entering the field name
of the primary key for both tables involved, and replaced the fields after
SELECT with *.

I am expecting an access textbook to come in in the next few days so I am
thinking of admitting defeat until then. It is too complicated for my level
of programming at the moment.

Try this, but the code I'm using is for illustration only. It needs to be
changed for your usage:

strSQL = "SELECT CostCode, ItemID, ModelID, Cost "
strSQL = strSQL & "FROM tblItem "
strSQL = strSQL & "WHERE ModelID In (" & Me.txtSelected & ")"
strSQL = strSQL & "Order By CostCode;"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am having some trouble entering the strSQL part of your code. There are
four brackets going this way ( but only three going this way ) and it is
causing problems. I am not experienced enough to see where the extra
bracket
is supposed to go.

Julia

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause to use
multiple criteria. Try the code I supplied in the click event of the list
box. It will fill a hidden textbox (in this case txtSelected) and
separate
the individual items, removing the last comma at the end. It will also
remove items if they are clicked off. The code I supplied would work if
the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box
asking
me
to select a parameter value, then if I press OK I get the form opening
with
no records in there. If I remove the OpenForm line then the form that I
have
the OnClick criteria form has the number of records for which I have
selected
products. This should be a simple thing but I can't understand where it
is
going wrong!

Julia

:

I have done something similar. I use a listbox's click event to fill a
textbox (txtSelected) with items to open a continuous subform
containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID, Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to open
records
based on a selection in a list box and then make a report. This can
also
happen in one step.

I have records for projects. Each project has a product, and one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the
Microsoft
web page in which you use the Filter property of a form, but I
cannot
seem
to
get either one to work. I have gotten further with the Microsoft
option,
but
I get an error message "You cancelled the previous operation". This
method
also is not very clear about where to put the Command to open the
form
based
on the filter criteria. I can see from the code that my selection is
getting
through but for some reason I am having difficulties opening the
form
based
on the filter criteria. This could be because I am using the wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Arvin Meyer [MVP]]

Do you have a textbox named txtSelected on your form? Try:

strSQL = "SELECT * "
strSQL = strSQL & "FROM YourTableName "
strSQL = strSQL & "WHERE YourPrimaryKeyName In (" & Me.txtSelected & "); "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am sorry but it still doesn't seem to work. With the code for the
listbox's click event, I get an error message when I click on any of the
items "Method or data member not found", and opening the code window shows
the second Me.txtSelected (ie the one just above "End If") highlighted.
Then
if I try to open the form, it returns all records and I get yet another
error
message, although this one is probably my problem in not being able to
translate your example to my database. I have tried entering the field
name
of the primary key for both tables involved, and replaced the fields after
SELECT with *.

I am expecting an access textbook to come in in the next few days so I am
thinking of admitting defeat until then. It is too complicated for my
level
of programming at the moment.

Try this, but the code I'm using is for illustration only. It needs to be
changed for your usage:

strSQL = "SELECT CostCode, ItemID, ModelID, Cost "
strSQL = strSQL & "FROM tblItem "
strSQL = strSQL & "WHERE ModelID In (" & Me.txtSelected & ")"
strSQL = strSQL & "Order By CostCode;"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am having some trouble entering the strSQL part of your code. There
are
four brackets going this way ( but only three going this way ) and it
is
causing problems. I am not experienced enough to see where the extra
bracket
is supposed to go.

Julia

:

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause to
use
multiple criteria. Try the code I supplied in the click event of the
list
box. It will fill a hidden textbox (in this case txtSelected) and
separate
the individual items, removing the last comma at the end. It will also
remove items if they are clicked off. The code I supplied would work
if
the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box
asking
me
to select a parameter value, then if I press OK I get the form
opening
with
no records in there. If I remove the OpenForm line then the form
that I
have
the OnClick criteria form has the number of records for which I have
selected
products. This should be a simple thing but I can't understand where
it
is
going wrong!

Julia

:

I have done something similar. I use a listbox's click event to
fill a
textbox (txtSelected) with items to open a continuous subform
containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill
the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID,
Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to
open
records
based on a selection in a list box and then make a report. This
can
also
happen in one step.

I have records for projects. Each project has a product, and one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the
Microsoft
web page in which you use the Filter property of a form, but I
cannot
seem
to
get either one to work. I have gotten further with the Microsoft
option,
but
I get an error message "You cancelled the previous operation".
This
method
also is not very clear about where to put the Command to open the
form
based
on the filter criteria. I can see from the code that my selection
is
getting
through but for some reason I am having difficulties opening the
form
based
on the filter criteria. This could be because I am using the
wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Julia]

Hi Arvin,

This has helped a lot! I can see that the Primary key of the records in the
list box are being placed in the textbox. Now it seems that the problem is
trying to open the form using the criteria in the textbox. I am not sure what
parts of the SQL code are needed. The Primary key for the records is not
directly in the table that I have based the form on - it is related via a
lookup - and I have tried to insert it but it locks the form. I think that
this is making it difficult for me to translate your code into my database.

Julia

Do you have a textbox named txtSelected on your form? Try:

strSQL = "SELECT * "
strSQL = strSQL & "FROM YourTableName "
strSQL = strSQL & "WHERE YourPrimaryKeyName In (" & Me.txtSelected & "); "
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am sorry but it still doesn't seem to work. With the code for the
listbox's click event, I get an error message when I click on any of the
items "Method or data member not found", and opening the code window shows
the second Me.txtSelected (ie the one just above "End If") highlighted.
Then
if I try to open the form, it returns all records and I get yet another
error
message, although this one is probably my problem in not being able to
translate your example to my database. I have tried entering the field
name
of the primary key for both tables involved, and replaced the fields after
SELECT with *.

I am expecting an access textbook to come in in the next few days so I am
thinking of admitting defeat until then. It is too complicated for my
level
of programming at the moment.

Try this, but the code I'm using is for illustration only. It needs to be
changed for your usage:

strSQL = "SELECT CostCode, ItemID, ModelID, Cost "
strSQL = strSQL & "FROM tblItem "
strSQL = strSQL & "WHERE ModelID In (" & Me.txtSelected & ")"
strSQL = strSQL & "Order By CostCode;"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am having some trouble entering the strSQL part of your code. There
are
four brackets going this way ( but only three going this way ) and it
is
causing problems. I am not experienced enough to see where the extra
bracket
is supposed to go.

Julia

:

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause to
use
multiple criteria. Try the code I supplied in the click event of the
list
box. It will fill a hidden textbox (in this case txtSelected) and
separate
the individual items, removing the last comma at the end. It will also
remove items if they are clicked off. The code I supplied would work
if
the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box
asking
me
to select a parameter value, then if I press OK I get the form
opening
with
no records in there. If I remove the OpenForm line then the form
that I
have
the OnClick criteria form has the number of records for which I have
selected
products. This should be a simple thing but I can't understand where
it
is
going wrong!

Julia

:

I have done something similar. I use a listbox's click event to
fill a
textbox (txtSelected) with items to open a continuous subform
containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I fill
the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID,
Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to
open
records
based on a selection in a list box and then make a report. This
can
also
happen in one step.

I have records for projects. Each project has a product, and one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from the
Microsoft
web page in which you use the Filter property of a form, but I
cannot
seem
to
get either one to work. I have gotten further with the Microsoft
option,
but
I get an error message "You cancelled the previous operation".
This
method
also is not very clear about where to put the Command to open the
form
based
on the filter criteria. I can see from the code that my selection
is
getting
through but for some reason I am having difficulties opening the
form
based
on the filter criteria. This could be because I am using the
wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Arvin Meyer [MVP]]

I'm about at the limit of what I can do in a newsgroup posting. Go to the
datastrat website listed below in my sig and get my email address. Send me
an email and I'll fix it for you.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

This has helped a lot! I can see that the Primary key of the records in
the
list box are being placed in the textbox. Now it seems that the problem is
trying to open the form using the criteria in the textbox. I am not sure
what
parts of the SQL code are needed. The Primary key for the records is not
directly in the table that I have based the form on - it is related via a
lookup - and I have tried to insert it but it locks the form. I think that
this is making it difficult for me to translate your code into my
database.

Julia

Do you have a textbox named txtSelected on your form? Try:

strSQL = "SELECT * "
strSQL = strSQL & "FROM YourTableName "
strSQL = strSQL & "WHERE YourPrimaryKeyName In (" & Me.txtSelected & ");
"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am sorry but it still doesn't seem to work. With the code for the
listbox's click event, I get an error message when I click on any of
the
items "Method or data member not found", and opening the code window
shows
the second Me.txtSelected (ie the one just above "End If") highlighted.
Then
if I try to open the form, it returns all records and I get yet another
error
message, although this one is probably my problem in not being able to
translate your example to my database. I have tried entering the field
name
of the primary key for both tables involved, and replaced the fields
after
SELECT with *.

I am expecting an access textbook to come in in the next few days so I
am
thinking of admitting defeat until then. It is too complicated for my
level
of programming at the moment.

:

Try this, but the code I'm using is for illustration only. It needs to
be
changed for your usage:

strSQL = "SELECT CostCode, ItemID, ModelID, Cost "
strSQL = strSQL & "FROM tblItem "
strSQL = strSQL & "WHERE ModelID In (" & Me.txtSelected & ")"
strSQL = strSQL & "Order By CostCode;"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am having some trouble entering the strSQL part of your code.
There
are
four brackets going this way ( but only three going this way ) and
it
is
causing problems. I am not experienced enough to see where the extra
bracket
is supposed to go.

Julia

:

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause
to
use
multiple criteria. Try the code I supplied in the click event of
the
list
box. It will fill a hidden textbox (in this case txtSelected) and
separate
the individual items, removing the last comma at the end. It will
also
remove items if they are clicked off. The code I supplied would
work
if
the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box
asking
me
to select a parameter value, then if I press OK I get the form
opening
with
no records in there. If I remove the OpenForm line then the form
that I
have
the OnClick criteria form has the number of records for which I
have
selected
products. This should be a simple thing but I can't understand
where
it
is
going wrong!

Julia

:

I have done something similar. I use a listbox's click event to
fill a
textbox (txtSelected) with items to open a continuous subform
containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) &
","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I
fill
the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID,
Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to
open
records
based on a selection in a list box and then make a report.
This
can
also
happen in one step.

I have records for projects. Each project has a product, and
one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from
the
Microsoft
web page in which you use the Filter property of a form, but I
cannot
seem
to
get either one to work. I have gotten further with the
Microsoft
option,
but
I get an error message "You cancelled the previous operation".
This
method
also is not very clear about where to put the Command to open
the
form
based
on the filter criteria. I can see from the code that my
selection
is
getting
through but for some reason I am having difficulties opening
the
form
based
on the filter criteria. This could be because I am using the
wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub

 

[Julia]

Hi Arvin,

I actually managed to get it to work! I have been trying three different
methods, yours, the one on the Microsoft access help and Allen Browne's
method. Allen's method is now working for me - I think the trick was to add
the primary key of the table used to select the data to the query I based the
report on. I am sorry I could not get your method to work! Thanks for all
your help.

Julia

I'm about at the limit of what I can do in a newsgroup posting. Go to the
datastrat website listed below in my sig and get my email address. Send me
an email and I'll fix it for you.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

This has helped a lot! I can see that the Primary key of the records in
the
list box are being placed in the textbox. Now it seems that the problem is
trying to open the form using the criteria in the textbox. I am not sure
what
parts of the SQL code are needed. The Primary key for the records is not
directly in the table that I have based the form on - it is related via a
lookup - and I have tried to insert it but it locks the form. I think that
this is making it difficult for me to translate your code into my
database.

Julia

Do you have a textbox named txtSelected on your form? Try:

strSQL = "SELECT * "
strSQL = strSQL & "FROM YourTableName "
strSQL = strSQL & "WHERE YourPrimaryKeyName In (" & Me.txtSelected & ");
"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am sorry but it still doesn't seem to work. With the code for the
listbox's click event, I get an error message when I click on any of
the
items "Method or data member not found", and opening the code window
shows
the second Me.txtSelected (ie the one just above "End If") highlighted.
Then
if I try to open the form, it returns all records and I get yet another
error
message, although this one is probably my problem in not being able to
translate your example to my database. I have tried entering the field
name
of the primary key for both tables involved, and replaced the fields
after
SELECT with *.

I am expecting an access textbook to come in in the next few days so I
am
thinking of admitting defeat until then. It is too complicated for my
level
of programming at the moment.

:

Try this, but the code I'm using is for illustration only. It needs to
be
changed for your usage:

strSQL = "SELECT CostCode, ItemID, ModelID, Cost "
strSQL = strSQL & "FROM tblItem "
strSQL = strSQL & "WHERE ModelID In (" & Me.txtSelected & ")"
strSQL = strSQL & "Order By CostCode;"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

I am having some trouble entering the strSQL part of your code.
There
are
four brackets going this way ( but only three going this way ) and
it
is
causing problems. I am not experienced enough to see where the extra
bracket
is supposed to go.

Julia

:

You can't open a form with the criteria you've built:

Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

There is no delineation between items, and you'd need an IN clause
to
use
multiple criteria. Try the code I supplied in the click event of
the
list
box. It will fill a hidden textbox (in this case txtSelected) and
separate
the individual items, removing the last comma at the end. It will
also
remove items if they are clicked off. The code I supplied would
work
if
the
form were already open, but you could open it like:

DoCmd.OpenForm "MainForm", acNormal
Forms!MainForm.Recordsource = strSQL
and it would requery it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Arvin,

Can you see what the problem is with my code? I get a message box
asking
me
to select a parameter value, then if I press OK I get the form
opening
with
no records in there. If I remove the OpenForm line then the form
that I
have
the OnClick criteria form has the number of records for which I
have
selected
products. This should be a simple thing but I can't understand
where
it
is
going wrong!

Julia

:

I have done something similar. I use a listbox's click event to
fill a
textbox (txtSelected) with items to open a continuous subform
containing
the
many-side items:

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) &
","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

The above fills (and subtracts) items in the textbox, Then I
fill
the
form
interactively:

strSQL = "SELECT CostCode, ItemID, CostCodeName, ContractorID,
Cost,
ModelElevation FROM tblItem WHERE (((tblItem.ModelID) In (" &
Me.txtSelected
& ")) Order By CostCode;"

and requery the subform:

Me.subItems.Form.RecordSource = strSQL
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I don't have a lot of experience with Access. I would like to
open
records
based on a selection in a list box and then make a report.
This
can
also
happen in one step.

I have records for projects. Each project has a product, and
one
product
can
be in many different projects.

I have tried Allen Brown's method (for reports) and one from
the
Microsoft
web page in which you use the Filter property of a form, but I
cannot
seem
to
get either one to work. I have gotten further with the
Microsoft
option,
but
I get an error message "You cancelled the previous operation".
This
method
also is not very clear about where to put the Command to open
the
form
based
on the filter criteria. I can see from the code that my
selection
is
getting
through but for some reason I am having difficulties opening
the
form
based
on the filter criteria. This could be because I am using the
wrong
syntax
to
specify the filter.

Can anyone help me?

This is the code I have:

Private Sub Command2_Click()

Dim Criteria As String
Dim i As Variant
'Build criteria string from selected items in list box
Criteria = ""
For Each i In Me![List0].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[PrimKey]='" _
& Me![List0].ItemData(i) & "'"

Next i

'Filter the form using selected items in the list box
Me.Filter = Criteria
Me.FilterOn = True

DoCmd.OpenForm "MainForm", acNormal, Criteria

End Sub