Record is in edit or add state ?

[lutajuca]

Hello...thanks in advance for any help.
I was looking for answer here and on the network, but ....
In sub form (datasheet; for add and delete and edit records ), how, when and where can I test the state of the record to see if it is a new record or just editing an existing record? ( would correspond to Form_AfterUpdate ) Thanks, again.

 

[John Big Booty]

Check out the NewRecord property and the Dirty property

 

[lutajuca]

Thanks a lot …. Just to check…
a) If this is ok
Private Sub Form_Current()
If Me.NewRecord Then
NewRec=true ‘ MsgBox "New one"
Else
NewRec=False ‘ MsgBox "Old one"
End If
End Sub
b) Form_AfterUpdate should test NewRec
Private Sub Form_AfterUpdate()
If NewRec Then
MsgBox "New one"
Else
MsgBox "Old one"
End If
End Sub

Last performed Form_Current determined NewRec for that record.
Is all this correct?

(Title was not adequate, would be better “ Record is new one or old one “ …. )

 

[John Big Booty]

Your syntax is not quiet correct it should be;

Private Sub Form_Current()
If Me.NewRecord = True Then
     MsgBox "New one"
Else
     MsgBox "Old one"
End If
End Sub

You can not check the NewRecord in the After Update event as once the record has been updated it is no longer New so you will always get a False response, try the Before Update event

 

[gemma-the-husky]

you can also "see" the state of a record, by adding record selectors to the form. A clean record will have a black triangle, and an edited record, a pencil

 

[lutajuca]

A propo syntax... my MsgBox is in comment.
AfterUpdate vs BeforeUpdate (btw, i have not tested me.NewRecord, I tested the variable NewRec in Form_AfterUpdate) … i think, after “Form_BeforeUpdate” and before” Form_AfterUpdate”, “Form_Current” is impossible … well, there is no difference for the question: new record or old record (for my problem there have differences) … I think…
i have record selector, i see the state of record, but how can i “see” in VBA? New record pass two state, i know .
But this does not solve my need:
DefaultValue from next (new) record depends on value in last added record (one user in one session)). First added record don’t have DefaultValue.
I tested solution with NewRec, Form_Current and Form_AfterUpdate, it is worked, but i’m predictable, calm user, end user is not!
I thought about this and i remembered ID ( in the structure of record)
Dim cID as Long
cID=0
…
Form_AfterUpdate :
if me.ID>cID then
Me.AA.DefaultValue=me.AA (example)
cID=me.ID
end if
I think this can work correctly ? Do you see some oversight?

 

[vbaInet]

What is your ultimate goal? You want to set the Default Value of a field only when it's a new record?

The Default Value will only "fires" for new records.

 

[lutajuca]

Ultimate goal - Save values from just now added record ( it was "new"!) as default value for next new record.

Unfortunately, I saw one oversight (one) … if the user first, when entering subform, he edited the old record and not add new record
It can be repaired: it is necessary to fill cID (now control in subform!), in on_enter ( event in Main form)……
in MainForm :
Private Sub contSubForm_Enter()
Me.contSubForm.Form!cID = DLookup("max(ID)", "table", True)
End Sub
in SubForm:
‘ cID is now text box in header or footer! (how to refer to a subform variable?)
Form_AfterUpdate :
if me.ID>me.cID then
Me.AA.DefaultValue=me.AA (example)
me.cID=me.ID
end if
Do you see now some other oversight? Unfortunately, although this may be the solution, not just elegant!
It would be better ,solution with NewRec, Form_Current and Form_AfterUpdate , if ….

 

[vbaInet]

So what is wrong with using the Default Value property of the textbox?

 

[lutajuca]

“So what is wrong with using the Default Value property of the textbox?” - in general, nothing ….I do not understand why this question
I need to save values (in Form_AfterUpdate ) from just now added record ( it was "new" , not from “old” and edited) as default value for next (when it appears) new record.
Solution with NewRec, Form_Current and Form_AfterUpdate, it is not worked good ! Some refresh -> Form_Current , and i loose info for NewRec…. pity. I will try with Form_BeforeInsert and Form_ AfterInsert instead of Form_Current…

 

[gemma-the-husky]

i think we just don't understand your problem. the before update event fires before any record is saved to the database. in that event we can set a value in code for any field.

so we can test the value first within that event

Hence this sort of thing -

if nz(somefield,"") = "" then
somefield = "whatever value"
end if

don't use the after update event , because that will just make the record dirty again!

 

[lutajuca]

It's my mistake, i'm sorry…. wrong title, my language, explanation…
BeforeUpdate is better place i f the record will be remembered ... I must to set the DefaultValue, the user can change it , and I have not started the entry, the user will, if they want ...
If I make a dirty record in AfterUpdate, what are the consequences ?
But, i think, I found answer , with you all indeed, …. I need to do just and only this :
Private Sub Form_AfterInsert
‘ . . .
Me.somefield.DefaultValue = Me.somefield ‘example
‘ . . .
end Sub
AfterInsert is only for ‘new’ record , and record is saved …
Thanks again, I'll confirm when I do a full test (I made some simplification)

 

[gemma-the-husky]

if you use after update to set data in the current record, the record becomes dirty again, and must be saved again. if you want to set data in the current record, the best place to do it is in the before update event.

if you need to do some tidying up after the record save that does not affect the current record, then use after update.

you can use afterinsert, beforeupdate/ table default values - it just depends what you want to do, exactly.

90% of the time, I would say beforeupdate is the best event to use to set non-volatile information, if you will

 

[lutajuca]

Thank you very much, it’s good to hear details from experts.
i have solution tested , it's quite good.
I would like to summarize :
If you need to save data in a record that was just the first time entered into the database, as a default values for a next data record to be entered into the database, one possible solution is AfterInsert event :

Private Sub Form_AfterInsert
…..
Me.Field1.DefaultValue = IIf(Me.Field1= nnn, mmm, Me.Field1 + 1) ‘numeric , as [FONT=&quot]cyclic counter [/FONT]
Me.Field2.DefaultValue = "'" & Me.Field2 & "'" ‘ date, same value as the previous one
Me.Field3.DefaultValue = IIf(Len(Nz(Me.Field3) & "") = 0, Me.Field3.DefaultValue, "'" & Me.Field3 & "'") ‘ date , optional
Me.Field4.DefaultValue = "'" & Me.Field4.Column(1, Me.Field4.ListIndex) & "'" ‘ combo box
…..
End Sub