Looking for Best Method

[Adrianna]

Okay I have a few fields that automatically update to provide the user with a preview of their record status. This information is at the beginning of their for and changes if the user makes changes to the fields that affect the status fields. All of the automatically updated fields that rely on just a few suject fields are working properly, but ......

I have one Auto Update field that displays an over all prject status. This field needs to update based on the information in almost all of the other fields in the form (and there are a lot). So, I was wonder what the best way of checking for IsNotNull on groups of fields would be. Basically, I have about 15 fields that determine Stage 1 (which is the default, but until all 15 are filled in...I don't want to move to Stage 2). Once all 15 are NotNull, I want to more to Stage 2 and start checking if the next 8 fields are NotNull. When those 8 are NotNull, I want to update to Stage 3....and so forth.

For the short auto fields, IFThenElse statements work well, but this would be a monster of a nested If statement, so I was wondering what would be the best way to make this overall project status field work?

 

[Fizzio]

Rather that using if...then, why not use a for..next, cycling trough all the textbox / combo box entries and not allowing the user to contine until then. This is easy if the controls are on separate forms / subforms for each stage but not so easy if they are on the same form. An alternative to this would be to usilise the tag of each control and read this before updating the form and if it meets the criteria you specify, allow the user to continue. for example you could set the tag to 1,2 then use the instr function to strip the numbers (of each stage the field is required in) and proceed if met.

 

[Adrianna]

Fizzio,
Interesting Suggestion. Unfortuantely, the user isn't forced to fill in any of the information at a specific time. This database tracks the progress or status of projects. So each time they enter information into the database, I need to reassess the project Stage that they are in.

I have this set in OnCurrent() for the form and AfterUpdate() on the subject fields.....hmm....I should move those to OnChange().


Anyway, I just need a way of saying

If Not IsNull(All Stage1 Fields) Then
If Not IsNull(All Stage 2 Fields) Then
If Not IsNull(All Stage 3 Fields) Then
Me.Stage = "Stage 4"
Else
Me.Stage = "Stage 3"
EndIf
Else
Me.Stage = "Stage 2"
EndIf
Else
Me.Stage = "Stage 3"
EndIf

 

[Fizzio]

You could still do it with the for...next loop using the tag property of the control to control your If....then. Use a custom function to loop through all the fields of the form, checking
a) what value(s) are in the tag property
b) if that field is populated

brief example (nowhere near correct syntax though!)

Function fnCheckStage (frmA as Form, intStage as Integer) as Boolean
Dim ctl As Control
Dim Msg, Style, Title, Response, MyString

fnCheckStage = True

For Each ctl In frmA.Controls
If InStr(1, ctl.Tag, intStage) > 0 Then 'Check for 1 in Tag
If isnull(ctl.value) then 'If field is null then
fnCheckStage = False
exit for
end if
end if

Next
End Function

You could then just call the function from the form ie
if fnCheckStage(Form,1) 'Checks stage 1
or use a for next loop to chech each stage in turn etc.

Just make sure that the tag of the control is set to whatever stage is applicable

(Code modified version of donation by Rich to validate a form)

HTH