Records Refresh in VBA (2000)

Topic

New Reply

Is there a way to code in VBA the menu action Records > Refresh? In an archive/delete form, I’m trying to update the criteria form fields that are populated by the table that is having records archived and deleted. That is, when I delete records that were created between 1/1/2000 and 12/31/2000, I want the form fields to immediately repopulate with records that don’t have those dates.

Thanks,
Bob

Reply | Quote

Replies

After your delete code put the following command:
Me.Requery

Reply | Quote

I tried your suggestion of Me.Requery but had no luck. It’s part of a subroutine in the click event of the form. I tried in several places — after the delete statement, after the processing loop, after closing the recordset–even after the end user message that finishes the sub.

Thanks,
Bob

Reply | Quote

What are you seeing instead? Me.Requery actually reloads the recordset for the form. If you have, in fact, deleted records, there is no way for them to continue to show up in the form. There must be something you left our of your description.

Reply | Quote

Here’s the code for the command button click event, if this helps. I don’t want deleted records to show up after the refresh.

***************************

Dim db As DAO.Database

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim DeleteCt As Integer

Dim bDate As Date
Dim eDate As Date
Dim rNum As String

Set db = CurrentDb

‘Get beginning date, ending date, release number from Archive form

bDate = Forms![frmArchive]!lstStartDate.Value
eDate = Forms![frmArchive]!lstEndDate.Value
rNum = Forms![frmArchive]!lstRelease.Value

DoCmd.OpenQuery “Archive Records”, , acAdd

Set rs1 = db.OpenRecordset(“CorrectionCRs”)
DeleteCt = 0

If eDate = bDate Then

If rs1!CorrDate <= eDate Then

If rs1!CorrRel = rNum Then

rs1.Delete 'Delete records if they match archive criteria
DeleteCt = DeleteCt + 1

Me.Requery

End If

End If

End If
rs1.MoveNext

Loop

rs1.Close

MsgBox Format$(DeleteCt) & " record(s) successfully archived and deleted."

***************************

Thanks,
Bob

Reply | Quote

Me.Requery requeries the form. You’re working with recordsets and deleting records from them, which doesn’t *necessarily* affect the form. The logical thing to do is to loop through the records and delete them and only then to requery the form, assuming it is bound to the table you’ve been deleting records form. As I said before, records can’t show up after they’ve been deleted, so where are you seeing them? Is your form bound to CorrectionCRs? And why are you opening the query ArchiveRecords? What does it have to do with anything?

Reply | Quote

The form is bound to the CorrectionCRs table (although the form fields in Design View show the word Unbound). The ArchiveRecords query is an action query (add) that archives the records before they are deleted. Here is my intended process:

1. Choose start date, end date, and release number on form. Field lists come from CorrectionCRs table. Press OK.
2. ArchiveRecords query runs, archiving records based on form field values.
3. Deletion Loop runs, deleting records based on form field values.
4. Field lists on form refresh, using Me.Requery.

Step 4 is the problem. Instead of the field lists refreshing, they stay the same, like they’ve been copied there permanently.

Thanks,
Bob

Reply | Quote

If the controls on the form show Unbound, how do you expect them to be updated? Only controls bound to fields in the table will be updated, for unbound controls it makes no sense.

Reply | Quote

I bound the fields, as suggested. Now, when I select a date in the beginning date field, the same date is selected in the ending date field. I’d like to enter two different dates.

Thanks,
Bob

Reply | Quote

You did set the Control Source to different fields, I hope?

Reply | Quote

Kind of. The fields are populated by two different queries that pull data from the same table, same field. I’m trying to use the date fields on the records to create an archive range that includes real dates.

Thanks,
Bob

Reply | Quote

Even if you have two queries, if they pull the same field from the same table, how would you expect them to return different values? That would get me worried.

Reply | Quote

I’ve attached a stripped-down copy of the database if you want to look at it. I’m not sure how well I’m explaining what I’m trying to do.

Thanks,
Bob

Reply | Quote

Yuck! You bound the controls used for selecting the records to be archived. I was assuming that you had separate controls for displaying the records and for selecting.

This means that each time you select something in one of the list boxes, you are modifying the first record in the table.

You can do away with the lengthy code to loop through a recordset to delete records. A delete query along the same lines as the append query will do this for you: change the criteria in “Delete Records” to the same as those in “Archive Records”.

The much shorter code for cmdOK_Click on the form then becomes:

Public Sub cmdOK_Click()
If Forms![frmArchive]!lstEndDate.Value < Forms![frmArchive]!lstStartDate.Value Then
MsgBox ("The ending date must be equal to or later than the beginning date.")
Exit Sub
End If

' Archive
DoCmd.OpenQuery "Archive Records"
' Delete
DoCmd.OpenQuery "Delete Records"
' Requery
Me.lstStartDate.Requery
Me.lstEndDate.Requery
Me.lstRelease.Requery
End Sub

