Validate form and subform on attempted exit

[andy_dyer]

Hi,

I'm trying to get my database to check if fields are completed prior to exit and if not take them back to the missing fields... I've managed this on previous databases but this one is complicated with a subform...

I want to check whether a field (txtInvoiceAmount) has an entry and if it is greater than 0... if yes to both then great if not to either or both then I want to set the focus to the field on the right record within the subform...

Private Sub cmdReturntoMenu_Click()
If Me.NewRecord And Me.Dirty = False Then
  DoCmd.Close
  DoCmd.OpenForm "frmMainMenu"
  Exit Sub
 End If

 If Me!Invoice.Form!txtInvoiceAmount > 0 Then
  DoCmd.Close
  DoCmd.OpenForm "frmMainMenu"
  Exit Sub
 End If

 If Me!Invoice.Form!txtInvoiceAmount = Null Or 0 Then
  DoCmd.OpenForm "frmError4"
  Me!Invoice.Form!txtInvoiceAmount.SetFocus
  Exit Sub
 End If

End Sub

At present - it does nothing on the button being clicked so it is obviously not registering something somewhere...

Any ideas??

 

[andy_dyer]

I'm still playing with this and my current code is this:

If Me!Invoice.Form.Dirty = True And IsNull(Invoice.Form!txtInvoiceAmount) Then
 DoCmd.OpenForm "frmError4"
 Me!Invoice.Form!txtInvoiceAmount.SetFocus
 Exit Sub
End If

Still no success though... any suggestions or pointers would be gratefully received.

 

[DCrake]

Assuming you are in the form called Invoice

If Me.Dirty And Nz(Me.TxtInvoiceAmount,0) = 0 Then
   Do this
Else
   Do That
End If

 

[andy_dyer]

Thanks for coming back to me!

The button is on frmProject with frmInvoice embedded in it...

I've tried to adjust your code but still have no response from the database...

If Me!Invoice.Form.Dirty And Nz(Me!Invoice.Form!txtInvoiceAmount, 0) = 0 Then
 DoCmd.OpenForm "frmError4"
 Me!Invoice.Form!txtInvoiceAmount.SetFocus
 Exit Sub
End If

 

[DCrake]

Have you tried putting a beakpoint on the code and stepping through it?

Can it actually see the txtinvoiceamount on the subform. Could you not have a hidden control on the mainform that looks at the subform control and in you checking refer to this control as oppoosed to the sub form control.

David

 

[andy_dyer]

I'm not too sure about breakpoints or hidden controls... apart from the bits of coding I've learnt from people on this forum I'm a bit clueless...

I've tried to give it more code to help it close if it's ok but still nothing...

If Me!Invoice.Form.Dirty And Nz(Me!Invoice.Form!txtInvoiceAmount, 0) > 0 Then
  DoCmd.Close
  DoCmd.OpenForm "frmMainMenu"
 Exit Sub
End If

If Me!Invoice.Form.Dirty And Nz(Me!Invoice.Form!txtInvoiceAmount, 0) = 0 Then
 DoCmd.OpenForm "frmError4"
 Me!Invoice.Form!txtInvoiceAmount.SetFocus
 Exit Sub
End If

 

[andy_dyer]

Have you tried putting a beakpoint on the code and stepping through it?

Can it actually see the txtinvoiceamount on the subform. Could you not have a hidden control on the mainform that looks at the subform control and in you checking refer to this control as oppoosed to the sub form control.

David

Yes it can see the field as it can make it visible and invisible (just tested that)...

I think it may have something to do with the ability to check all records on the subform for the single record on the main form... is there a way of checking the field for all records on the subform and then if any are not completed properly then open the correct subform's record and set the focus??

 

[DCrake]

Your subform will have an underlying query/table as its recordsource, what you could do is to test it for the reasons you described earlier. If the condition is met then you could use that information to open up the form you want with the appropriate filter applied.

David

 

[andy_dyer]

Your subform will have an underlying query/table as its recordsource, what you could do is to test it for the reasons you described earlier. If the condition is met then you could use that information to open up the form you want with the appropriate filter applied.

David

I'm not quite sure that i understand...

I've got a subform based on the following query:

SELECT tblInvoice.[Invoice ID], tblInvoice.[Project Number], tblInvoice.[Project Title], tblInvoice.[Planned Invoice Date], tblInvoice.[Actual Invoice Date], tblInvoice.[Invoice Number], tblInvoice.[Invoice Amount (NET)], tblInvoice.[Payment Received Date], tblInvoice.ProjectID, tblInvoice.[Invoice Sent?], tblInvoice.[Invoice Paid?]
FROM tblProject INNER JOIN tblInvoice ON tblProject.ProjectID = tblInvoice.ProjectID
WHERE (((tblInvoice.ProjectID)=[forms]![frmProject]![ProjectID]));

Which displays all the records in the subform based on the main form's ProjectID...

How can I use this to help me check if the field is blank and open an error form?

I'm sorry if this is basic...

 

[andy_dyer]

Ok...

I've created another query:

SELECT Count(qryInvoice.[Invoice ID]) AS [Number]
FROM qryInvoice
WHERE (((qryInvoice.[Invoice Amount (NET)]) Is Null Or (qryInvoice.[Invoice Amount (NET)])=0));

This counts the number of nulls or 0's that i have in this field...

My current vba in my main form on my exit button click is now:

If DoCmd.OpenQuery(qryInvoiceAmountNull) = 0 Then
  DoCmd.Close
  DoCmd.OpenForm "frmMainMenu"
Else
  DoCmd.OpenForm "frmError4"
  Me!Invoice.Form!txtInvoiceAmount.SetFocus
End If

This doesn't seem to work... any pointers on my code to check this query?

 

[andy_dyer]

No progress over the weekend...

Can anyone help??

 

[LPurvis]

This is a continuous (or at least multiple record) subform?
And you want to verify that no row as a zero or Null value in a field (txtInvoiceAmount - is that the name of the field or the control, or both? Personally I dislike prefixing field names with data type...).

I'd go hitting the subform's recordset myself.
It's already loaded so you're being more efficient by not re-requesting this information from the database.

With Me.SubformName.Form
    If .RecordsetClone.RecordCount > 0 Then .RecordsetClone.MoveFirst
    Do Until .RecordsetClone.EOF
        If Nz(.RecordsetClone!txtInvoiceAmount, 0) = 0 Then
            .BookMark = .RecordsetClone.BookMark
            !txtInvoiceAmount.SetFocus
            Msgbox "A value must be supllied for Invoice Amount", vbInformation
            Exit Do
        End If
        .RecordsetClone.MoveNext
    Loop
End With

