Ok I have a form which is popped up as a modal form from a button. It allows someone to add an assignment for an employee.
The issue is that I only want to allow one record to be inserted by the form at a time (due to validations I need to make). I have a save button and it works well and saves the record, closes the form, and requeries the main form.
Now to the question - if the user uses the tab button, they can tab onto a new record, and the previously entered record is automatically saved, but it disappears because I have hid the navigation buttons.
I had code in the BeforeUpdate field that would save the record, pop up a message box and close the form. This worked perfect, except that when my Save button was clicked, the record tried to save twice (one for beforeupdate, one for onclick) and it hit the unique index.
So how would you handle this? I want to close the record after the first record is saved, but I cannot be sure if the user will use my save button, or be a jerk and tab through the fields.
You may want to be careful with this though. I had a similar problem once; when someone used the mouse wheel, they scrolled to the next record like the problem you had.
Here's how I solved it; Create a form exactly like the one you're using now, but don't have it linked to a table or anything, just a form with textboxes and what not. Then, on the button click event, do something like
Dim dbsEWORF As DAO.Database Set dbsEWORF = CurrentDb Dim rstTable As DAO.Recordset Set rstTable = dbsEWORF.OpenRecordset("Insert Table Name Here", dbOpenDynaset) With rstTable .AddNew !Field1 = Me.Txtbox1 !Field2 = Me.Txtbox2 'Repeat as necessary .Update End With
You don't have to worry about tabbing or mouse scrolling, and it has proven to be a much safer method for me.
When using a popup form that allows the user to add a single record, make the form unbound and update using recordsets. You then have complete control over what gets written when.
There are a number of ways to set it up - what ever works best in your situation. For example, utilize a hidden boolean that track's if the info has been saved. In the form's unload event, check the boolean.
I've had forms that not only add but also edit (looks like 2 different forms to the customer, but is 1 form underneath). Instead of the hidden boolean, I use a save button that starts disabled and as soon as something is changed, it is set enabled. Since Enabled is a Yes/No property, it can be used just like a boolean.
Loading and Saving info using recordsets can be a cinch if you plan carefully. Here's how I like to do it. I make a query with the specific fields I'm going to have on the form. I add the fields to the form, making sure I have every field in the query and I don't change the names (this is important). My recordset operations open the query and cycle through the fields collection (since the query field names match the form control names) to capture all the data. If I need to add a field, I simply add it to the query and the form alike and all the data handling procedures pick up on it automatically. The only watchout is that you catch the Autonumber fields in code since you can't write to those.