Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Records Refresh in VBA (2000)

    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

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Records Refresh in VBA (2000)

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

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Refresh in VBA (2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Records Refresh in VBA (2000)

    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.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Refresh in VBA (2000)

    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

    MsgBox ("The ending date must be equal to or later than the beginning date.")
    End

    End If

    rs1.MoveFirst

    'Check all records in CorrectionCRs table to see if they match archive criteria

    Do While Not rs1.EOF

    If rs1!CorrDate >= 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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Records Refresh in VBA (2000)

    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?
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Refresh in VBA (2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Records Refresh in VBA (2000)

    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.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Refresh in VBA (2000)

    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Records Refresh in VBA (2000)

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

  11. #11
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Refresh in VBA (2000)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Records Refresh in VBA (2000)

    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.

  13. #13
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    146
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Refresh in VBA (2000)

    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
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Records Refresh in VBA (2000)

    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. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •