run code moving from record to another record

[gearcam]

Copy your db, delete irrelevant object from copy, enter test data and upload.

not so easy as it is huge with lots of forms and tables but i will copy the relevent bits to a new database

 

[gearcam]

Ok see attached test database

 

[vbaInet]

Before I look at it, I think we need to clarify what you mean by the previous record. Do you mean the record just looked at or the record before the current record?

For example, I could be on record 10 and jump to record 20. Is my previous record record 10 or is it record 19 or 9?

 

[gearcam]

i mean the record just looked at

In this case

For example, I could be on record 10 and jump to record 20. Is my previous record record 10 or is it record 19 or 9?

the record i want to amend is no 10

 

[vbaInet]

Note that your Locking strategy could cause you more problems than good. See amended code:

Option Compare Database
Option Explicit


Private blIsLoaded   As Boolean
Private dblPrevJobNo As Double


Private Sub Form_Current()
On Error GoTo Err_New_1_Click
 
    If blIsLoaded = True Then
        Dim rs As DAO.Recordset
    
        Set rs = Me.RecordsetClone
        
        With rs
            .FindFirst "[Job No] = " & dblPrevJobNo
            
            If Not .NoMatch Then
                .Edit
                !who_formopen = "new value: " & dblPrevJobNo
                .update
            End If
        End With
        
        Set rs = Nothing
    Else
        blIsLoaded = True
    End If
    
    dblPrevJobNo = Me.Job_No

Exit_New_1_Click:
    Exit Sub

Err_New_1_Click:
    Set rs = Nothing
    If Err.Number = 3021 Then
'        MsgBox [who_formopen] & " " & Err
    End If
    Resume Exit_New_1_Click

End Sub

 

[gearcam]

thanks that seems to work in the test ill try it tommorow for real
I am trying to understand how it works how does blIsLoaded get its data ?
Ok i get it the first time througn it must be Null then its set

 

[vbaInet]

It's only a boolean variable that ensures the code doesn't fire when the form initially loads. There are other ways of doing it but this one is the quickest.

 

[gearcam]

hi thanks for your help it works pretty good but not all the time i can not see why

 

[vbaInet]

If it errors you need to reset the global variables.

 

[gearcam]

it does not error out it sometimes just does net set the field as it should
but i can not seem to trace exactley the rout of the problem

It runs on a sub form, the main form has serch functions reset etc etc etc so i have to figure out exactley the events that make it miss this routine !!

 

[vbaInet]

How do you know it doesn't error? Didn't I comment out the Msgbox in the error handler?

Even if you think it doesn't error, those variables must be reset in the error handler. I did tell you tell that your locking strategy could cause you more problems than good. I just gave you the code because you asked for it.

 

[gearcam]

i have the mesg box running and a stop line in the start of the error check.
Its not the code that is wrong the main form sometimes does not appear to run the "Current" section sometimes from a search or somthing not sure what yet

 

[vbaInet]

If that's the case then your form sounds like it's corrupt.