Open form resets a list box

[alanm2007]

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]

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]

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]

How does it actually work?, as you have the value in stConNum and use stWhere to filter, yet that is not set?

 

[alanm2007]

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]

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:

Private Sub cmdAddNew_Click()
DoCmd.OpenForm "frmIncidentLog"
DoCmd.GoToRecord , , acNewRec
Forms!frmIncidentLog!sbfrmIncidentDetailsStudent.Form!lstSelectedStudents.Requery
End Sub

 

[Kayleigh]

Thanks for mentioning query - realised data source was not accurate...
Works now