Saving Records

Hi all,

I have a form, which when opened, opens as a filtered form. I use this form as an imput form. I also have a save button which saves the record into the table.

I was wondering what additional VBA coding i would have to attach to the save routine so that the record is saved, disappears from the form and the next empty record (ready for input) appears in the same filtered form.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
after saving the record, try


don't know the status of the form at this point, so you may also need


and this (most probaly not)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
<I have a form, which when opened, opens as a filtered form. I use this form as an imput form.>
Normally this is not the case because this may cause confusion if the record you are entering is not in the filtered set.
Can you explain the logic behind this?

<I was wondering what additional VBA coding i would have to attach to the save routine so that the record is saved>
This is done automatically, whether or not the data entry property is set.

FWIW, doing things like this tend to makes the interface more complex that it may need to be.
Anytime you "Fight" Access' default functionality, you sometimes end up tying yourself in knots.

If you want total control over the way data is entered, then go with an unbound form.


PipMicAuthor Commented:

just realised that its early morning in your place, so thanks for your comments.

Regards the logic:

I have a user who is entering data and what I have is an input form in "filtered mode". In other words, the user can only see an empty form. I have had unfiltered forms in the past when users have accidently over written in existing data.

Therefore the reason for the unfiltered form is to avoid accidental overwrites.

I would be grateful to know if there is a better or alternate way of doing this.

I am always willing to learn.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

PipMicAuthor Commented:
Hi capricorn,

Tried your code and it didnt work.

Attached it to the save button but no joy!
Attached it to form AfterUpdate event but no joy!

Grateful for additional help.
Jeffrey CoachmanMIS LiasonCommented:
<I have a user who is entering data and what I have is an input form in "filtered mode". >
Then we seem to have a terminology issue here, because that' is not "Filtered", ...that is "Data Entry mode".

"Filtering" says that you only want to see a subset of the data:
Only USA Orders
Only Orders from Customer 123
Only NY Orders from Customer 123
All Order except Bulgaria
Orders between Jan1 2011 and Feb 15 2011.

What you are asking for is simple called "Data Entry Mode"
Where a user can only see the records they are entering

You can do this by setting the Data Entry property to Yes as capricorn1 stated.

So points to capricrn1

PipMicAuthor Commented:


Perhaps I'm using the idea of Filtering incorrectly, however i thought this would be a cute way of preventing the user to accidentally ovewrite data.

The basic problem I was having was that whilst the save button did indeed save the record, and the form then returned to entry "mode", i.e. ready for input, if i clicked on my delete button it would tell me that I was going to delete the record, even though it did not appear on screen.

My recourse is now to remove the delete button  and create a button which will reset the fields to null, so that in the event that the user decides to forego the data he/she is entering he/she can reset the fields to null.

My apologies if my terminology is incorrect.
Rey Obrero (Capricorn1)Commented:
<Tried your code and it didnt work.>

if you will read the entire post at http:#a:35323553, there are some unknown factors, so the first code is not an outright solution.

if you want a cancel button, you can place this codes in the click event


that will clear the form.
PipMicAuthor Commented:
hi Capricorn,

Used: docmd.gotorecord,,acnewrec

and then created a reset button and it works.
PipMicAuthor Commented:
Provided good advice
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.