However relying on code in an event in your parent form isn't as rigorous as, say, making the field required (and not zero) in the table's definition.
(Or at least using a parent form event that can't be easily circumvented).

Cheers.

 

[andy_dyer]

This is a continuous (or at least multiple record) subform?
And you want to verify that no row as a zero or Null value in a field (txtInvoiceAmount - is that the name of the field or the control, or both? Personally I dislike prefixing field names with data type...).

I'd go hitting the subform's recordset myself.
It's already loaded so you're being more efficient by not re-requesting this information from the database.

With Me.SubformName.Form
    If .RecordsetClone.RecordCount > 0 Then .RecordsetClone.MoveFirst
    Do Until .RecordsetClone.EOF
        If Nz(.RecordsetClone!txtInvoiceAmount, 0) = 0 Then
            .BookMark = .RecordsetClone.BookMark
            !txtInvoiceAmount.SetFocus
            Msgbox "A value must be supllied for Invoice Amount", vbInformation
            Exit Do
        End If
        .RecordsetClone.MoveNext
    Loop
End With

However relying on code in an event in your parent form isn't as rigorous as, say, making the field required (and not zero) in the table's definition.
(Or at least using a parent form event that can't be easily circumvented).

Cheers.

Hi Leigh,

Yes this is a multi-record sub form - txtInvoiceAmount is the textbox name...

Is there a way of changing the standard Access non-user friendly message that comes up if the field is null when it is marked required? (can't believe I hadn't thought of that...)

 

[LPurvis]

You can trap that error in the Form's Error event.
(It is raised in response to Data errors - not VBA errors).

You don't receive any information about which field caused the error - you could look to supply that yourself by examining the ActiveControl of the form of course.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3314 Then
    MsgBox "You must supply a value for this field", vbInformation
    Response = acDataErrContinue
End If

End Sub

Cheers.

 

[andy_dyer]

Thanks Leigh - I've placed that code in my form and subform On Error sections and changed the wording and it works... sort of...

As soon as I create a new record in my main form - it creates a new record in my subform as well (was only doing this when i actually purposely dirtied the subform previously) and comes up with the error message for the subform - what have I done??

I need to be able to click on add and then change my mind if I need to without dirtying the form - thi is just dirtying itself!!



I've also got a few fields that refresh on got or lost focus - this is causing the error to be raised for all of my required fields without going through the handler and so giving me the standard error... help!!

 

[andy_dyer]

Ok I've worked out why my subform was dirtying itself... I had an entry that was populating On Current - I've now taken this out...

The subform is now behaving fine - but I am able to shut a form that is dirty with some of my required fields empty...

How????

Plus I'm still getting standard error messages about the required fields until I populate them all - isn't there someway to delay the check for required fields??

So frustrating...

 

[LPurvis]

>> but I am able to shut a form that is dirty with some of my required fields empty
They'll not be empty (as in Null) - they'll be zero length strings (i.e. have you edited these fields' controls and then exited with them "empty"?)
Set the field properties to "Allow Zero Length" to false (No).

>> isn't there someway to delay the check for required fields
It's an "either/or" I'm afraid.
If the field is required (and your form and control are appropriately bound) then the required error is launched as the field is updated - not the record.
If you wanted a field to be raised as required only upon committing the record then you'd have to set the field as Not required and use a table level validation rule instead.
i.e.
[FieldName] Is Not Null
in the table's properties Validation Rule property.

Bear in mind that you can then set the Validation Text property to give a message you're more happy with in the table properties. If you want to still handle this in the form, then the same error event applies - but the DataErr number you're checking for then is 3317.

Cheers.

 

[andy_dyer]

>> but I am able to shut a form that is dirty with some of my required fields empty
They'll not be empty (as in Null) - they'll be zero length strings (i.e. have you edited these fields' controls and then exited with them "empty"?)
Set the field properties to "Allow Zero Length" to false (No).

>> isn't there someway to delay the check for required fields
It's an "either/or" I'm afraid.
If the field is required (and your form and control are appropriately bound) then the required error is launched as the field is updated - not the record.
If you wanted a field to be raised as required only upon committing the record then you'd have to set the field as Not required and use a table level validation rule instead.
i.e.
[FieldName] Is Not Null
in the table's properties Validation Rule property.

Bear in mind that you can then set the Validation Text property to give a message you're more happy with in the table properties. If you want to still handle this in the form, then the same error event applies - but the DataErr number you're checking for then is 3317.

Cheers.

I've changed them from being required and used the code and a simple message in the validation rule and text - on choosing the first option in my cascading combos I now get this error and have to click end to clear it then it allows me to continue...

So ti is still trying to validate at a field level because after combo box lost focus it is refreshing or requerying the next combo and causing the error...

How can i fix this one??

 

[LPurvis]

Those error messages alone (i.e. out of context and implementation) don't tell me enough really.
VBA errors should be occuring.
Are you attempting to force a save somehow?
Attach the relevant form and table in an mdb instead?

 

[andy_dyer]

Whole database attached...

Just try and add a new project and work down the combo boxes and you'll see what is happening...

Thanks for your help!!