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

[crazyhouse]

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(Can cel 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.SetFocu s
Me.Refresh
Else

DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdDeleteReco rd
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.

[missinglinq]

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_BeforeUpda te 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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  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
      Combo24.SetFocus
    Else
      Me.Undo
    End If
  End If
End If
End Sub

Linq ;0)>

[missinglinq]

Private Sub Form_Unload(Can cel 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.SetFocu s
Me.Refresh
Else

DoCmd.SetWarnin gs False
DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdDeleteReco rd
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.SetWarnin gs 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.SetWarnin gs True

These lines

DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdDeleteReco rd

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)>

[crazyhouse]

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.

[crazyhouse]

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.

[missinglinq]

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.

Private Sub ControlOne_AfterUpdate()
  Me.ControlOne.SetFocus
End Sub

won't work, but

Private Sub ControlOne_AfterUpdate()
 Me.ControlTwo.SetFocus
 Me.ControlOne.SetFocus
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:.

Private Sub EMSControl_AfterUpdate()
  If Me.EMSControl = "Fire" Then
     Me.Mascot = "Smokey the Bear"
  Else If Me.EMSControl = "Police" Then  
     Me.Mascot = "McGruff the Crime Dog"
  End If
End Sub

While there is a Form_AfterUpdat e event, it is best avoided. People sometimes inadvertently place code here to change something in the record, which in turn causes the Form_BeforeUpda te to fire again, which causes the Form_AfterUpdat e 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_AfterUpdat e event that's causing this.

Linq ;0)>

[crazyhouse]

Sorry, i just realized that your comment said to use BeforeUpdate(), not after.

[crazyhouse]

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.

[missinglinq]

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_AfterUpdat e instead of in the Form_BeforeUpda te event could have caused the problems you encountered, wherein you couldn't close the form.

Linq ;0)>