Update ID autonumber only after the Record has saved?

[Torinjr]

I have a from called Add Data that creates records for my Data table. I have got the form to a point where the inputted data is saved on a button click and I want to now make the form so it sets certain text boxes and combo boxes to Null after the record saves. I have managed to do this with the Macro builder using the command SetValue as "Null" for each text box and combo box i want changed for the next record. However, this does not work for the autonumber field ID (primary key). The only way i can update this text box is by clicking the 'Refresh All' button in the ribbon, which then clears the text boxes that i wanted to remain.



Is there a command in the macro builder that will update only the ID textbox for my next record without clearing certain text/combo boxes, or is there another way around this?

I am new to Access and have no experience with VBA, so id prefer a solution that advoids VBA coding.

Thanks

[ItsMe]

You should not need to do any of that.

You can set default values of controls from the properties sheet. If the control is bound to a field in a table, it will use the default value set at the table level (as long as you leave the default value property of the control blank).

As for the autonumber, just like default values, it will not commit to the table until after data is entered and saved. In other words, do nothing and all fields will remain Null, even if their default value is 100, True, Date(), etc. You will see the value(s) but other users who look in the table will not see it until the commit.

[Torinjr]

After the record saves all the items return to their default value, or null like you have said. Although, I want some text/combo boxes not to reset to their default value see below.

My Form looks like this:

ID: (New) -Autonumber field.
Tender Name: -Combobox that i want to stay same.
Item Type: -Combobox that i want to stay same.
Item Description: -Textbox that i want to set to empty/null for new the Item.
Quantity: -Textbox that i want to set to empty/null for new the Item.

So when i save and go to the next record, how do I get the Tender Name and Item Type to remain with the same value as what they were for the last record?

[ItsMe]

...After the record saves all the items return to their default value,...

That is not exactly what I was trying to say. When you move to a new record, all of the fields in the new record will remain Null until you add some data to a field, regardless of the default value. This includes the Autonumber field. You may see something (a value) in a control, but that is not what the value of the field in the table is. The record does not exist until you start to enter data.

Understand this, and you will understand it is frivolous to attempt to adjust the Autonumber field. This is aside from the fact that you cannot edit the Autonumber field.

As for moving to a new record and assign a value to the combo. The only way I know is to use VBA. There may be a way to do it via a Macro, I do not know.

[Missinglinq]

Also understand that you do not need a 'save' button to commit the entered data to your table; moving to a new record or closing the form will do this automatically. As to getting the Tender Name and Item Type to remain with the same value as what they were for the last record, that's answered in your post here:

https://www.accessforums.net/forms/g...orm-52168.html

I know it can seem scary, but in order to do any serious Access development, you're going to have to bite the bullet and learn VBA code.

Linq ;0>

[Rawb]

It sounds like you may be using an Unbound Form. If that is the case, I'd strongly suggest moving it to a Bound Form.

Bound Forms will be able to pull default values/etc. directly from the Table: The Form will show all blanks until something is actually entered, at which time it will provide any default values specified in the Table itself. Bound Forms don't require the user to click anything in order to save the data either. As soon as they close the Form, or start working on another new Record, Access will save it automatically.

And this will solve your Autonumber issue - the Autonumber field will be empty until the user starts entering data and then will immediately populate with the next available number.

[Torinjr]

Thanks for the help, I do need to learn VBA - my only experience with coding is a small amount of matlab exposure, so i struggle but understand the general concept. I think the form is bound.

Cheers, Torin