Round And Round The Error We Go (1 Viewer)

[CJBIRKIN]

Hello

I am trying ( in vain ) to create a password admin form for a database.

Background:

I have one table Tbl_LOGIN with

USER_ID (AUTONUMBER) PK
USER_NAME(TEXT)
USER_PASSWORD(TEXT)
USER_LEVEL (INT)

A SECOND TABLE

TBL_USER_LEVEL

LEVEL_ID (AUTONUMBER) PK
LEVEL_DESCRIPTION (TEXT)

USER_LEVEL AND LEVEL_ID are linked in a query

I have then built a form on the query and used a combobox on the form to allow the administrator to select the users level with TBL_USER_LEVEL. LEVEL_ID being stored in the Tbl_LOGIN.USER_LEVEL field for that record.

As i don't want anyone to have access to the passwords i also have a password reset button that simply defaults a new user- or someone who has lost there password -to a standard default which is then required to be changed on their next login. So far so good.

The problem is error trapping.

basically i don't want the administrator to create a duff user i.e one without some required data. I have been able to error trap most of the problems, however if they create an user without a level of access and then navigate (record select) the recordset i get the message

"The microsoft jet database engine cannot find a record in the table TBL_USER_LEVEL with key matching field(s) Tbl_login.User_level"

The problem occurs before the current event procedure but after the beforeupdate . It doesn't have an error code so i can't clear the error either. However the the form_error procedure is called.

How can i stop this from happening?


Cheers
Chris

 

[Fizzio]

Your easiest option is to make the fields required in the underlying table - this will then not allow an incomplete record to be submitted. Alternatively, you could validate the form before it is updated on the creation of a new user again so that all the fields are completed. This will be a better solution as you will then know that you have complete records for all users rather than allowing incomplete records then trying to trap the errors created by them.

 

[CJBIRKIN]

Hello

That is exactly what i've tried to do. The field was set to required and i got one set of messages saying cannot save record required field missing etc.

However my users are not clude up on microsoft error messaging so i was trying to be as bit more user friendly and do the validation on the form.

The problem is i can't trap this error. it doesn't even have an error code. I can do the validation on the exit button i.e if x is blank then do please fill in y

but this error occurs before the on current event

The only way round this would seem to be to code the recordset navigation but this seems to be just duplicating the functionality already provided.

thanks for your reply
Any ideas??

Chris

 

[Fizzio]

In your query, are you pulling the ID field from the lookup table? (tbl_user_level)? You should be pulling it from the tbl_login table, as this is the table you are trying to update. Also you should set up table level relationships between your tables with the linking ID. The lookup table does not need to be in the form's recordsource query as essentially it is it is only a lookup table. You can set this via a combobox, looking up the values from this table.
HTH

 

[CJBIRKIN]

Hello

I got rid of the link to the look up in the query and that fixed it. Cheers

Chris