Issue with form refresh/requery

[newguy86]

Here is the situation. I have a form attached to a temporary table that is setup to display records based on given search criteria. When I close the form I need all of the records to be updated back to the historical table so that if any changes are made to any records they are automatically saved to the record in the historical table.

The problem that I am having is when I try to requery or refresh the form using VBA the query that is supposed to take all of the records from the temporary table and update them into the historical table is still showing the old data and therefore updating that data back into the historical table. And it is only when I manually requery or refresh the form that the update query is showing the new data.

Now when I step through the macro, the debugger goes right through the requery line of code as if nothing is wrong, and when the debugger gets to the refresh line of code I get an error message stating the command or action refresh is not available. But then I immediately hit the 'Refresh' menu item in the 'Records' menu on the form manually and it works perfectly fine.

If anyone can provide assistance on this issue, it would be greatly appreciated.

P.S. Please keep in mind that I only use either the Requery command or the Refresh command in the macro seperately, I never use both of them at the same time. Also I have included my code for those who should want/need it.

Private Sub Command61_Click()
On Error GoTo Err_Command61_Click
        
    DoCmd.Requery
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acRefresh, , acMenuVer70
        
    DoCmd.Close

Exit_Command61_Click:
    Exit Sub

Err_Command61_Click:
    MsgBox Err.Description
    Resume Exit_Command61_Click
    
End Sub

Private Sub Form_Close()
On Error GoTo Err_Form_Close
    
    Dim x As Integer
    
    If Not IsNull(Me!Incident_Number) Then
        x = WriteClosedFormTimestamp04_05(Me, Me!Incident_Number)
    End If
    
    DoCmd.OpenQuery "qryUpdateComplaintsTempToComplaints"
    
    DoCmd.Close acForm, "Form_frmComplaints"
    
    DoCmd.OpenQuery "qryDeleteComplaintsTemp"

        
Exit_Form_Close:
    Exit Sub

Err_Form_Close:
    MsgBox Err.Description
    Resume Exit_Form_Close
End Sub

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!

 

[TheAceMan1]

How are ya newguy86 . . .

[blue] ... the query that is supposed to take all of the records from the temporary table and update them into the historical table is still showing the old data ...[/blue]

Well ... you updated the Historical table [red]but not the Temp table![/red] So sure ... your update query shows the same data, and sure the form shows no change ... as their both based on the temp table. In code I see your sequence of events as follows:
[ol][li]Update historical table.[/li]
[li]Update or reinitialize records in temp table.[/li]
[li]Requery form to see changes.[/li][/ol]
Close the form via a button or your choice.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[newguy86]

I am not sure I understand what you are getting at, but it doesn't matter now because the issue that I was having the whole time was my update query was using the data from the historic table to update the temporary table.





Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement!