Re-posted. Please help - having 'mare! 1

[Robbo1974]

I have a form with a subform, which in turn has a subform. The second subform holds details of products ordered, the first subform details of the order and the main form holds client contact data. I want to make sure that my users will complete all of the fields on the first subform before they complete the product details, hence ensuring that no incomplete orders slip through the net. I don't want to take the easy way out of setting the required property of the fields to yes, as this produces very cryptic error messages that the users will probably have a bit of trouble with, besides which it's not very professional is it? The problem is that my code does not work! This runs from the AfterUpdate event of the main form:

Private Sub Form_AfterUpdate()

If [Forms]![frmContactInformation]![frmOrders1subform].Form![Date] > Int(Now()) Then
MsgBox "Order dates can not be set for the future." & vbCrLf & vbCrLf & "Please enter a valid date.", vbOKOnly + vbExclamation, "Invalid Order Date"
[Forms]![frmContactInformation]![frmOrders1subform].Form![Date].Value = Null
    
DoCmd.OpenForm "frmCalendar3"
    
End If
    
If [Forms]![frmContactInformation]![frmOrders1subform].Form![PlacedBy].Value = Null Then
MsgBox "you have not entered a name in the 'placed by' field." & vbCrLf & vbCrLf & "Please do so now.", vbOKOnly + vbExclamation, "Incomplete Order Details"
[Forms]![frmContactInformation]![frmOrders1subform].Form![PlacedBy].SetFocus
End If

If [Forms]![frmContactInformation]![frmOrders1subform].Form![CboEmployees].Value = Null Then
MsgBox "you have not selected an employee to assign the order to." & vbCrLf & vbCrLf & "Please do so now.", vbOKOnly + vbExclamation, "Incomplete Order Details"
[Forms]![frmContactInformation]![frmOrders1subform].Form![CboEmployees].Dropdown
End If

If [Forms]![frmContactInformation]![frmOrders1subform].Form![Type].Value = Null Then
MsgBox "you have not selected an order type." & vbCrLf & vbCrLf & "Please do so now.", vbOKOnly + vbExclamation, "Incomplete Order Details"
[Forms]![frmContactInformation]![frmOrders1subform].Form![Type].Dropdown
End If

End Sub

Now the bit looking at the date works fine, it's just that the rest doesn't! Any ideas!!!???? I have tried replacing the .value = null bit with is null and also tried .value = "" and neither of those work either!
When I put an error trap in I get an error generated, but there is no err.number or err.description - just a msgbox with a 0.

This is one of two final bugs I have to iron out before I can hand my project over and deadline day is looming quite large! Any help or advice anyone can provide would be appreciated hugely.

Thanks in advance!

Robbo ;-)

 

[tomschafer]

Instead of = Null try =""

 

[Robbo1974]

as stated above I've already tried that and it doesn't work either. :-( Robbo ;-)

 

[tomschafer]

Have you tried to set the msgbox type to vbExclamation only? Instead of vbOKOnly + vbExclamation.

 

[JaneC]

Robbo,

Have you tried this ? (Not clear from your post where you tried the is null bit)


If IsNull([Forms]![frmContactInformation]![frmOrders1subform].Form![PlacedBy].Value) Then
etc

This should work.

HTH

Jane

 

[Robbo1974]

Jane, that works like a dream! Funny how the answer is always so bleedin' simple but you just can't see the wood for the trees eh? PS, if you fancy having a look at my 'Subform not displaying right record' thread then I'd probably be happy! ;-)
Robbo ;-)

 

[Robbo1974]

thread702-139367 by the way! ;-) Robbo ;-)

 

[JaneC]

Thanks, just found it.

Thinking now .......

 

[Robbo1974]

Working things through, this is still throwing up a couple of problems.

1) The conditional statement for the employees combo

If isnull([Forms]![frmContactInformation]![frmOrders1subform].Form![CboEmployees].Value)

still does not trigger, even when the field is null.

2) I am still generating an unclassified error from the after update event of the form - has nothing to do with any of the if statements though as I've tested it by taking them all out one at a time - error still comes up. Not too much of a problem as I can judst stop asking for the msgbox I suppose.

3)Once the user has clicked ok on the msgboxes they can still then proceed to fill in details for the order with those fields left uncompleted - obviously as they are onto the sub subform they are no longer triggering the after update event for the first sub. I have tried to put the code into a public function and then call the function from the after update events for the two main controls on the sub subform (the function is called Check_Order() and I just added a

call Check_Order

line to the end of a procedure that already runs on this event) and this won't work either. The system needs to be absolutely watertight to prevent incomplete orders slipping through the system.

I was thinking about using the fields required property after all but creating my own custom messages to display rather than the standard access ones. Is this possible? Robbo ;-)

 

[MichaelRed]

Well, I would offer a couple of suggestions. Mostly aimed at the old "Structure" issue -but that often helps the DEBUGGING.

