unable to loop thru records

doncee

Using a multi select list box to open several records in a
pre - defined form. Most of the code that follows is taken
from a posting by Alan Browne on his web site. The click
routine is supposed to loop thru all of the reports, or in
this case records, selected in the list box & display them
for previewing or editing. In my situation it only displays 1
record in the form & does not perform the necessary loop.
Have tried futzing with it but getting no where fast.
Hoping someone can direct me to a solution. Below is the onclick() vb code:


Private Sub Command5_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String


strDoc = "EditClipFrm"


With Me.List12
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then

strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","

strDescrip = strDescrip & """" & .Column(1, varItem) & """, "

End If

Next

End With


lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "clipname: " & Left$(strDescrip, lngLen)
End If
End If

DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.NUMBER <> 2501 Then
MsgBox "Error " & Err.NUMBER & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

Thanks as always for any advice on this
dc

May 7 '06 #1

Bob Quintal

doncee <nodbcspam9814wanted@charter.net> wrote in
news:Xns97BC4068D14F2medbcSWBEll@216.196.97.131:
[color=blue]
> Using a multi select list box to open several records in a
> pre - defined form. Most of the code that follows is taken
> from a posting by Alan Browne on his web site. The click
> routine is supposed to loop thru all of the reports, or in
> this case records, selected in the list box & display them
> for previewing or editing. In my situation it only displays 1
> record in the form & does not perform the necessary loop.
> Have tried futzing with it but getting no where fast.
> Hoping someone can direct me to a solution. Below is the
> onclick() vb code:[/color]

This line is wrong:[color=blue]
> DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere[/color]
try,
DoCmd.OpenForm strDoc, acNormal,,strWhere

from the help file:
expression.OpenForm(FormName, View, FilterName, WhereCondition,
DataMode, WindowMode, OpenArgs)


--
Bob Quintal

PA is y I've altered my email address.

May 7 '06 #2

doncee

Bob Quintal <rquintal@sympatico.ca> wrote in
news:Xns97BC58EA943A2BQuintal@207.35.177.135:
[color=blue]
> doncee <nodbcspam9814wanted@charter.net> wrote in
> news:Xns97BC4068D14F2medbcSWBEll@216.196.97.131:
>[color=green]
>> Using a multi select list box to open several records in a
>> pre - defined form. Most of the code that follows is taken
>> from a posting by Alan Browne on his web site. The click
>> routine is supposed to loop thru all of the reports, or in
>> this case records, selected in the list box & display them
>> for previewing or editing. In my situation it only
>> displays 1 record in the form & does not perform the
>> necessary loop. Have tried futzing with it but getting no
>> where fast. Hoping someone can direct me to a solution.
>> Below is the onclick() vb code:[/color]
>
> This line is wrong:[color=green]
>> DoCmd.OpenForm strDoc, acNormal,
>> WhereCondition:=strWhere[/color]
> try,
> DoCmd.OpenForm strDoc, acNormal,,strWhere
>
> from the help file:
> expression.OpenForm(FormName, View, FilterName,
> WhereCondition, DataMode, WindowMode, OpenArgs)
>
>[/color]

Thanks for the reply, however I am still getting the same results. The form
opens but only for 1 of the highlited records. I am hoping to open as many
instances of the form as lines I have highlited in the listbox. If I have 4
lines highlited then I would like those 4 records opened in the selected
form. Does this make sense?? Thanks again for your reply.
dc

May 7 '06 #3

Richard Bernstein

doncee <nodbcspam9814wanted@charter.net> wrote in
news:Xns97BC5B6E04705medbcSWBEll@216.196.97.131:
[color=blue]
> Thanks for the reply, however I am still getting the same results. The
> form opens but only for 1 of the highlited records. I am hoping to
> open as many instances of the form as lines I have highlited in the
> listbox. If I have 4 lines highlited then I would like those 4 records
> opened in the selected form. Does this make sense??[/color]

The OpenForm code you used will open only one instance of the form with a
recordset of X records, equal to the number of items selected.
If you are sure that the WhereCondition returns more than one record, you
need a way to navigate through the the recordset, either custom controls or
the native navigation buttons.

Richard Bernstein

May 7 '06 #4

Bob Quintal

