Macro Validation Help 1

[ajhts]

I have a data entry form that is setup as a datasheet view. Works perfect. The problem is now they want is dummy proof. Here is what the datasheet view looks like.

Month Day Year CPT DirectMinutes Units
8 1 2004 97001 10 1
8 1 2004 97110 15 1
8 1 2004 97101 10 1

Here is the problem. I wrote a macro with a complicated formula to validate the TOTAL number of directminutes and the TOTAL number of units for the day. I run my macro as an event procedure on close, works great to tell them they have allocated their units wrong and to make a change.

The only way that I knew how to do this was to have a form footer on the datasheet view that is not visible and have it sum the directminutes and units. The problem is when it runs on close and prompts them that they have a problem, when they click ok it exits anyway. I would like it to stay within that screen so they can correct things. Hoping this is something easy!

AJ

 

[dpav29]

I've made something similar work for me.. .

Instead of the close event, put a button on the form and put the code behind the onclick event. Make an if statement something like:

If (yourcalculation) Then
If MsgBox("Go fix your mistake",vbOK + vbQuestion, "Oops")
me.oneofyourfields.setFocus
End if
Elseif MsgBox("Are you sure you want to save this?",vbYesNo + vbQuestion, "Save?") = vbYes Then
frmSave = True
DoCmd.close
End If

This is from memory, but I think it's pretty close. . .it will send them back to the form if there's an error; if no error, confirm that they are done and close it.

 

[mustangcoupe]

move your code to the unload event for the form and add a if then statement
the unload event runs before the on close event for the form

Private Sub Form_Unload(Cancel As Integer)
if (your calculation) then 
'if your calculation is true put code here
else
'put message box here with information about there error
cancel=true
'cancel will halt the closing of the form
end if
End Sub

 

[ajhts]

This may be a really dumb question but since I am using datasheet view for the data entry, how do you place a button on the form? I tried in the form footer, but again it doesn't show up in the datasheet view? Sorry, still kind of new with some of this.

AJ

 

[mustangcoupe]

I dont beleive you can in datasheet view.

TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[TheAceMan1]

How are ya ajhts . . . . .

Your problem is the [blue]Close Event[/blue] can't be canceled (does not use the Cancel Arguement).

[blue]mustangcoupe[/blue] is right-on with the [blue]On UnLoad Event (uses the Cancel Arguement).[/blue] Setting the [blue]Cancel Arguement[/blue] to [blue]True[/blue] [purple]cancels the event and rolls back any saving![/purple]. just transfer your code there . . . . Now that button can perform an alternate desired function! . . . Noy trying to be demeaning with [blue]dpav29[/blue], but this is what I woild do . . . ..

See Ya! . . . . . .

 

[dpav29]

AceMan is "Da Man!" . . .I'm giving him the star for schooling me on this. It really helped me with my project. . . I hope you (ajhts) got it done!

Dave

 

[ajhts]

okay, sorry I took so long getting back, appreciate the help. I moved my code to the ON UNLOAD and it still didnt' work. Any idea's? It works ON CLOSE, however it closes the form anyway and they can't correct their data.
Maybe I am doing something wrong, I just simply moved my macro to ON UNLOAD instead of ON CLOSE. Do I need to have a go to control command in their?

AJ

 

[TheAceMan1]

ajhts . . . . .

Really! . . . . . [blue]post the code![/blue]

See Ya! . . . . . .

 

[ajhts]

Okay, I found out what the problem is. Your advice works perfect on my datasheet form. The problem is my master form uses this datasheet form as a subform, so when I update the records and close it, my macro doesn't work properly. If I only open up the datasheet form by itself and update records, it works perfect.

In saying this, do you have any idea's. I really need this to be a subform for transactions.

Thanks
AJ

 

[TheAceMan1]

ajhts . . . .

As a subform, [purple]referencing[/purple] the subform and its objects changes. Post the code as you have it now for modification . . .

See Ya! . . . . . .