Can you use me.refresh then return to the record you were working on? 1

[deanbri75]

I have some VBA code that inserts the current date into a record when you click on an unbound button in my form. When you click, it does add the date to the table, but the changes aren't displayed in the form unless I refresh the form. I added me.refresh to the code and while that helps, it always returns my form to the first record. Is there a way to refresh the form and have it stay on the record i'm editing?

I'm using windows XP and Office 2003. Here's my code:

Private Sub Command60_Click()
On Error GoTo Err_Command60_Click

Dim db As CurrentProject
    Dim strSQL As String
    
    Set db = CurrentProject()

    strSQL = _
    "UPDATE dbo.PaperApp SET PriorityDate = CONVERT(CHAR(10), GETDATE(), 101) WHERE (SSN = "
 
    strSQL = strSQL & Me!SSN & ")"
    
    DoCmd.RunSQL strSQL
    Me.Rerefresh

Exit_Command60_Click:
    Exit Sub

Err_Command60_Click:
    MsgBox Err.Description
    Resume Exit_Command60_Click
    
End Sub

Thanks in advance!
brian

 

[RoyVidar]

Ah - ADP or at least SQL-server backend?

Usually, in Access, the refresh won't alter the current record of the form, but I think perhaps it does in ADPs?

But first, what do you think you're doing with the currentproject thingie?`

I'd probably execute this on the connection, either through declaring a connection object, or directly

[tt]currentproject.connection.execute strsql,,adcmdtext+adexecutenorecords

' or

dim cn as adodb.connection
set cn = currentproject.connection
cn.execute strsql,,adcmdtext+adexecutenorecords[/tt]

So - the SSN is the PK? The general apprach, get the PK, do the refresh, use a gotorecord thingie/find...

assuming ADP -> ado recordset perhaps the following would work
[tt]dim lngpk as long
dim rs as adodb.recordset
lngpk=me!ssn.value

' do your stuff

set rs = me.recordsetclone
rs.find "SSN = " & lngpk
if not rs.eof then
me.bookmark = rs.bookmark
end if[/tt]

If mdb, use dao.recordset, and the .findfirst method, and test for .nomatch in stead of .eof

(air code...not tested)

Roy-Vidar

 

[deanbri75]

Thanks for the tips. Here's what ended up working for me:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSSN As String
Set cn = CurrentProject.Connection
Set rs = Me.RecordsetClone
strSSN = Me!SSN.Value

strSQL = _
    "UPDATE dbo.PaperApp SET PriorityDate = CONVERT(CHAR(10), GETDATE(), 101) WHERE (SSN = "
 
    strSQL = strSQL & Me!SSN & ")"

cn.Execute strSQL, , adCmdText + adExecuteNoRecords
Me.Refresh

rs.Find "SSN = " & strSSN
If Not rs.EOF Then
  Me.Bookmark = rs.Bookmark
End If

Exit_Command60_Click:
    Exit Sub

Err_Command60_Click:
    MsgBox Err.Description
    Resume Exit_Command60_Click

I had to change the type from Long to String. The Find method requires that criteria be a string value.

thanks again!