Listbox .Requery

[trackdaychamp]

Hi,

I have an Access front end linking to a SQL Server backend via an ADP file.

When I edit a record, I am having trouble refreshing the listbox that displays that same record. I am trying to use the .Requery method (see code below)

Can anyone suggest a better way?

Thanks

Mark


Private Sub cmdEditProg1_Click()

'Edit the Strategic Programme Record

strQuery = "SELECT * FROM Tbl_StrategicProgrammes" & _
" Where ProgNo = " & TxtProgNo

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strQuery, cn, adOpenDynamic, adLockOptimistic

If Not rs.EOF Then

rs.Fields("ProgName").Value = txtProgName
rs.Update
MsgBox "The Strategic Programme has been modified", vbInformation, "PMO"

End If

rs.Close
Set rs = Nothing
Set cn = Nothing

LstProg.Requery

End Sub

 

[Bodisathva]

Are you actually using the SQL tables as the recordsource or are you using a snapshot recordset or recordset clone? Since you would be unable to edit a record which is currently displayed in the list box if the list was dynamic, I would be inclined to think you need to re-establish the actual recordsource for the list box after your edit.

 

[trackdaychamp]

Hi,

I am not sure I understand. Do you mean I should open a new recordset [which will take into account the edited data] and re-populate the listbox from this?

Thanks,

Mark