Error in subform is not an error?

  • Thread starter Thread starter Jonathan Scott via AccessMonster.com
  • Start date Start date
J

Jonathan Scott via AccessMonster.com

I have a subform which I allow adding of records. The recordsource of the
subform is a joined query, where one of the tables has a unique constraint on
it. This relation is one-to-many.

When I add a record, part of which violates the constraint, I get an error.
Inside the Form_Error() subroutine I have it print out Err.Number. But it
comes out as '0'. Trying "Resume Next" gives me an error that I can only use
it when an error has occurred, yet I am INSIDE the Form_Error() subroutine!

In a CurrentDB.Execute, I can have the constraint violation not stop
execution by not using the option "dbFailOnError", and the rest of the record
can be added. However, in the subform, I appear to not have this option. Is
there a way I can get the record added? As long as the table with a unique
constraint in it is linked properly to the other table entry I am putting in,
I don't care if the constraint throws or not.

Any help would be appreciated,
Jonathan Scott
 
In Form_Error, the error number is passed in as an argument.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
So, there is no way to handle this correctly? I would like to enter data
inside a subform, but when part of that data violates a unique constraint, it
fails. If it violates the unique constraint, I would like to be able to then
use the ID for that existing record in my new record when it is input into
the DB.

I suppose I am forced to write some kind of input form all on my own in order
to implement such logic?

Jonathan Scott


Allen said:
In Form_Error, the error number is passed in as an argument.
I have a subform which I allow adding of records. The recordsource of the
subform is a joined query, where one of the tables has a unique constraint
Click to expand...
[quoted text clipped - 21 lines]
Any help would be appreciated,
Jonathan Scott
Click to expand...
Click to expand...
 
I'm also curious why it is that an element on a subform, marked as invisible,
never becomes invisible. Am I mistaken as to what that actually means? Is the
element hidden from somewhere/someone else instead?

Jonathan Scott


Jonathan said:
So, there is no way to handle this correctly? I would like to enter data
inside a subform, but when part of that data violates a unique constraint, it
fails. If it violates the unique constraint, I would like to be able to then
use the ID for that existing record in my new record when it is input into
the DB.

I suppose I am forced to write some kind of input form all on my own in order
to implement such logic?

Jonathan Scott
In Form_Error, the error number is passed in as an argument.
Click to expand...
[quoted text clipped - 3 lines]
Click to expand...
 
The constraints are enforced at the engine level (i.e. by JET or whatever
data engine your table use.)

When the form passes the data to the engine and the data is not accepted,
the form's Error event is triggered. That can happen because the data is of
the wrong type (e.g. text instead of number, or invalid date), or because a
validation rule is not met, or because a duplicate index is violated, or
because of a constraint in the table, or because the server went down, or
....

You can read the value of DataErr to understand why the engine rejected the
data, and you can set the value of Response to determine whether the user
sees the error message (acDataErrDisplay) or not (acDataErrContinue).

I can't recall trying to execute an append query during this event, which
sounds like what you are trying to do to solve the constraint violation. You
would need to try it, but you might get by with Response = acDataErrContinue
so that the event terminates, and then next attempt to write the data longer
triggers an error.

There might be a better way to solve the problem. For example, if the
constraint is the relation between the subform's table and the main form's
table, you could test Me.Parent.NewRecord in Form_BeforeInsert event of the
subform, and cancel the event if the main form is at a new record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
So, there is no way to handle this correctly? I would like to enter data
inside a subform, but when part of that data violates a unique constraint,
it
fails. If it violates the unique constraint, I would like to be able to
then
use the ID for that existing record in my new record when it is input into
the DB.

I suppose I am forced to write some kind of input form all on my own in
order
to implement such logic?

Jonathan Scott


Allen said:
In Form_Error, the error number is passed in as an argument.
I have a subform which I allow adding of records. The recordsource of the
subform is a joined query, where one of the tables has a unique
constraint
Click to expand...
[quoted text clipped - 21 lines]
Any help would be appreciated,
Jonathan Scott
Click to expand...
Click to expand...
Click to expand...
 
No idea what you are talking about here, Jonathan.

Element = control?
Invisible = setting Visible property?
Using code in an event? Action in a macro?
With error handling that is suppressing a message?
Because the control has focus?
Because the event is terminating before this line executes?
Because the event is not triggering?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
Thanks Allen,

I have decided not to let them insert directly into the subform. Everywhere I
am reading that the only thing that can be done is changing the error message.
The way you suggest sounds a bit hackish. But it turns out that importing
through an Excel spreadsheet would be easier for the users anyways.

I am working in a Japanese version of Access, so I don't really know what the
names are in English. "Element" meant a field on the subform. "Invisible"
meant unmarking the "visible" property.

Thank you for your time,
Jonathan Scott


Allen said:
The constraints are enforced at the engine level (i.e. by JET or whatever
data engine your table use.)

When the form passes the data to the engine and the data is not accepted,
the form's Error event is triggered. That can happen because the data is of
the wrong type (e.g. text instead of number, or invalid date), or because a
validation rule is not met, or because a duplicate index is violated, or
because of a constraint in the table, or because the server went down, or
...

You can read the value of DataErr to understand why the engine rejected the
data, and you can set the value of Response to determine whether the user
sees the error message (acDataErrDisplay) or not (acDataErrContinue).

I can't recall trying to execute an append query during this event, which
sounds like what you are trying to do to solve the constraint violation. You
would need to try it, but you might get by with Response = acDataErrContinue
so that the event terminates, and then next attempt to write the data longer
triggers an error.

There might be a better way to solve the problem. For example, if the
constraint is the relation between the subform's table and the main form's
table, you could test Me.Parent.NewRecord in Form_BeforeInsert event of the
subform, and cancel the event if the main form is at a new record.
So, there is no way to handle this correctly? I would like to enter data
inside a subform, but when part of that data violates a unique constraint,
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 
Back
Top