You have code in the application that is dirtying the new record. That is what is causing the problem. NEVER dirty a new record before the user dirties it and you will not accidentally create bogus records. If you can't figure out what code is causing the problem, post the database and tell us how to reproduce the problem.
Everything else works fine, my button to create new entries works perfectly. It's just this f5 business that seems to sidestep my button and the logic behind it and create the record itself.
I didn't know about f5 until my colleagued pressed it accidentally to reload the form and it entered an incomplete entry into the database. Is there no way to easily disable the f5 refresh?
The difference between my solution and arnelgp's is that mine will only affect the forms where you insert the code, whereas his will affect the entire application.
The difference between my solution and arnelgp's is that mine will only affect the forms where you insert the code, whereas his will affect the entire application.
As there are many times when you are likely to want the Refresh capability, I would only disable it where absolutely necessary.
Therefore I recommend doing so using Frothy's approach
I'll amplify this. Using F5 cannot create a new record because all it does is a REFRESH. However, code in your form can do this in conjunction with F5 because F5 seeks to make the form "not dirty." The only two ways for Access to do this involve either triggering a SAVE (which is the default, because Access wants to SAVE rather than lose data) or triggering an UNDO (which isn't the default).
So the next question is, what makes the form dirty? To which the answer is, anything that runs automatically and can modify the content of any control. The answer is, two main places. If you have timer code running and that code could modify any controls, that's one possible culprit. The other likely candidate is the Form_Current code, which would be run after the F5 refresh occurs - because at that moment, the form has again become "current."
As to why it is a "new record" rather than updating the old record would depend on form settings and database settings.
This is my database, I have removed the Macro to disable F5 and left in the macro changes that check if the textboxes are empty once the add record button is pressed.
To reproduce the issue simply enter data into one of the textboxes in the form and press f5. This creates a record in the database. I don't want the fields to be required in the database, as this tends to lock the form when you try to enter text and then make it blank and try to leave the form or click into another textbox.
1. Your validation code is in the wrong event. the validation code belongs in the Form's BeforeUpdate event. Your Add button should simply save the record. All the validation needs to be moved. If a field is missing, you would cancel the event and exit. For Ixample
Code:
If Me.Operating_system_combobox & "" = "" Then
Msgbox "Operating System is required.", vbOKOnly
Me.Operating_system_combobox.SetFocus
Cancel = True
Exit Sub
End If
As the code works now, simply closing the form will save an empty record so disabling F5 isn't going to solve the problem. To solve the problem, you need to understand how a bound form works so that you use the correct events to control actions.
2. There is no point in the code that calculates Next_ID since it is never saved and you are using an autonumber anyway. If you were to actually need this code, the AfterUpdate event and the Load events are the WRONG place to do it. You would generate the number in the BeforeInsert event. That way you generate a new number for each record but only when you need it because the user has started entering data on a new record.
3. The .Value property is the default property for controls and therefor can be omitted so you would use Me.mycontrol rather than Me.mycontrol.value. The .text property should only be used when the control has the focus so it would be used in the change event to capture keystrokes as they are entered.
4. The Add button, if you still want it (I would relabel it as save), should always be active. I only use the save button because users like to press something to take action.
You can force the user to save with your save button but I never do. I let Access do it's thing. My validation code is in the correct event (the Form's BeforeUpdate event) and so on my forms, partial records never get saved regardless of what causes the record to be saved.
5. If you like having the Save button to give the user the warm fuzzys, then you might also want a Cancel button.
thank you for having a look at my database and for the informative response, I really appreciate that
I would still want the save and maybe a cancel button but i'm unsure on how to add the save button since when I used:
Code:
Private Sub Save_Record_Button_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub
I get an error "3021, no current record". Wouldn't I just need this to update the form for the validation code to be run and for the record to be created if the validation is passed?
When you have a properly bound form, there are very few times to NOT have a current record. One occurs if you delete the current record somehow and then your form fails to navigate correctly because you deleted the only extant record in the recordset. There are other rare cases, almost all of them (I DID say "almost") caused by trying to do too much or trying to do something in the wrong event.
If your form is used exclusively for new data entry, then your Form_Load (for the first time you open the form) and the After_Update (right after saving the previous record of intrest) routines might be the right place create a new, empty record for you. If your form can also be used for editing existing records, then a "NEW" button would be appropriate, since you like buttons. There is a command button wizard that will build the scaffolding for the NEW function. I'm with Pat on the idea of using an UNDO or CANCEL button. And yes, there is also a command button wizard for THAT function.