I am trying to get a form to pick up the record data from a different record to this new record

[Thomas Simsion]

I have a sub with two records Service date and next service date I would
like to be able to fill in service date from the previous record next
service date Can it be done.

 

[Guest]

This may be the same problem I am trying to solve. I am using the Inventory program that comes as an example with Office 2000. For the way we do business I have items in inventory with pricing. When I want to make a Purchase Order I want to select an item and then have Access fill in the Price that corresponds to the item on the Purchase Order. Have you gotten any feedback from your question?

 

[mikepage5]

I normally have mine set the text box with the date you
wish to be automatically updated to the service date
default setting set to the max date of that particular
records previous entries, ie service date max as default
entry for the text box and not the table. I have my
current project set to put in the previous mileage of a
vehicle based off of the max mileage previously entered
into the previous mileage text box of the current entries
form. There is much more to mine but hopefully this
helps.

 

[Tony Vrolyk]

Not sure if there is a better way but this just popped in my head.

Create variables for each of the controls you want this to effect. Have the
variables assinged to the curent value On Current. Then Before Insert,
assign the variable's value to the appropriate controls.

The Before Insert doesn't fire until you actually start typing something so
the new record will initially be blank. You could have your own custom New
button to have the controls filled in before actually starting typing. In
this case you would then want to disable the New button so someone doesn't
repeatedly hit it and create a bunch of mostly blank records (we all have
those users who double-click everything)

**sample**
Option Compare Database
Option Explicit
Dim variable1 as whatever
Dim variable2 as whatever

Private Sub Form_Current()
if not me.newrecord
variable1 = me.text1
variable2 = me.text2
etc...
end if
exit sub

Private Sub Form_BeforeInsert(Cancel As Integer)
me.text1 = variable1
me.text2 = variable2
etc...
exit sub

**end sample**

Tony

 

[Kelvin]

This is a good option except that this is based on the form so when you
close the form the default option vanishes. Another option is to use the
DLookup function but without knowing more about your table structure I can't
be more specific. Look at the help for the syntax.

Kelvin