473,320 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How do i check a record to be complete, then delete or close

What i want to do is make sure that a record has a certain field completed before closing, but i cant really do it by setting it as required because i have multiple me.refresh in it. So I did this

Private Sub Form_Unload(Cancel As Integer)

If IsNull([Combo24]) Then
If MsgBox("The inspector box has to be filled in, or the record will be deleted. Do you want to continue closing and delete the record?", vbYesNo) = vbNo Then
Cancel = True
Combo60.SetFocus
Me.Refresh
Else

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
Else
Exit Sub
End Sub

The problem with this is..... If i just open the form to look at it, and then close the form it prompts me to see if I want to delete the record, but there is no record to delete so I get an error stating that i need to debug, yada yada yada.
Aug 22 '08 #1
8 1429
missinglinq
3,532 Expert 2GB
The problem with this is..... If i just open the form to look at it, and then close the form it prompts me to see if I want to delete the record, but there is no record to delete so I get an error stating that i need to debug, yada yada yada.
The reason you get the error when just opening and closing the form is that you placed your code in the Form_Unload event, which fires every time you close the form!

This type of validation code needs to go in the Form_BeforeUpdate event. You also have a number of other problems. I'll come back when I have more time and explain these to you, but for now, this code will do what you want done:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.NewRecord Then
  3.   If IsNull([Combo24]) Then
  4.     If MsgBox("The inspector box has to be filled in, or the record will be deleted. Do you want to continue closing and delete the record?", vbYesNo) = vbNo Then
  5.       Cancel = True
  6.       Combo24.SetFocus
  7.     Else
  8.       Me.Undo
  9.     End If
  10.   End If
  11. End If
  12. End Sub
Linq ;0)>
Aug 22 '08 #2
missinglinq
3,532 Expert 2GB
Private Sub Form_Unload(Cancel As Integer)

If IsNull([Combo24]) Then
If MsgBox("The inspector box has to be filled in, or the record will be deleted. Do you want to continue closing and delete the record?", vbYesNo) = vbNo Then
Cancel = True
Combo60.SetFocus
Me.Refresh
Else

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
Else
Exit Sub
End Sub
As I said previously, Form_Unload is the wrong event for this code. By the time this event fires you cannot change anything in the current record, it's already been saved.

After determining that Combo24 is Null

Combo60.SetFocus

you return focus to Combo60. I assumed this was a typo and changed it in my code, to return focus to the combobox that was empty..

I have no idea what the Me.Refresh is supposed to be doing. Me.Refresh should only be used in multi-user databases; it updates a given user's screen display to show changes that have been made to records by other users since the given user opened the form. It doesn't, by the way, show new records or deleted records when this is done by other users. Re-querying has to be done to reflect these changes.

You committed a Cardinal Sin with this line

DoCmd.SetWarnings False

in that you didn't, immediately after attempting to delete the record, set the warnings back to True! From this point forward in your database, no error messages would show, and this could be catastrophic! Always, always, always follow the code that would throw up the warning you're suppressing with

DoCmd.SetWarnings True

These lines

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord


I suspect are going to error out, even in the correct event, because you're trying to select and delete a record that hasn't been saved yet!

To dump a new record that hasn't been save yet, you use this single line

Me.UnDo

You final Else is unnecessary, as is the Exit Sub line immediately before the End Sub, and you have no End If following the Else, which would also have popped an error. You have to be very careful with the placing of the End Ifs when nesting If ...Then... End Ifs.

In your first thread on this forum you said "One day i will get better at this" and you will! Contrary to popular belief, especially among suits, Access is a huge and complex application! I've been doing this for years, as have many of our experts and moderators here, and we're all still learning!

Linq ;0)>
Aug 22 '08 #3
Does the me.refresh save the current form? The only reason that i am using it is because when i setfocus, the cursor wont actually move until the me.refresh occurs. I tried it without it, and the cursor did not actually move, when i put the me.refresh in there, it did. I am also populating other fields with me.refresh (using earlier fields in the same record to get their information). If there is another way of doing this, please inform me. I have never had any type of training in this. I was completely honest when i stated that i haved learned everything from the internet, maybe others bad habits are sinking in. Thanks for the tip on the setwarnings, i didn't think about the major impact.

I tried this code and it worked correctly, but while trying to dummy proof it is when i noticed that i couldn't actually leave the database if something wasn't entered, since there was nothing to delete.

If the unload is saving the record, then wouldn't the select and delete record actually work, as long as there was data entered?

Dont i have to use the afterupdate(), on an actual field? What if nothing has been entered, wouldn't the afterupdate not fire?

I do appreciate your help linq.
Aug 22 '08 #4
Linq, if you dont mind i would like to get the answers from the questions in my previous post, but I decided to just make the other fields on the form disabled until the inspector name is entered. That way it HAS to be first.
Aug 22 '08 #5
missinglinq
3,532 Expert 2GB
Does the me.refresh save the current form? The only reason that i am using it is because when i setfocus, the cursor wont actually move until the me.refresh occurs
.

No, Me.Refresh doesn't "save a form." Nor does it save a record. It does just what I described above. When trying to set focus to a control that can receive focus, such as a textbox, doesn't work, the reason usually is that Access considers the control to already have focus! I know this sounds strange, but Access acts very strange, at times.One would think that a control no longer had focus in its AfterUpdate event or in its LostFocus event, but Access says that it does, although the cursor no longer appears in the textbox! Whenever this happens, the workaround is to set control to another control, then set it back to the intended control.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ControlOne_AfterUpdate()
  2.   Me.ControlOne.SetFocus
  3. End Sub
