A
aman
Registered User.Local timeToday, 05:52JoinedOct 16, 2008Messages1,250
- Oct 26, 2018
- #1
Hi Guys
The below code doesn't update all the selected items in the listbox . It just loop through the first item multiple items. Can anyone please figure out what's going wrong here ? Thanks
The below code doesn't update all the selected items in the listbox . It just loop through the first item multiple items. Can anyone please figure out what's going wrong here ? Thanks
Code:Copy to clipboard
With Me.lstAllocation
For i = 0 To .ListCount - 1
If .Selected[i] Then
Set rs = CurrentDb.OpenRecordset["select * from tbl_RMS_MinStand_Mas_Allocation where AllocationID=" & Me.lstAllocation.Column[6] & ""]
' MsgBox Me.lstAllocation.ItemsSelected[i]
'If Not [rs.BOF = True And rs.EOF = True] Then
With rs
.Edit
.Fields["MonthRef"] = Me.cboMonthRef.Column[0]
.Fields["SupRef"] = Me.cboSupervisor1
.Update
End With
Set rs = Nothing
.Selected[i] = False
End If
Next i
End With
Gasman
Enthusiastic AmateurLocal timeToday, 13:52JoinedSep 21, 2011Messages9,338
- Oct 26, 2018
- #2
Walk through the code with the debugger. I cannot see why it does what you say it does?
Cronk
Registered User.Local timeTomorrow, 00:52JoinedJul 4, 2013Messages2,562
- Oct 26, 2018
- #3
Requery the list box. Updating the underlying data does not cause the list box display to be updated.
A
aman
Registered User.Local timeToday, 05:52JoinedOct 16, 2008Messages1,250
- Oct 26, 2018
- #4
I have selected first 2 records in a listbox and when i run the code with debugger then it just loops through the first one 2 times.
A
aman
Registered User.Local timeToday, 05:52JoinedOct 16, 2008Messages1,250
- Oct 26, 2018
- #5
the below statement retreives same allocation ID each time it tuns
Code:Copy to clipboard
Set rs = CurrentDb.OpenRecordset["select * from tbl_RMS_MinStand_Mas_Allocation where AllocationID=" & Me.lstAllocation.Column[6] & ""]
Gasman
Enthusiastic AmateurLocal timeToday, 13:52JoinedSep 21, 2011Messages9,338
- Oct 26, 2018
- #6
aman said:the below statement retreives same allocation ID each time it tunsCode:Copy to clipboardSet rs = CurrentDb.OpenRecordset["select * from tbl_RMS_MinStand_Mas_Allocation where AllocationID=" & Me.lstAllocation.Column[6] & ""]Click to expand...
So you have answered it yourself.? You are using the incorrect value to search on.
FWIW when I did something similar for a report I used
Code:Copy to clipboard
Set ctl = Me!lstCrew
' Now select what records from listbox
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strParam = strParam & ctl.ItemData[varItem] & ","
Next varItem
Else
MsgBox ["At least one Day Type is required"]
Exit Sub
End If
HTH
A
aman
Registered User.Local timeToday, 05:52JoinedOct 16, 2008Messages1,250
- Oct 26, 2018
- #7
I need to retrieve Column[6] value from a listbox each time so how can this be changed ?
Code:Copy to clipboard
Me.lstAllocation.Column[6]
A
aman
Registered User.Local timeToday, 05:52JoinedOct 16, 2008Messages1,250
- Oct 26, 2018
- #8
got it working
Gasman
Enthusiastic AmateurLocal timeToday, 13:52JoinedSep 21, 2011Messages9,338
- Oct 26, 2018
- #9
When silly things like that happen, I always go to the debugger. That then shows me where it does not do what I thought it was going to do.
The_Doc_Man
Immoderate ModeratorStaff member
Local timeToday, 07:52JoinedFeb 28, 2001Messages21,246
- Oct 26, 2018
- #10
It just loop through the first item multiple items.Click to expand...
Note that the code showed in post #1 of this thread steps through the list box but never changes the position of the RecordSet [i.e. has no RS.MoveNext command inside the loop].
Minty
AWF VIPLocal timeToday, 13:52JoinedJul 26, 2013Messages8,915
- Oct 26, 2018
- #11
@Doc - I think the recordset is specific to the outer loop [i] value hence it's recreated every iteration? e.g it's only retrieving a single record [I assume]
The_Doc_Man
Immoderate ModeratorStaff member
Local timeToday, 07:52JoinedFeb 28, 2001Messages21,246
- Oct 26, 2018
- #12
OK, Minty, noted. I missed something earlier. The fact that the row number wasn't part of the selector escaped my attention.
M
misscrf
Registered User.Local timeToday, 08:52JoinedNov 1, 2004Messages158
- Nov 19, 2019
- #13
Thank you for posting this solution. It's exactly what I needed, to use some listboxes for building criteria and appending that criteria onto a dynamic sql statement. Works like a charm!
Gasman said:So you have answered it yourself.? You are using the incorrect value to search on.
FWIW when I did something similar for a report I usedCode:Copy to clipboardSet ctl = Me!lstCrew ' Now select what records from listbox If ctl.ItemsSelected.Count > 0 Then For Each varItem In ctl.ItemsSelected strParam = strParam & ctl.ItemData[varItem] & "," Next varItem Else MsgBox ["At least one Day Type is required"] Exit Sub End If
HTHClick to expand...