Want field on new record to default to value of previous record

[tgbyhn10]

Hi

I expect this is very simple when you know how

I have some address fields displayed on a form, when the user adds a new record (via the default record selector) I want the screen to display a new blank record but have the address fields should default to the values from the previous record (the most recent record eneterd) in the table. I have a unique key set to auto number on the table if that helps and could put a date created on each record.

Thanks

Pete

 

[PeterF]

By using the biggest autonumber you normally get the last record entered, something like this

=DLookUp("YourFieldnameHere" , "YourTableNameHere", "IDFieldName = " & (DMax, "IDFieldName", "YourTableNameHere"))

in the default value of your control should give the result you're after.

 

[tgbyhn10]

Thanks Pete

Table "Quotes" includes

Unique Key Autonumber
Address Line 1 Text

I have set the default value property of "Address Line 1" to the following

=DLookUp("Address Line 1","Quotes","Unique key = " & (DMax,"Unique key","Quotes"))

Nothing happens, what have I done wrong?



Thanks

Pete

 

[tgbyhn10]

It's OK. I spotted it.

=DLookup("[Address Line 1]", "Quotes", "[Unique key] = " & DMax("[Unique key]", "Quotes"))

works great, thanks Pete

One question though

If I wanted to do this in VBA code in an event, where would be the best place to put it to achieve the same thing?

I prefer doing things like this in the actual code as it seems hidden away in the default value property.

 

[wazz]

i think you want it on the form's On Current event:

if me.newrecord then
...

 

[tgbyhn10]

Thanks. So when is the oncurrent triggered?

 

[ShaneMan]

Hey Pete,

Thought I would give you this link in case it helps and may give you a different way of looking at it.

Shane

http://www.mvps.org/access/forms/frm0012.htm

 

[wazz]

the On Current event triggers when you move to another record - whether it's a new record or an old record. the current record is whichever record you are on.

so, if you don't ask, "If Me.NewRecord Then (+ your code)", data in existing records will be edited. be sure to ask if it's a new record to avoid inadvertantly editing existing records.

 

[tgbyhn10]

Great. Thanks for the all the help. Pete