doncee <nodbcspam9814wanted@charter.net> wrote in
news:Xns97BC5B6E04705medbcSWBEll@216.196.97.131:
[color=blue]
> Bob Quintal <rquintal@sympatico.ca> wrote in
> news:Xns97BC58EA943A2BQuintal@207.35.177.135:
>[color=green]
>> doncee <nodbcspam9814wanted@charter.net> wrote in
>> news:Xns97BC4068D14F2medbcSWBEll@216.196.97.131:
>>[color=darkred]
>>> Using a multi select list box to open several records in a
>>> pre - defined form. Most of the code that follows is taken
>>> from a posting by Alan Browne on his web site. The click
>>> routine is supposed to loop thru all of the reports, or in
>>> this case records, selected in the list box & display them
>>> for previewing or editing. In my situation it only
>>> displays 1 record in the form & does not perform the
>>> necessary loop. Have tried futzing with it but getting no
>>> where fast. Hoping someone can direct me to a solution.
>>> Below is the onclick() vb code:[/color]
>>
>> This line is wrong:[color=darkred]
>>> DoCmd.OpenForm strDoc, acNormal,
>>> WhereCondition:=strWhere[/color]
>> try,
>> DoCmd.OpenForm strDoc, acNormal,,strWhere
>>
>> from the help file:
>> expression.OpenForm(FormName, View, FilterName,
>> WhereCondition, DataMode, WindowMode, OpenArgs)
>>
>>[/color]
>
> Thanks for the reply, however I am still getting the same
> results. The form opens but only for 1 of the highlited
> records. I am hoping to open as many instances of the form as
> lines I have highlited in the listbox. If I have 4 lines
> highlited then I would like those 4 records opened in the
> selected form. Does this make sense?? Thanks again for your
> reply. dc
>
>[/color]
Your form will open filtered to the records in your list. you
must use the navigation buttons at the bottom of the form to
move between each record, or redesign the form to show multiple
records.



--
Bob Quintal

PA is y I've altered my email address.

May 7 '06 #5

salad

doncee wrote:
[color=blue]
> Using a multi select list box to open several records in a
> pre - defined form. Most of the code that follows is taken
> from a posting by Alan Browne on his web site. The click
> routine is supposed to loop thru all of the reports, or in
> this case records, selected in the list box & display them
> for previewing or editing. In my situation it only displays 1
> record in the form & does not perform the necessary loop.
> Have tried futzing with it but getting no where fast.
> Hoping someone can direct me to a solution. Below is the onclick() vb code:
>
>
> Private Sub Command5_Click()
> On Error GoTo Err_Handler
>
> Dim varItem As Variant
> Dim strWhere As String
> Dim strDescrip As String
> Dim lngLen As Long
> Dim strDelim As String
> Dim strDoc As String
>
>
> strDoc = "EditClipFrm"
>
>
> With Me.List12
> For Each varItem In .ItemsSelected
> If Not IsNull(varItem) Then
>
> strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
>
> strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
>
> End If
>
> Next
>
> End With
>
>
> lngLen = Len(strWhere) - 1
> If lngLen > 0 Then
> strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"
> lngLen = Len(strDescrip) - 2
> If lngLen > 0 Then
> strDescrip = "clipname: " & Left$(strDescrip, lngLen)
> End If
> End If
>
> DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere
>
> Exit_Handler:
> Exit Sub
>
> Err_Handler:
> If Err.NUMBER <> 2501 Then
> MsgBox "Error " & Err.NUMBER & " - " & Err.Description, , "cmdPreview_Click"
> End If
> Resume Exit_Handler
> End Sub
>
> Thanks as always for any advice on this
> dc
>[/color]
As Bob said, you might need to set the navigation buttons on. If you
want you could pass an argument to the form if you don't want nav
buttons in certain conditions.
DoCmd.OpenForm strDoc, , , strWhere, , , "Test"

Then in the OnOpen event enter something like
Me.NavigationButtons = (Me.OpenArgs = "Test")
Me.AllowAdditions = Not (Me.OpenArgs = "Test")

If this is not the situation, then add the following code
msgbox strWhere
prior to opening the form. The code you provided us looks OK...not sure
what strDescrip is...but you should be getting a multiple record list.

May 7 '06 #6

doncee

salad <oil@vinegar.com> wrote in
news:kJu7g.387$t32.290@newsread2.news.pas.earthlin k.net:
[color=blue][color=green]
>>
>> Thanks as always for any advice on this
>> dc
>>[/color]
> As Bob said, you might need to set the navigation buttons
> on. If you want you could pass an argument to the form if
> you don't want nav buttons in certain conditions.
> DoCmd.OpenForm strDoc, , , strWhere, , , "Test"
>
> Then in the OnOpen event enter something like
> Me.NavigationButtons = (Me.OpenArgs = "Test")
> Me.AllowAdditions = Not (Me.OpenArgs = "Test")
>
> If this is not the situation, then add the following code
> msgbox strWhere
> prior to opening the form. The code you provided us looks
> OK...not sure what strDescrip is...but you should be
> getting a multiple record list.
>[/color]

Thanks to all who replied. Yes, in fact I do have the filtered
records that I was trying to produce, but I was unknowingly
canceling the form before cycling thru the records. It is clear
now what is happening. Thanks again for all of your help.
dc

May 8 '06 #7