Setting the default value to the last used value (1 Viewer)

Hi, I have a form with a number of text, date and combo boxes. We quite often add items that are very similar, so it would be useful if the values you've just entered were kept the same for the next item. Then you can just change the odd field that's different. Is there a way to achieve this? I've googled, but only found some horrendously complicated looking code!

Thanks.

in the form beforeupdate event put

ctrl1.defaultvalue=ctrl1
ctrl2.defaultvalue=ctrl2
etc

where ctrlx is the name of your control

CJ_London said:

in the form beforeupdate event put

ctrl1.defaultvalue=ctrl1
ctrl2.defaultvalue=ctrl2
etc

where ctrlx is the name of your control

Click to expand...

Thanks for the reply - however I seem to be doing something wrong. In the 'beforeupdate' for the text box, I put 'Stack.defaultvalue=Stack' (Stack' is the name of the text box). When I attempt to enter data, Access errors 'cannot find the object 'Stack''.

Did you select Event Procedure and paste it in the code window? And ensure that control name is correct. I would use the After Update event of each control.

vbaInet said:

Did you select Event Procedure and paste it in the code window? And ensure that control name is correct. I would use the After Update event of each control.

Click to expand...

I selected 'event procedure'. Access said I had to go to design view & set 'HasModule' to Yes, which I did. Then I clicked the ... next to Event Procedure & pasted the code in - I tried putting it in both BeforeEvent & AfterEvent. My VBA now looks like this:

Option Compare Database

Private Sub Stack_AfterUpdate()
Stack.DefaultValue = Stack
End Sub

Private Sub Stack_BeforeUpdate(Cancel As Integer)
Stack.DefaultValue = Stack
End Sub

The error message is gone, but it doesn't work. After typing in a value & pressing enter, it doesn't keep the new value. It always goes back to the same value (which comes from another record in the table - I'm not sure why it always defaults to taking the values from that record, but it always has ever since I made it)?

I said

in the form beforeupdate event put

Click to expand...

not control before update

CJ_London said:

I said

not control before update

Click to expand...

Apologies - I've now gone to design view, clicked on the form (the properties box says 'Selection type: Form') & gone to Event/Before update. I've clicked on the ..., chosen expression builder & typed in what you said. In the 'Before Update' box it now says '=[Stack].[DefaultValue]=[Stack]'.

Is that correct? It still doesn't work, the value defaults back to the same random previous value after entering a new record.

sounds like you are using macros - regret I don't use them. What version of Access are you using?

I would have expected the code to look like

Stack.DefaultValue = Stack

i.e. no square brackets

I would expect you in form design view to go to properties, click on the beforeupdate row then click on the three dot carat indicated here and chosen code builder which you appear to have been able to do before

So I've gone to design view, chosen selection type as 'form', gone to event/before update and clicked on the .... I've chosen code builder, which takes me into VBA. I've added the line you said in between the existing code, so that it says this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Stack.DefaultValue = Stack
End Sub

I exit my way out, and 'before update' now says [Event Procedure].

Now it gets slightly odd - I go back to 'Form View' and the form shows the data from the previously entered record (which is what I want). I add a new record, press enter, however now the data defaults back to the random earlier record that I don't want. If I go into design view, then back into form view, I get the data from the last entered record (which is what I want). However, again, after entering a new record & pressing enter, I get the 'wrong' defaults again.

So basically going into design view & back into form view gives me the result I want. However when entering data, I don't get the result I want.

I'm on Access 2013.

the code does not save the changed default in the form design which is why you are seeing an 'old' value so edit the form properties to remove the default.

If you are using a datasheet or continuous form, what happens when you start to enter data in a new record is a new 'new' row is created as soon as you hit the first character to be entered (it happens in a single form as well, but you just can't see it). Any default values will show in this 'new' row as previously specified and are only updated when the event the code is in is triggered - this is usually when the form is updated - so having entered your data and updated, the defaults are updated at that point.

To get closer to your expectations and bring the update of the defaultvalue to the earliest point, you need to use the control change event - but the code is slightly different in that you need to refer to the text property of the control since that is what is being changed.

Stack.DefaultValue = "'" & Stack.Text & "'"

What you will then see is as soon as you hit the first character, a new 'new' row is created and displays the character you have typed, type the next character and that will also be reflected in the default value

I've attached a small db to demonstrate

Be aware if you save the form design once you have run it, default values will be saved in the design and show the next time you open it. SO if you don't want this, ensure you remove any default value properties before saving the form

OK, I'm making progress. Putting that '.text' on the end, and using the 'on change' property of the particular field, seems to work. Where the text contains numerical data, I've put the code in as Field.DefaultValue = Field.Text. Where it contains text, I've put it in as Field.DefaultValue = "'" & Field.Text & "'".

That seems to give the desired results thanks!