Cancel new record after half entering

[smercer]

Hi all

Is there a way to cancel a record after half entering even without the primary key? (with coding, not the undo button on tool bar)

I am having a problem with a unbound combo box and when the user starts typing in it, it will create a new record because its row source is sql code to look up a title in the same table as the subform it is based in.

When user leaves this combo box, I have code that will look to check to see if another title is already in the database and bring up a popup form that will ask user if that is the record they are typing in.

If they press F5 then the form they are typing in will change to the record mentioned via requery and this is where it wont work and is why I need to cancel record.

Thanks in advance

 

[llkhoutx]

Try

DoCmd.RunCommand acCmdUndo

 

[smercer]

Try

DoCmd.RunCommand acCmdUndo

hi llkhoutx,

I have not tested it yet but would that be a lot of coding? for example the user may do a few other things and the undo would not undo the right action?

Thanks for helping

 

[Simplycongy]

Could you not place the sql in an OK buitton instead of the code for the unbound combo box itself....

Alternatively, if when the user presses f5 and the record changes, if the record is not how you want it do you effectively want to delete the current record....

 

[smercer]

Could you not place the sql in an OK buitton instead of the code for the unbound combo box itself....

The person I am designing this for wants to be able to use the mouse as less as possible. the reason for this is if he has to type in something and then has to move his hand off the keyboard to the mouse to press a button and then back again to enter more records it is very inefficent for him.

Alternatively, if when the user presses f5 and the record changes, if the record is not how you want it do you effectively want to delete the current record....

When user leaves this combo box, I have code that will look to check to see if another title is already in the database and bring up a popup form that will ask user if that is the record they are typing in.

I do want it to delete the record that is currently being typed in not the record that is being looked up. I then what it to select the record that was queried.

Thanks for helping

 

[Simplycongy]

can you post a version as an attachment so we can have a sneaky peek???

 

[smercer]

can you post a version as an attachment so we can have a sneaky peek???

It is a bit big to post here. can email instead.

Would you like me to email it to you?

Thanks for helping

 

[smercer]

here is the coding:

Public Function FFiveKey()
'this  is where I want it to delete the record that was typed in

'tried the following to stop the validation error from appearing when the form is requeried.
'it did not work and i have resorted to taking out the validation rule so I can test it and it still does it anyway.

'Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!Category.ValidationRule = ""
'Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!Sub_Category.ValidationRule = ""


Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected.Form.RecordSource = "qry_Book_Entry_Book_Description_Select_record" 'this is so that the subform will requery with the record that the popup had selected.
Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected.Requery
DoCmd.OpenForm "frm_select_Book", , , , acFormReadOnly, acHidden 'this is so that the form does not have the focus when it is opened
'Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!Category.ValidationRule = "Not " & """sel"""
'Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!Sub_Category.ValidationRule = "Not " & """sel"""
    
End Function

 

[Simplycongy]

I've PM'd you my e-mail address... I'll try and take a look if you mail it to me

 

[smercer]

I've PM'd you my e-mail address... I'll try and take a look if you mail it to me

Has been sent.

 

[smercer]

Simplycongy: I tried emailing it to you but you have some kind of download restriction on the hotmail account (or is full).

File size when compressed: 1.13mb

 

[smercer]

Was sent to alternate email.

Thanks

 

[Simplycongy]

Ok.... I'm not sure this will work. It appears my copy of Access 2000 has corrupted somewhere along the way, and my Mums got my discs....

BUT i think that this VERY DIRTY method may work....

If i understand correctly, when you leave title and your pop up box appears, the module crashes as there isn't a primary key entered in the record (at least that was what was happening on my machine!!!). So, lets cheat force it through

insert these lines at the top of your FFiveKey() Sub...

Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Des cription_Record_selected!txt_Title_Lookup = Null

Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Des cription_Record_selected!ISBN_NUMBER = 9999999999

DoCmd.RunCommand SetWarnings False
DoCmd.RunCommand AcCmdDeleteRecord
DoCmd.RunCommand SetWarnings True

And please let me know how you get on.....

 

[smercer]

Ok.... I'm not sure this will work. It appears my copy of Access 2000 has corrupted somewhere along the way, and my Mums got my discs....

BUT i think that this VERY DIRTY method may work....

If i understand correctly, when you leave title and your pop up box appears, the module crashes as there isn't a primary key entered in the record (at least that was what was happening on my machine!!!). So, lets cheat force it through

insert these lines at the top of your FFiveKey() Sub...

Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Des cription_Record_selected!txt_Title_Lookup = Null

Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Des cription_Record_selected!ISBN_NUMBER = 9999999999

DoCmd.RunCommand SetWarnings False
DoCmd.RunCommand AcCmdDeleteRecord
DoCmd.RunCommand SetWarnings True

And please let me know how you get on.....

I used your coding and had to rearrange it a little, It now works, but there is still a problem.

This surprises me as much as it will surprise you; I get a warning dialog box popup and as you have already guessed It is not want is required. (if you don't believe me see attachment)

Here is the code now that I have altered it:

Public Function FFiveKey()
'this  is where I want it to delete the record that was typed in

'Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!txt_Title_Lookup = Null

Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected!ISBN_Number = 9999999999#

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected.Form.RecordSource = "qry_Book_Entry_Book_Description_Select_record" 'this is so that the subform will requery with the record that the popup had selected.
'DoCmd.GoToRecord , , acNewRec
Forms!frm_Book_Entry_form!sfrm_Book_Entry_Book_Description_Record_selected.Requery
DoCmd.OpenForm "frm_select_Book", , , , acFormReadOnly, acHidden 'this is so that the form does not have the focus when it is opened

End Function

When I put in the code, the lines that disable the warnings went red so I changed it to this.

I also found that the combo box was deleting the title out of the record, so now I think I will just set it to null when user goes to a new record. now that part works.

Thanks so much for geting me out of this hole I got stuck in.

 

[smercer]

Simplycongy: I changed the

DoCmd.RunCommand AcCmdDeleteRecord

with a delete query it works now

 

[Simplycongy]

Oh yeah... you don't need the runcommand syntax with setwarnings . Didn't write that bit in access... but i got the error message so i thought you'd want to disable it so added it in after. Your database kept on crashing my PC, really have to re-install access 2000 or go back to 97....

Its a pleasure to have been of some help. I haven't been using Access vba for long, but sometimes it just takes afresh set of eyes to solve the problem. I often bounce ideas/problems off of people at work who have no idea about coding or anything, just to get the old creative juices flowing. You'd be suprised how oten just explaining the problem to someone who doesn't understand it can help you solve it yourself!

 

[smercer]

Oh yeah... you don't need the runcommand syntax with setwarnings . Didn't write that bit in access... but i got the error message so i thought you'd want to disable it so added it in after. Your database kept on crashing my PC, really have to re-install access 2000 or go back to 97....

Its a pleasure to have been of some help. I haven't been using Access vba for long, but sometimes it just takes afresh set of eyes to solve the problem. I often bounce ideas/problems off of people at work who have no idea about coding or anything, just to get the old creative juices flowing. You'd be suprised how oten just explaining the problem to someone who doesn't understand it can help you solve it yourself!

It crashes on mine as well. dont know why, but it is extremely annoying. forturnunetly I have windows XP so I do not need to reset my computer each time.

I find it happens most when I click on the "New book Description" button. Some times it happens and sometimes it does not. we will soon see if it is the button itself because I have just made it into the function button.