okay - so I've inserted this slick little bit of code in a public function (not really ure how to link this - http://www.utteraccess.com/forum/Create-Pu...-t1980325.html)
thanks again Doug!
Public Sub Check_Required_Fields(FormReference As Form)
If IsNull(FormReference.Controls("Control1")) And FormReference.Name = "Form1" Then
MsgBox "Control1 is required."
FormReference.Controls("Control1").SetFocus
.....
ElseIf IsNull(FormReference.Controls("Control15")) And (FormReference.Name = "Form1" Or FormReference.Name = "Form2") Then
MsgBox "Control15 is required."
FormReference.Controls("Control15").SetFocus
End if
End Sub


so now, I want to call this event from my form's BeforeUpdate event.
there are two issues though...
1. if all of the required fields are populated, I need to be able to pass this info back to the form, so it will complete then next action
2. the next action may be one of three things - not simply docmd.close
I have three command buttons on the form - Add More of Same (this copies four of the control values to a new record), Add New (new record, all fields are blank), CLose form.
(the from Clos Button 'X' propert is No.)

What do I write in the BeforeUpdate event?
Do I call this code from each of the three buttons?

Hi

Change the Sub into a Function to return a value from it. In your case, a Boolean value should do what you want -

Public Function Check_Required_Fields(FormReference As Form) As Boolean
.
.
End Function

You can set the value of the function as you go throught the code.........

If IsNull(FormReference.Controls("Control1")) And FormReference.Name = "Form1" Then
MsgBox "Control1 is required."
FormReference.Controls("Control1").SetFocus
Check_Required_Fields = True
Exit Function
.....
ElseIf IsNull(FormReference.Controls("Control15")) And (FormReference.Name = "Form1" Or FormReference.Name = "Form2") Then
MsgBox "Control15 is required."
FormReference.Controls("Control15").SetFocus
Check_Required_Fields = True
Exit Function
End if
Check_Required_Fields = False

If you use the forms Before Update, you should just be able to do it all from there with

Cancel = Check_Required_Fields(Me)

**untested!!

I still dont understand what to put in the Form_BeforeUpdate event, or in the Comand_Click events for the 3 buttons.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim UserEntry As String

UserEntry = MsgBox("Do you want to save this record?", vbYesNo, "Save Record?")

If UserEntry = vbYes Then
Call Check_Required_Fields(Me)
Cancel = Check_Required_Fields(Me)
'so if a field was blank, Cancel= True & the record does not update, the user ends up back on the form in the field that needs input
'if all fields were populated, Cancel False and the record is updated so now how do I intiate the next action - new record, new record with some fields already populated or close form?

ElseIf UserEntry = vbNo Then
Cancel = True
'again, how do I force the next action?
End If
End Sub

Hi

First of all you'd need to change the result of the message box to an integer and not a string, and you don't need to call the function twice as you currently have it.

To perform different actions depending on which button was clicked you could set a module level variable to a different value and use that to determine what to do next.

To give that a try, at the top of the form's module (right underneath where it says Option Compare Database and hopefully Option Explicit) put

Dim intWhichButton As Integer

so at the very top it would look like



Now, for each of your three buttons, set the value of the new variable to either 1, 2 or 3. For example, in the Click event of the Add New button put

intWhichButton = 1

For the other two buttons put intWhichButton = 2 and intWhichButton = 3.

You can incorporate the value of the variable into the form's Before Update to then take the appropriate action. Add a new sub to the form's module something like



and then change your code to



***still untested aircode warning!!!

Thank you!
I was trying to work through the BeforeUpdate code last night, and just kept thinking the action part should execute from each command click event - and my brain just got stuck in a loop tryng to think about it.
Crazy migraine this morning - so the less thinking, the better.
But I am trying this out now - so appreciate your help! tx!

Hi

Hope it works for you

I'm getting a compile error Else without If,
and I can't resolve it.
Apparently I am a sloooooow learner o.O

tried this -


thnking it was just the block alignment...
nope

Hi

Told you it was aircode

There's an extraneous Else in there, try this instead

correct me where i'm wrong... (not really a question of IF i am ; ) )


I also tried the code like this:


which compiles fine, and works 'correctly' when I enter No to save
but if I enter Yes for save
it runs through all of the code forcing me to fill the fields
then asks me a 2nd time - Do you want to save -
then I get a run-time error 2105...

Hi

It would probably be easier if I could see a working (or not as it happens) copy of your db. Could you upload a demo (zipped to less than 2meg and no sensitive data) so I can take a look for you

okay - hope this works and I didn't strip out too much...
thanks for looking at it.

Hi

You had a few Call Form_BeforeUpdate's dotted around which weren't needed, plus the logic needed 'tweaking'. See if the modified attached is what you're looking for

lol, well...
yes and no.

I was trying SO hard to do this in the BeforeUpdate event, because I thought that was 'the' place to do it.

Technically - if not - couldn't this be broken down into its three parts, and each piece just be placed in the code behind each command button?

And, should I still call the Check_Required_Fields fxn on the Form Before_update event - just in case something else (unforeseen User maneuver) occurs and the User moves off the db record?

Also this assumes the User wants to save the current record before moving to a new record (which would be logical) - but does't account for the User just trying to get to a clean record and start over... so thinking I should add the Undo button back to the form...

and another (me induced) quirk...
though the Section II Add and Section III Add are 'required' - the code is not forcing them to be populated...
I'm not sure why...

Hi



Yep, there's no reason you couldn't do that if you wanted.



Wouldn't have thought that would be necessary as you're calling the function from appropriate place(s) with the three options you're giving the user. You Undo the record (Me.Undo) as well wherever suits you in the flow of things



Me neither I don't recall the validation code you have in your db (I've slept since I took a look last night) so would need to take another look to track it down

I got it -
it's a text field that allows zero length values (at the table level).
Another part of the code resets these fields everytime a higher level control is updated, so am guessing it is seeing it as populated.
I need to change me Check_Required_Fields to look for Null or zerolength values -

Nz will do this, right?

And zero length is the same as "", right??

thanks (again and again)

I think using the Nz function is not what I really meant.
I thought there was a single function to test for value length that would identify Null or zero length values.
Can't find it, so for now, I will just use the two expressions with OR...

Hi

There's a several ways to test for both Null and ZLS at the same time, eg



and others..........personally I prefer

If Nz(SomethingHere,"") = "" Then

it's either Null or a ZLS, but that's a personal preference