run code moving from record to another record

[gearcam]

HI how can i run some code when moving from one record to another

I know when the new record opens i can use current but whats the oppsite ie when leaving the record, not closing the form thats easy.

I might have 10 records from a search so as i scroll through them in a form i want to save some data on the record that is now not current.

Steve

 

[vbaInet]

You want to perform some action on the record that you're leaving?

 

[gearcam]

Yes exactly

 

[vbaInet]

Can you tell us what kind of action?

 

[gearcam]

yes just amend a field content
So on opening the record the "current" sets a field to a value
Then on leving it sets to another value.

This will then be used to se if the form is open elswhere on a shared database

 

[vbaInet]

Do you know how to work with recordsets?

 

[gearcam]

probablly i have quiete a good understanding of VB in access

Can you show me what you mean ?

 

[vbaInet]

From the RecordsetClone you can move to the previous record and do whatever you want to do there. You will still perform this function in the Current event of your form. E.g.:

dim rs as dao.recordset

set rs = me.recordsetclone

on error goto errorhandler
with rs
    .moveprevious
    .edit
    !FieldName = "new value"
    .update
end with

set rs = nothing

... your error handler here...

 

[gearcam]

thanks i am going to giove this a try

So i add this into the form current code ?

I always get error 3021 "no record"

 

[vbaInet]

You will get that error on the first record (i.e. when you first open the form) and that's why I wrote in the code "... your error handler here... ", plus make sure your form's Record Source is a query, not a table.

 

[gearcam]

That must be the problem then my form is linked to a table

It works if i change to .movefirst
so can i get the number of the record currentley showing on the screen and use that somehow ?

 

[vbaInet]

And have you changed it to a query?

No, it's not movefirst you want. I've already given you the code you need. Put error handling and it will be good to go.

 

[gearcam]

no chaning to a query is a bit more complicated i think thi form has many many uses not sure if they will all work ill give it a go

 

[vbaInet]

If it was complicated I won't ask you to change it to a query. In any enironment, be it single-user or multi-user, it is advised to base your forms on queries.

 

[gearcam]

Ok i have changed the data source for a new query that is linked to the table
but i still get error 3021

 

[vbaInet]

You're not fully doing what I advised. Let me see your entire Sub procedure.

 

[gearcam]

Ok here is the full set up
The base table for all the data is called orders
I have a query with all fields called ordersfull (this is the new part)
I have a form called orders which is linked to the query ordersfull
 
this is the current section of the orders code
 
Private Sub Form_Current()
On Error GoTo Err_New_1_Click
 
Dim rs As dao.Recordset
Set rs = Me.RecordsetClone
With rs
.MovePrevious
.Edit
!who_formopen = "new value"
.update
End With
usercheck:
Set rs = Nothing
 
 
'all sorts of code here to do various calcs etc etc ....... 
 
 
Exit_New_1_Click:
Exit Sub
Err_New_1_Click:
temp = Err
If Err = 3021 Then
MsgBox [who_formopen] & " " & Err
Resume usercheck
End If
If Err = 2448 Then
MsgBox [who_formopen] & " has the form already open, opening read-only. " '& Error$
Resume Exit_New_1_Click
Else
MsgBox Err.Description
lockform
Resume Exit_New_1_Click
End If
 
End Sub

 

[vbaInet]

In the future, please put your code in Code Tags. See here for explanation:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23

Upload a test db so I can see what's going on. In the FAQs above, you will see a link on how to upload a db on here.

 

[gearcam]

ok ill will buld a new test database from scratch and see what happens
thanks
Steve

 

[vbaInet]

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