Adding a new record in form, can't trap errors

[hermanko]

Hi,

I have a form used to enter data into an existing table. I set the form
properties for AllowAdditions to Yes and Data Entry to Yes. As well,
the Cycle is set to NO, because I want to restrict the user to adding
just 1 record at most before having to close the form. I do not want
multiple record entries unless the form is closed and then reloaded
thru a menu option.

Before limiting to just one entry, I was able to trap the two major
error events: 1) null value for a primary key field, and 2) a duplicate
primary key field entry.

Now, it doesn't capture those errors even tho I have the code written.
My guess is that it has something to do with the fact that it cannot go
to the next record in order to trigger the error? Before, my form was
able to let the user to tab thru the fields and then after the last
field, it would trigger an error (if any of the two above situations
were true) because it was tabbing to the next record, but now (with
Cycle set to NO) after the last field, a tab would just go back to the
first field of that same record.

I hope that I have made my problem clear I have a "SAVE" button on
the form where I want to be able to trap those errors but right now
when i click that button, the form closes without error, but the table
does not update, which leads me to think it "is" an error because the
table remains the same as before. This can be confusing to the user if
he inputs by accident a duplicate primary key and saves thinking it's
updated but in reality nothing changes.

the code i have is:

Private Sub cmd_saveexit_Click()
On Error GoTo MyError

If MsgBox("Save new record and exit form?", vbOKCancel +
vbDefaultButton1) = vbOK Then
OK = True
DoCmd.Close acForm, "Add Document", acSaveYes
DoCmd.OpenForm "Admin Menu"
Else
Me![File Code].SetFocus
End If

ExitMyError:
Exit Sub

MyError:
MsgBox Err.Description
Resume ExitMyError

End Sub


Any help would be greatly appreciated!

 

[Michel Walsh]

Hi,


You can more easily manage these two problems having a real primary key in
the TABLE DESIGN. If done there, a primary key cannot have a null for value,
neither can have a duplicated value. If you try to save a record with a
null, or already has the proposed value, within a form the form OnError
event will fire:

===============
Private Sub Form_Error(DataErr As Integer, Response As Integer)
' add you code here to handle the situation
End Sub
===============


Doing so, you will increase productivity (no need to close a form each
time), and security because any record appended to the table, through ANY
interface (from your form AS WELL AS from seeing the table directly, or from
any other possible mean to reach the data), once saved in the table, will be
so that there will be no null, and no duplicated value, in the primary key.
Define the primary key in the TABLE DESIGN.


To handle the situation in the Form_Error event, examine the DataErr value
you get when you try to append a record with a null value for the primary
key, and do the same when you try to append with a value already in the
table, or for any other error that can occur outside your VBA code itself.
It is then a matter to handle each of these possible cases of error you want
to handle, which can be to issue a Message to the end user, or to correct
the invalid data. The Response argument is to be set to tell to Access what
to do next, as given in the documentation.

Hoping it may help,
Vanderghast, Access MVP

Hi,

I have a form used to enter data into an existing table. I set the form
properties for AllowAdditions to Yes and Data Entry to Yes. As well,
the Cycle is set to NO, because I want to restrict the user to adding
just 1 record at most before having to close the form. I do not want
multiple record entries unless the form is closed and then reloaded
thru a menu option.

Before limiting to just one entry, I was able to trap the two major
error events: 1) null value for a primary key field, and 2) a duplicate
primary key field entry.

Now, it doesn't capture those errors even tho I have the code written.
My guess is that it has something to do with the fact that it cannot go
to the next record in order to trigger the error? Before, my form was
able to let the user to tab thru the fields and then after the last
field, it would trigger an error (if any of the two above situations
were true) because it was tabbing to the next record, but now (with
Cycle set to NO) after the last field, a tab would just go back to the
first field of that same record.

I hope that I have made my problem clear I have a "SAVE" button on
the form where I want to be able to trap those errors but right now
when i click that button, the form closes without error, but the table
does not update, which leads me to think it "is" an error because the
table remains the same as before. This can be confusing to the user if
he inputs by accident a duplicate primary key and saves thinking it's
updated but in reality nothing changes.

the code i have is:

Private Sub cmd_saveexit_Click()
On Error GoTo MyError

If MsgBox("Save new record and exit form?", vbOKCancel +
vbDefaultButton1) = vbOK Then
OK = True
DoCmd.Close acForm, "Add Document", acSaveYes
DoCmd.OpenForm "Admin Menu"
Else
Me![File Code].SetFocus
End If

ExitMyError:
Exit Sub

MyError:
MsgBox Err.Description
Resume ExitMyError

End Sub


Any help would be greatly appreciated!