I Can't figure out how to automatically refresh form?

[steve728]

I thought I should send the whole sub procedure for help.
Once this process is completed, it returns to the
form. The new record does not display unless I close the
form and repopen it or go to the bottom rec and return back to this current rec. I tried me.requery, me.refresh and nothing!!#@$#@!. Please help me with a solution. Your help will make my month!

Steve

If MsgBox("Are you sure you want to copy this item?", vbYesNo, "Question&quot = vbYes Then

Set rsCodes1 = New ADODB.Recordset
Set rsCodes2 = New ADODB.Recordset
mCode = Me.CODE.VALUE
mdesc = Me.DESC.VALUE

mrecsource = Me.RecordSource
DoCmd.SetWarnings False

' Make-table SQL statement
rsCodes1.Open "SELECT * INTO temp FROM arbcodes WHERE arbcodes.code = '" & _
mCode & "' and arbcodes.desc = '" & mdesc & "';", etc...
' Now append this record into arbcodes table and refresh
rsCodes2.Open "INSERT INTO ARBCODES SELECT temp.* FROM temp;", etc...
Me.Requery
DoCmd.SetWarnings True

adoConnection.Execute "DROP TABLE temp"


Me.RecordSource = mrecsource

End If

 

[AccessAce]

To be safe, I do this.

Me.Requery
Me.Refresh
Me.Repaint

 

[steve728]

Thank you so much for trying to help me so quickly,
however, it didn't help.

I still have hope cyber buddies!

Steve

 

[GKProgrammer]

Hi Steve,

I am little confused with the above code, can you tell me where these two lines fit into this code:

mrecsource = Me.RecordSource
Me.RecordSource = mrecsource


Also I notice you requery the form but how about doing a requery on the recordset itself first.


Regards,
gkprogrammer

 

[steve728]

mrecsource = saves the user's current query source. There are 4 different sorts (queries to choose from)

me.recordsource just makes sure to put the sort back to how
it was before this process changes. I thought it would help with the refresh to get a "fresh" copy of the backend query.

Steve

 

[steve728]

Thanks GKProgrammer,

I forgot to add that I already tried the requery by
adding, just after the appending code,
rs.open "select * from arbcodes;", etc...
rs.requery
rs.close

It didn't help.

Still hoping...

Steve

 

[steve728]

One additional bit of information that may give someone a clue:

Once the above process is finished I must wait a couple of seconds for the "DoCmd.GoToRecord , , acLast" to work.
There is an obvious delay I need to consider. I already tried changing the menu item Options\Advanced\Refresh Interval (sec) to 1 but still no luck. I'll try anything!

Still hoping and praying I get some help!!!

 

[zevw]

Steve28!

I am sorry, but I don't understand you are making this so complicated this is what I would write. what is the need for creating all these new recordsets.

If MsgBox("Are you sure you want to copy this item?", vbYesNo, "Question&quot = vbYes Then

mCode = Me.CODE.VALUE
mdesc = Me.DESC.VALUE

DoCmd.SetWarnings False

' Make-table SQL statement
DoCmd.RunSQL "SELECT * INTO temp FROM arbcodes WHERE arbcodes.code = '" & _
mCode & "' and arbcodes.desc = '" & mdesc & "';", etc...
' Now append this record into arbcodes table and refresh
DoCmd.RunSQL "INSERT INTO ARBCODES SELECT temp.* FROM temp;", etc...
Me.Requery
DoCmd.SetWarnings True

End If