See attached modified database.

BTW, I would never, but *never* let end users open a table or query directly. I *always* use forms.

Reply | Quote

Yuck! You bound the controls used for selecting the records to be archived. I was assuming that you had separate controls for displaying the records and for selecting.

This means that each time you select something in one of the list boxes, you are modifying the first record in the table.

You can do away with the lengthy code to loop through a recordset to delete records. A delete query along the same lines as the append query will do this for you: change the criteria in “Delete Records” to the same as those in “Archive Records”.

The much shorter code for cmdOK_Click on the form then becomes:

Public Sub cmdOK_Click()
If Forms![frmArchive]!lstEndDate.Value < Forms![frmArchive]!lstStartDate.Value Then
MsgBox ("The ending date must be equal to or later than the beginning date.")
Exit Sub
End If

' Archive
DoCmd.OpenQuery "Archive Records"
' Delete
DoCmd.OpenQuery "Delete Records"
' Requery
Me.lstStartDate.Requery
Me.lstEndDate.Requery
Me.lstRelease.Requery
End Sub

See attached modified database.

BTW, I would never, but *never* let end users open a table or query directly. I *always* use forms.

Reply | Quote

I’ve attached a stripped-down copy of the database if you want to look at it. I’m not sure how well I’m explaining what I’m trying to do.

Thanks,
Bob

Reply | Quote

Even if you have two queries, if they pull the same field from the same table, how would you expect them to return different values? That would get me worried.

Reply | Quote

Kind of. The fields are populated by two different queries that pull data from the same table, same field. I’m trying to use the date fields on the records to create an archive range that includes real dates.

Thanks,
Bob

Reply | Quote

You did set the Control Source to different fields, I hope?

Reply | Quote

I bound the fields, as suggested. Now, when I select a date in the beginning date field, the same date is selected in the ending date field. I’d like to enter two different dates.

Thanks,
Bob

Reply | Quote

If the controls on the form show Unbound, how do you expect them to be updated? Only controls bound to fields in the table will be updated, for unbound controls it makes no sense.

Reply | Quote

The form is bound to the CorrectionCRs table (although the form fields in Design View show the word Unbound). The ArchiveRecords query is an action query (add) that archives the records before they are deleted. Here is my intended process:

1. Choose start date, end date, and release number on form. Field lists come from CorrectionCRs table. Press OK.
2. ArchiveRecords query runs, archiving records based on form field values.
3. Deletion Loop runs, deleting records based on form field values.
4. Field lists on form refresh, using Me.Requery.

Step 4 is the problem. Instead of the field lists refreshing, they stay the same, like they’ve been copied there permanently.

Thanks,
Bob

Reply | Quote

Me.Requery requeries the form. You’re working with recordsets and deleting records from them, which doesn’t *necessarily* affect the form. The logical thing to do is to loop through the records and delete them and only then to requery the form, assuming it is bound to the table you’ve been deleting records form. As I said before, records can’t show up after they’ve been deleted, so where are you seeing them? Is your form bound to CorrectionCRs? And why are you opening the query ArchiveRecords? What does it have to do with anything?

Reply | Quote

Here’s the code for the command button click event, if this helps. I don’t want deleted records to show up after the refresh.

***************************

Dim db As DAO.Database

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim DeleteCt As Integer

Dim bDate As Date
Dim eDate As Date
Dim rNum As String

Set db = CurrentDb

‘Get beginning date, ending date, release number from Archive form

bDate = Forms![frmArchive]!lstStartDate.Value
eDate = Forms![frmArchive]!lstEndDate.Value
rNum = Forms![frmArchive]!lstRelease.Value

DoCmd.OpenQuery “Archive Records”, , acAdd

Set rs1 = db.OpenRecordset(“CorrectionCRs”)
DeleteCt = 0

If eDate = bDate Then

If rs1!CorrDate <= eDate Then

If rs1!CorrRel = rNum Then

rs1.Delete 'Delete records if they match archive criteria
DeleteCt = DeleteCt + 1

Me.Requery

End If

End If

End If
rs1.MoveNext

Loop

rs1.Close

MsgBox Format$(DeleteCt) & " record(s) successfully archived and deleted."

***************************

Thanks,
Bob

Reply | Quote

What are you seeing instead? Me.Requery actually reloads the recordset for the form. If you have, in fact, deleted records, there is no way for them to continue to show up in the form. There must be something you left our of your description.

Reply | Quote

I tried your suggestion of Me.Requery but had no luck. It’s part of a subroutine in the click event of the form. I tried in several places — after the delete statement, after the processing loop, after closing the recordset–even after the end user message that finishes the sub.

Thanks,
Bob

Reply | Quote

After your delete code put the following command:
Me.Requery

Reply | Quote