By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,779 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

unable to loop thru records

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
doncee <no*****************@charter.net> wrote in
news:Xn*************************@216.196.97.131:
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:
This line is wrong: DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere

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

P: n/a
Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:
doncee <no*****************@charter.net> wrote in
news:Xn*************************@216.196.97.131:
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:


This line is wrong:
DoCmd.OpenForm strDoc, acNormal,
WhereCondition:=strWhere

try,
DoCmd.OpenForm strDoc, acNormal,,strWhere

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


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

P: n/a
doncee <no*****************@charter.net> wrote in
news:Xn*************************@216.196.97.131:
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??


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

P: n/a
doncee <no*****************@charter.net> wrote in
news:Xn*************************@216.196.97.131:
Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:
doncee <no*****************@charter.net> wrote in
news:Xn*************************@216.196.97.131:
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:


This line is wrong:
DoCmd.OpenForm strDoc, acNormal,
WhereCondition:=strWhere

try,
DoCmd.OpenForm strDoc, acNormal,,strWhere

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


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

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

P: n/a
doncee wrote:
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

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

P: n/a
salad <oi*@vinegar.com> wrote in
news:kJ***************@newsread2.news.pas.earthlin k.net:

Thanks as always for any advice on this
dc

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.


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

This discussion thread is closed

Replies have been disabled for this discussion.