First, I would place EACH of the seperate possible error 'traps' (your seperate IF /END IF Blocks)in the general section as a seperate "Procedure". In each of the individual error traps, set a boolean to show that an 'error' has occured (see next item/paragraph).

Add a boolean to the form level declarations to denote that an "error" has occured.

Call each of them (the 'error traps') in the BEFORE update procedure. After (PUN INTENDED) all, when the AFTER update occurs, the damage has been done!

At the TOP of this routine, Set the boolean error flag to False. Call each error trap routine.

At the bottom of this routine, check the error flag. If it is set (true), Set focus to a control in ttis section.

Alternatively, In each of the individual error trap routines, after the msgbox is acknowledged, set focus back to the offending control.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Robbo1974]

Michael, you've gone a bit over my head here! My VBA is fairly limited (as is evidenced by my lack of structure no doubt!)

Would this be a case of

 dim IfStatementError as boolean

set IfStatementError = false

if/End If here

error_IfStatement:

set IfStatementError = true

apologies if you're wincing at the code!!

also what exactly do you mean by "I would place EACH of the seperate possible error 'traps' (your seperate IF /END IF Blocks)in the general section as a seperate "Procedure". In each of the individual error traps, set a boolean to show that an 'error' has occured " Does this mean that I have to declare a seperate variable for each If/End If Block i.e IfStatementError1, IfStatementError2...etc?

Setting the focus back to the control does not seem to work for some reason - the focus just disappears. Perhaps this is something to do with the fact that more than one msgbox is triggered when more than one of the fields is left null.

Finally, "Add a boolean to the form level declarations to denote that an "error" has occured." Eh?

If you could step by step me I would be very grateful, if you don't have the time any help would be much appreciated.

Thanks in advance

iain.robertson@leedsrugby.com








Robbo ;-)

 

[MichaelRed]

The following should (by examole) show the answers. It almost certainly has some problems/errors as I did not construct a form for testing. It is NOT intended as "the ANSWER", but just a sample/guide. Review it. Fix the problems. Try out (the variant/fixed) the approach.

Option Compare Database

    Dim flgDate As Boolean
    Dim flgPlaceBy As Boolean
    Dim flgEmploy As Boolean
    Dim flgType As Boolean
Private Sub Form_BeforeUpdate()

    flgDate = basChkDate
    If (flgDate = True) Then
        [Forms]![frmContactInformation]![frmOrders1subform].Form![Date].SetFocus
        Exit Sub
    End If

    flgPlaceBy = basChkPlaceBy
    If (flgPlaceBy = True) Then
        [Forms]![frmContactInformation]![frmOrders1subform].Form![PlacedBy].SetFocus
        Exit Sub
    End If

    flgEmploy = basChkEmploy
    If (flgEmploy = True) Then
        [Forms]![frmContactInformation]![frmOrders1subform].Form![CboEmployees].SetFocus
        Exit Sub
    End If

    flgType = basChkType
    If (flgType = True) Then
        [Forms]![frmContactInformation]![frmOrders1subform].Form![Type].SetFocus
        Exit Sub
    End If

End Sub
Public Function basChkDate() As Boolean
    If [Forms]![frmContactInformation]![frmOrders1subform].Form![Date] > Int(Now()) Then
        MsgBox "Order dates can not be set for the future." & vbCrLf & vbCrLf & "Please enter a valid date.", vbOKOnly + vbExclamation, "Invalid Order Date"
        [Forms]![frmContactInformation]![frmOrders1subform].Form![Date].Value = Null

        DoCmd.OpenForm "frmCalendar3"
        basChkDate = True
    End If

End Function
Public Function basChkPlaceBy()
    If [Forms]![frmContactInformation]![frmOrders1subform].Form![PlacedBy].Value = Null Then
        MsgBox "you have not entered a name in the 'placed by' field." & vbCrLf & vbCrLf & "Please do so now.", vbOKOnly + vbExclamation, "Incomplete Order Details"
        [Forms]![frmContactInformation]![frmOrders1subform].Form![PlacedBy].SetFocus

        basChkPlaceBy = True
    End If

End Function
Public Function basCheckEmploy()
    If [Forms]![frmContactInformation]![frmOrders1subform].Form![CboEmployees].Value = Null Then
        MsgBox "you have not selected an employee to assign the order to." & vbCrLf & vbCrLf & "Please do so now.", vbOKOnly + vbExclamation, "Incomplete Order Details"
        [Forms]![frmContactInformation]![frmOrders1subform].Form![CboEmployees].Dropdown

        basCheckEmploy = True
    End If

End Function
Public Function basChkType()
    If [Forms]![frmContactInformation]![frmOrders1subform].Form![Type].Value = Null Then
        MsgBox "you have not selected an order type." & vbCrLf & vbCrLf & "Please do so now.", vbOKOnly + vbExclamation, "Incomplete Order Details"
        [Forms]![frmContactInformation]![frmOrders1subform].Form![Type].Dropdown

        basChkType = True
    End If

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over