Canceling new record entry

[numbat]

I have created a form for entering new records with custom navigation and new record buttons. I have also created custom error messages for primary key and null value violations. My problem is how to allow the user to cancel a new entry if, for instance, enter a empty string in a primary key. At the moment the only way to cancel the operation is through the use of the undo current field/record arrow which I would like to avoid. Is there any other way to this?

Thanks.

 

[RoyVidar]

Depending on how you've implemented your data validation.

One trick would be to use whatever event you're using to trap for these errors to give the user the possibility of undoing the entry.

For instance, if you're using the form error event, here using the dupe pk as an example:

[tt]if dataerr=3022 then
if msgbox("dupe key, continue?",vbyesno)=vbno then
me.undo
else
me!somecontrol.setfocus
end if
end if[/tt]

- is this in the direction you need?

Roy-Vidar

 

[numbat]

RoyVidar

Thanks for your reply. This was the approach I had been taking although I hadn't used the undo command because I wanted the data in the other controls kept. So the problem I am encountering arises, for example, if the user clicks the save button, are sent back to the control and they delete the primary key value in the text box and then try to leave the control with it empty. They will then get an error message saying a null value is not allowed and they are forced to enter a value. I guess I am looking for a way to cancel or delete the record after any data errors. I don't think I have a very good idea of how access manages inserting the record. It seems as if a record is created even though there is an error, but there is no way to delete that record through code once the error has occurred.

 

[RoyVidar]

I'm somewhat at loss at what you're saying "I guess I am looking for a way to cancel or delete the record after any data errors.", the me.undo does that. Either "deletes" a new unsaved record, or resets a previously saved record to the state it was prior to editing. Doesn't it? Are the constraints you're checking not on table level? Couldn't you try just testing for me.newrecord, then undo if it is, or delete if it isn't?

But then you also state "...because I wanted the data in the other controls kept"

You could perhaps take a look at the before update event of the form. The event fires whenever an attempt to save is performed, and you can do the datavalidation there. In my little experience, I don't think Access saves a new record if it doesn't qualify according to table level constraints.

To use the before update, for instance something like this to check the PK, here using the cancel arguement, which brings focus back to the record for further editing (alter to fit your requirements):

[tt]if len(trim$(me!txtPK & vbnullstring))=0 then
msgbox "fill in"
cancel=true
else
if not isnull(dlookup("pkfield","yourtable","pkfield=" & me!txtPK)) then
msgbox "pk exists"
cancel=true
endif
endif[/tt]

Roy-Vidar

 

[numbat]

RoyVidar,

You are correct that 'me.undo' will clear the form and hence the record in the case of a new record. My goal is to send the user back to the control that is causing the data validation error and give them the opportunity to change the value. I have an undo button, and if the user simply hits that, the form will reset without a problem. If however the user then deletes the text in the PK control, leaving an empty text box and then tries to undo the operation I will get a form error for the null value. At this point the undo button doesn't work and the only way out is the undo arrow on the default button bar.

I will take a look implementing the code at the before update event.

Thanks for your help.

 

[mych]

I tend to create my forms with Temporary holders for data for example a name and address form...

I would have a number of txt boxes coresponding to fields in my table. These fields would be hidden on my form. eg txtTempName, txtTempAddress, txtTempPostCode

The user would enter the data. In the On Lost Focus I may have some formatting eg txtTempName = UCase(txtTempName)

Also on the form I would have two buttons Cancel and Enter. The Cancel button would just clear all the temp holders. The Enter button would do validation and then store the value in the corresponding Table Field eg

txtName = txtTempName
txtAddress = txtTempAddress
etc

If there are any validation errors I transfere focus to that field after displaying an appropriate error message.

HTH

Mych

 

[numbat]

Thanks for the tips. I think I will try something along those lines or perhaps use an unbound form. My current strategy is simply not working.