Refreshing List box

[PeterM]

I have a form in AC2003 which has a listbox on it (List238) containing all
records for the table. My user can click on a listbox entry and I query that
record. Well, when my users change a field and click the save button it
executes the following code.

Private Sub BTN_Save_Click()
On Error GoTo Err_BTN_Save_Click
Dim strMsg As String
strMsg = strMsg & "Save Changes?" & Chr(13) & Chr(13)
If MsgBox(strMsg, vbQuestion + vbYesNo, "Please Confirm!") = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.List238.Requery
Me.Refresh
Else
DoCmd.RunCommand acCmdUndo
End If
Exit_BTN_Save_Click:
Exit Sub

Err_BTN_Save_Click:
MsgBox Err.Description
Resume Exit_BTN_Save_Click
End Sub

Here is the recordsource for the listbox

SELECT Diabetes_Diary.DD_Date AS [Date], Format([dd_date],"ddd") AS [Day],
Diabetes_Diary.DD_Average AS [Avg], Diabetes_Diary.DD_Comments AS Comments
FROM Diabetes_Diary
ORDER BY Diabetes_Diary.DD_Date DESC;

The field I'm having a problem with is Diabetes_Diary.DD_Average. It's not
calculated via the query, it's a stored field (I know that's not the way to
do it but I inherited this mess). When the user clicks the Save command
button the record is updated and the List238.Requery fires but the listbox
does not change. If the user closes the form and reopen it, the correct
value is displayed in List238.

This one is driving me nuts (granted, a short trip) but I'm at the end of my
rope. Can anyone see what I'm doing wrong?

Thanks for the help!

 

[John Spencer]

As a guess there is code somewhere (perhaps in the close event of the form)
that is recalculating the average and that code is not being executed when you
do the save but is being executed when you close the form.

I would also get rid of that old code call to Docmd.DoMenuItem

Private Sub BTN_Save_Click()
On Error GoTo Err_BTN_Save_Click
Dim strMsg As String
strMsg = strMsg & "Save Changes?" & Chr(13) & Chr(13)

If MsgBox(strMsg, vbQuestion + vbYesNo, "Please Confirm!") = vbYes Then
If Me.Dirty = True Then Me.Dirty = False 'Save bound record if changed
Me.List238.Requery
Me.Refresh
Else
'DoCmd.RunCommand acCmdUndo
Me.Undo 'Undo any changes to the form
End If

Exit_BTN_Save_Click:
Exit Sub

Err_BTN_Save_Click:
MsgBox Err.Description
Resume Exit_BTN_Save_Click
End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[James A. Fortune]

I have a form in AC2003 which has a listbox on it (List238) containing all
records for the table. My user can click on a listbox entry and I query that
record. Well, when my users change a field and click the save button it
executes the following code.

Private Sub BTN_Save_Click()
On Error GoTo Err_BTN_Save_Click
Dim strMsg As String
strMsg = strMsg & "Save Changes?" & Chr(13) & Chr(13)
If MsgBox(strMsg, vbQuestion + vbYesNo, "Please Confirm!") = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.List238.Requery
Me.Refresh
Else
DoCmd.RunCommand acCmdUndo
End If
Exit_BTN_Save_Click:
Exit Sub

Err_BTN_Save_Click:
MsgBox Err.Description
Resume Exit_BTN_Save_Click
End Sub

Here is the recordsource for the listbox

SELECT Diabetes_Diary.DD_Date AS [Date], Format([dd_date],"ddd") AS [Day],
Diabetes_Diary.DD_Average AS [Avg], Diabetes_Diary.DD_Comments AS Comments
FROM Diabetes_Diary
ORDER BY Diabetes_Diary.DD_Date DESC;

The field I'm having a problem with is Diabetes_Diary.DD_Average. It's not
calculated via the query, it's a stored field (I know that's not the way to
do it but I inherited this mess). When the user clicks the Save command
button the record is updated and the List238.Requery fires but the listbox
does not change. If the user closes the form and reopen it, the correct
value is displayed in List238.

This one is driving me nuts (granted, a short trip) but I'm at the end of my
rope. Can anyone see what I'm doing wrong?

Thanks for the help!

When I did the following:

New Form

Form's RecordSource:

SELECT Field1, Field2 FROM table1 ORDER BY ID;
List238 on the new form
txtField1 on the new form
txtField2 on the new form
BTN_Save command button on the new form

List238 Click event:

Private Sub List238_Click()
DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, List238.Value
End Sub

txtField1 ControlSource:
Field1

txtField2 ControlSource:
Field2

List238 RowSource:
SELECT ID, Field1, Field2 FROM table1;

(I used ColumnCount = 3 and BoundColumn = 1)

BTN_Save Click event as shown in your post

Note: You only need to include enough fields in the listbox to enable
the user to select the correct record.

When the form is opened, List238 starts at the first record and puts
the values in the the bound textboxes. When I changed a value and
clicked 'BTN_Save," List238 updated for me immediately without any
problems.

In short, I cannot duplicate your problem.

As far as calculating the Diabetes_Diary.DD_Average, once the record
is selected, you can put a value in by hand or have another command
button attempt the calculation by correlating the information you have
for that record on the form with whatever table data you use to make
that calculation. Alternatively, an update query might be able to
update all the averages at once from your source tables, separate from
using the edit form at all.

Note that the List238.Value part of GoToRecord only worked because the
ID values in table1 started at 1 and were contiguous. Some other
means of selecting the clicked record onto the form, perhaps by using
a RecordsetClone, should be used if that is not the case.

James A. Fortune
(e-mail address removed)