Setting a value to a field in a new row to match the last row in a form

[edme]

Hello

Seems to be an easy problem but I am a newbee in VBA.
I have a form with a list of resources, I want to set the value of one of the fields (type) to match the same field in the previous row when clicking on a new row button.

Any ideas ?

[NeoPa]

Do you need this to default? Or would Ctrl-' do you?

When entering data into a datasheet (This works in Excel as well as Access) hold down Ctrl while tapping ' (Ctrl-') to replicate the value from the cell above.

[edme]

Do you need this to default? Or would Ctrl-' do you?

When entering data into a datasheet (This works in Excel as well as Access) hold down Ctrl while tapping ' (Ctrl-') to replicate the value from the cell above.

is there a way to do this to specific fields automatically when creating a new record ?

[missinglinq]

The question is whether these records are being entered in a single session, i.e. the form being opened, then one record after another after another, etc, being entered. If so using a hack that sets the DefaultValue for the field, as NeoPa alluded to, is the way to go.

An example of that would be

Private Sub YourTextBoxName_AfterUpdate()
  Me.YourTextBoxName.DefaultValue = """" & Me.YourTextBoxName.Value & """"
End Sub

If, on the other hand, multiple sessions are involved, with the form being opened, a record or records being entered, the form being closed then re-opened at a later date, and more records entered, this method won't work. DefaultValues assigned thru code will be 'lost' when the form is closed.

In this latter case I think you'd have to use a combination of DLookup() and DMax() against an auto-incrementing ID field or against a timestamp field, or, alternately, store the value in a utility table when the form is closed and retrieve it and assign it to the DefaultValue each time the form is opened.

Welcome to Bytes!

Linq ;0)>

[ADezii]

is there a way to do this to specific fields automatically when creating a new record ?

If you wish to retrieve the Value from a specific Field, from the prior Record for a New Record, and Insert it into the Field, you can do this in the GotFocus() Event. The following code will Insert the Value from the Region Field from the Previous Record into the Current (New) Record when the Field receives the Focus.

Private Sub Region_GotFocus()
  If Me.NewRecord Then
    SendKeys "^'", True
  End If
End Sub

[missinglinq]

Using SendKeys really should be avoided unless there is absolutely, positively no other way to accomplish a task, which is not the case here.

SendKeys is unreliable under a number of Windows/Access version combinations, with reports of it not working at all under Vista and not always working under Access 2007/2010. There is also a problem with it toggling the NumLock and CapsLock keys in some instances.

Linq ;0)>

[parodux]

I believe the best way to do this is:

Private Sub Form_Current()
 Dim rs As DAO.Recordset
  If Me.NewRecord Then
      Set rs = Me.RecordsetClone
      rs.MoveLast
      Me!NameOfField.DefaultValue = rs!NameOfField
      rs.Close
  End If
End Sub

[NeoPa]

You don't really want line #7 (although I understand the laudable impulse).

It's generally good practice to leave objects as you find them. If you open a recordset, then it's right to close it when finished. If, as in this case, you find it open but assign a new object to refer to it, then it should be left open, but the new object link should be cleared (Set rs = Nothing) when done.

This is probably not a serious problem (I think the close would be ignored anyway) but the point is worth making for the understanding. Ultimately all objects are cleared at the end of the procedure anyway, but I'm guessing you like your code to be tidier than that, which I fully understand and agree with.

As far as the issue is concerned, I would have thought it would be less code intensive to change the default as and when a change is made on the form (ControlName_Aft erUpdate()). The Form_Open() event procedure could be used to set it if that were deemed necessary.

[edme]

Thanks all,
I actually used @missinglinq advise and it worked perfectly.

[parodux]

You don't really want line #7 (although I understand the laudable impulse).

It's generally good practice to leave objects as you find them. If you open a recordset, then it's right to close it when finished. If, as in this case, you find it open but assign a new object to refer to it, then it should be left open, but the new object link should be cleared (Set rs = Nothing) when done.

This is probably not a serious problem (I think the close would be ignored anyway) but the point is worth making for the understanding. Ultimately all objects are cleared at the end of the procedure anyway, but I'm guessing you like your code to be tidier than that, which I fully understand and agree with.

As far as the issue is concerned, I would have thought it would be less code intensive to change the default as and when a change is made on the form (ControlName_Aft erUpdate()). The Form_Open() event procedure could be used to set it if that were deemed necessary.

I think I get your point!~)

Anyway, yes.. I do like to keep my code tidy and the Set rs = Nothing I just forgot, I normally use that to.

..About the afterupdate... edme just wants a value from the last post when in a new post. So if you open the form and goto new post you need to pick up the value from the last post on the way!~)

[NeoPa]

parodux: ..About the afterupdate... edme just wants a value from the last post when in a new post. So if you open the form and goto new post you need to pick up the value from the last post on the way!~)

This is not always required. It is not clear from the question weather or not a New Record after opening the form should have any default set. If it is required then the Load event would be an appropriate place for it. I cannot think of any situation where AfterUpdate code would be required in either scenario.

BTW I had a strong suspicion you were a tidy coder as only tidy coders would even have thought of closing .RecordsetClone, even via a recordset variable.

[parodux]

This is not always required. It is not clear from the question weather or not a New Record after opening the form should have any default set. If it is required then the Load event would be an appropriate place for it. I cannot think of any situation where AfterUpdate code would be required in either scenario.

BTW I had a strong suspicion you were a tidy coder as only tidy coders would even have thought of closing .RecordsetClone, even via a recordset variable.

You mentioned the ControlName_Aft erUpdate() that's all..

I wouldn't use it!~)

[NeoPa]

I do apologise. You must be wondering what I'm smoking. I'm afraid I got confused as to what I was talking about.

Let me clarify that my original statement (post #8) was what I can stand by and the follow up (post #11) was wrong. After the initial logging of the value when the form is loaded, the .DefaultValue need only be changed when a value in that control is updated.

Sorry again for any confusion.