Open form resets a list box (1 Viewer)

alanm2007

Registered User.
Local time
Today, 12:39
Joined
Sep 22, 2011
Messages
10
Hi all,

I'm a bit stumped on a problem - I have an access routine that works through a list box and opens up a form for each entry to update the record.
Trouble is that once the form has been opened and edited, the list box then resets, so I can only update one at a time - here's the code:

Private Sub cmdSendMani_Click()
On Error GoTo cmdSendMani_Click_Err

Dim stConNum As String
Dim varItem As Variant
Dim stWhere As String

For Each varItem In Me.lstSendMani.ItemsSelected

stConNum = Me.lstSendMani.Column(1, varItem)

DoCmd.OpenForm "frmShipList", acNormal, , stWhere, acFormEdit, acHidden

If Forms![frmShipList]![luStatus] = 1 Then
Forms![frmShipList]![luStatus] = 2
End If
Forms![frmShipList]![ManiSent] = -1

DoCmd.Close acForm, "frmShipList", acSaveYes
Next

Me.lstSendMani.Requery

cmdSendMani_Click_Exit:
Exit Sub

cmdSendMani_Click_Err:
MsgBox Error$
DoCmd.Close acForm, "frmPrint"
Resume cmdSendMani_Click_Exit

End Sub


Is there an alternative to opening a form and changing the data - maybe opening the recordset instead?

Any help apprieciated as always
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:39
Joined
Oct 29, 2018
Messages
21,357
Hi. I think you’re resetting the listbox when you do the Requery line. An alternative to what you’re doing is to use an UPDATE query instead.
 

alanm2007

Registered User.
Local time
Today, 12:39
Joined
Sep 22, 2011
Messages
10
Thanks DB - I did try taking out the requery part, but it still did the same. It looked like it got stuck when I opened the form to edit it - I'm guessing because the form with the list box lost focus and refreshed when it go the focus back - maybe?

Good suggestion on the update query, I will try that
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:39
Joined
Sep 21, 2011
Messages
14,038
How does it actually work?, as you have the value in stConNum and use stWhere to filter, yet that is not set?
 

alanm2007

Registered User.
Local time
Today, 12:39
Joined
Sep 22, 2011
Messages
10
Ah yes Gasman - I see I've left out some of the code:

stConNum = Me.lstSendMani.Column(1, varItem)
stWhere = "[Con_Num] = " & stConNum

Doh!
 

Kayleigh

Member
Local time
Today, 12:39
Joined
Sep 24, 2020
Messages
706
I've tried requerying listbox when opening form as new record but listbox still displays data from previous records.
What can I change in this code:
Code:
Private Sub cmdAddNew_Click()
DoCmd.OpenForm "frmIncidentLog"
DoCmd.GoToRecord , , acNewRec
Forms!frmIncidentLog!sbfrmIncidentDetailsStudent.Form!lstSelectedStudents.Requery
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2002
Messages
42,970
Base the loop on a query rather than a listbox.
 

Kayleigh

Member
Local time
Today, 12:39
Joined
Sep 24, 2020
Messages
706
Thanks for mentioning query - realised data source was not accurate...
Works now :)
 

Users who are viewing this thread

Top Bottom