won't work, but

Expand|Select|Wrap|Line Numbers
  1. Private Sub ControlOne_AfterUpdate()
  2.  Me.ControlTwo.SetFocus
  3.  Me.ControlOne.SetFocus
  4. End Sub
will! This kind of stuff is why we're all still learning!

I am also populating other fields with me.refresh (using earlier fields in the same record to get their information).
Sorry, but I have absolutely no idea what you mean by this statement! You'll have to explain this again.

I tried this code and it worked correctly, but while trying to dummy proof it is when i noticed that i couldn't actually leave the database if something wasn't entered, since there was nothing to delete.
If "something wasn't entered" and you placed the code I gave you in the Form's BeforeUpdate event, as instructed, then the code will not run, so it cannot possibly prevent you from exiting the form! My guess would be that you've left some of your previous, incorrect code, in place.

If the unload is saving the record, then wouldn't the select and delete record actually work, as long as there was data entered?
The Form_Unload is not "saving" the record. When you click to close a form, the current record is saved before Access gets to the Form_Unload event.

Dont i have to use the afterupdate(), on an actual field? What if nothing has been entered, wouldn't the afterupdate not fire?
Since the AfterUpdate event, either the form's or a control's, hasn't been mentioned in this thread until this current post, I'm confused as to why you ask the question! But to answer it, yes, if nothing has been entered in a control, or selected from a combobox or listbox control, it's AfterUpdate event will not fire. It also will not fire if data has been entered into it thru code. It will only fire if you physically enter data into it, or copy and paste data into it. A control's AfterUpdate event is generally used if you want something done based on the data that has just been entered in the control An example woud be:.

Expand|Select|Wrap|Line Numbers
  1. Private Sub EMSControl_AfterUpdate()
  2.   If Me.EMSControl = "Fire" Then
  3.      Me.Mascot = "Smokey the Bear"
  4.   Else If Me.EMSControl = "Police" Then  
  5.      Me.Mascot = "McGruff the Crime Dog"
  6.   End If
  7. End Sub
While there is a Form_AfterUpdate event, it is best avoided. People sometimes inadvertently place code here to change something in the record, which in turn causes the Form_BeforeUpdate to fire again, which causes the Form_AfterUpdate to fire again...which places you in an unending loop, preventing you from exiting the form! From your statement
I tried this code and it worked correctly, but while trying to dummy proof it is when i noticed that i couldn't actually leave the database if something wasn't entered, since there was nothing to delete.
it's possible that you have some code in the Form_AfterUpdate event that's causing this.

Linq ;0)>
Aug 22 '08 #6
Sorry, i just realized that your comment said to use BeforeUpdate(), not after.
Aug 22 '08 #7
OK let me explain how i got the Me.Refresh

from one of my previous threads, about updating a field from another field (part number, and description if you remember), on another table. I got it to work with a subfrorm that is hidden on the table. When a person enters the part number into the visible field it looks up the partnumber from the invisible table, after that point i had (in code) the visible description made equal to the invisible description, but it would not actually change until the record was saved. So away on the internet i went to look for an answer, and i came across the Me.Refresh. So on the afterupdate of the description field, i did a me.refresh and the information loaded in the screen.

I am sure that someone on here would say that i was supposed to do it with a dlookup, or a lookup, or a query, but i have not found very good information on these, and beleive me I have searched. If anyone here has a decent online site that i can learn some query 'stuff' from i would read it and try to learn. Until then i try to hammer it out. I luck out, get a peice of code that works, though not very efficiently, and i use it. Beleive it or not, even with all of my posts lately, I really do try to get the information myself and not bother any of you with it. The only reason that i ask so many questions is because i am really trying to learn it, not just use the code and never wonder why it works.

btw thanks for the tip on the setfocus, i will use it.
Aug 22 '08 #8
missinglinq
3,532 Expert 2GB
Sorry, i just realized that your comment said to use BeforeUpdate(), not after.
I know that you're working diligently at learning Access/VBA, and one of the single most important things you need to learn is to pay attention to detail! This is absolutely crucial in order to be successful, not only in Access/VBA, but in any programming language! There is simply no substitute for accuracy! Spelling does count! Punctuation does count!

Placing the code I gave you in the Form_AfterUpdate instead of in the Form_BeforeUpdate event could have caused the problems you encountered, wherein you couldn't close the form.

Linq ;0)>
Aug 23 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Uwe Range | last post by:
Hi to all, I am displaying a list of records in a subform which is embedded in a popup main form (in order to ensure that users close the form when leaving it). It seems to be impossible to...
4
by: Susan Bricker | last post by:
I have a command button on a form that is supposed to Delete the record being displayed. The record is displayed one to a form. The form is not a Pop-Up nor is it Modal. Tracing the btnDelete...
16
by: MartinR | last post by:
I would like to know the code that i should use to delete a record without the message box saying "You are about to delete a record, are you sure..." poping up. At the moment i am using the...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
6
by: jpatchak | last post by:
Hello, I have a main form with one subform. I have a command button on the main form to delete the current record. Below is my code. The tables on which the main form and subform are based...
4
by: Steven | last post by:
Hi, Would need some thought about using a button of the existing form to search record before deleting it. Any quick help is very appreciated. Steve
1
by: Sunray | last post by:
I have a form called the sales form and i have 2 sets of listboxes So what happens is. i add items form the bottom set of list boxes which are bound to a data base to the top set of list boxes which...
5
by: agarwasa2008 | last post by:
Hi, I would like to delete a record based on a user entered string. Here are the details. I have a txtFind textbox. A string is entered by the user. Based on that string value it displays that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.