Required field on Continuous Forms if another field is selected on close on all records

so basically you want to check all records. you could create a function then reuse this on form_beforeupdate (validate and dont save if missing) or maybe when form loads

eg

Private Function CheckAnyMissingRequiredFields() As Boolean

    Dim bLoop As Boolean
    Dim rs As dao.Recordset

    Set rs = Me.RecordsetClone
    bLoop = True
    CheckAnyMissingRequiredFields = False
    Do While bLoop
        If rs.EOF = True Then
            bLoop = True
        ElseIf rs!FieldNameContainingPrint = "print" And IsNull(rs!FieldNamecontainingNull) = True Then
            MsgBox "You must enter a cheque number for each payment before you can exit", vbExclamation, "Reason for expenditure"
            CheckAnyMissingRequiredFields = True
            bLoop = False
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Function


You need to reference the bounded fieldname that Text16 and Text8 is related to
Basically iterating thru each record, stop when u find the first missing info and display message
function returns false if all data good, true if missing

eg usage

private sub form_beforeupdate(cancel as integer)
    if CheckAnyMissingRequiredFields = true then cancel = true
end sub

Hi I have tried the above, I pasted the first code as a function and the second on Before update on Events.
I then tried to close the form again and it still closed without displaying the error message even tho i have purposly left 3 of the "LastChqSentNo" (Text8) blank!
Text8 is bound to "LastChqSentNo"
Text16 is bound to "Print"

I have tried pasting the code above axactly and also changeing the line:
 ElseIf rs!FieldNameContainingPrint = "print" And IsNull(rs!FieldNamecontainingNull) = True Then
To:
        ElseIf rs!Print = "print" And IsNull(rs!LastChqSentNo) = True Then

I must be doing something wrong!!!

It might be to do with the referencing the bounded field names as I am unsure of if / what i need to do here?

I have also tried:
        ElseIf rs!Print = "Text16" And IsNull(rs!Text8) = True Then

Perhaps its the isnull check, might be empty string instead

slight modification

Private Function CheckAnyMissingRequiredFields() As Boolean

    Dim bLoop As Boolean
    Dim rs As dao.Recordset

    Set rs = Me.RecordsetClone
    bLoop = True
    CheckAnyMissingRequiredFields = False
    Do While bLoop
        If rs.EOF = True Then
            bLoop = True
        ElseIf rs!Print = "print" And nz(rs!LastChqSentNo, "") = "" Then
            MsgBox "You must enter a cheque number for each payment before you can exit", vbExclamation, "Reason for expenditure"
            CheckAnyMissingRequiredFields = True
            bLoop = False
        Else
            rs.MoveNext
        End If
    Loop
    rs.Close
    Set rs = Nothing
End Function

hang on, dont run it yet

Hi. Sorry, I have just pasted this code in and still the form doesnt recognise it has blank fields.
Just to go through what I have done incase I have done somthing wrong.
1st Created a function using your latest code above.
2nd pasted:
if CheckAnyMissingRequiredFields = true then cancel = true
end sub
Into Before Update in events.
Then tried to update the form but missing out a Cheque number. and tried closing the form and nothing happens.
Were am I going wrong?

Hi andy, was that from the last bit of code I posted? The one where I added a rs.movefirst

Hi Rockiroads,
I have put a screen shot of the form if this helps.
Basically each time we open this form we print out cheques for each record it displays. once you click the "print" button it puts the word "print" in a field. Once you have printed out the cheque we fill out all the corresponding cheque numbers on the form (but this is easy to forget) so i wanted to make this a required field but only coming up with the error message when you try and close the form if the word print in the field appears but NO cheque number has been entered. I can achieve this but only works if the record that is selected is the blank cheque filed.
With this being a "Continuous Forms", I realise that i can make this a required field but other forms also use this filed and in those cases it is not required.

Hope this helps (but probably confused you more as total novice at this as you can tell)
Error-if-Cheque-Null.doc

Hi. yes it was the last one you posted.

Private Function CheckAnyMissingRequiredFields() As Boolean

    Dim bLoop As Boolean
    Dim rs As dao.Recordset

    Set rs = Me.RecordsetClone
    rs.MoveFirst
    bLoop = True
    CheckAnyMissingRequiredFields = False
    Do While bLoop
        If rs.EOF = True Then
            bLoop = False
        ElseIf rs!Print = "print" And Nz(rs!LastChqSentNo, "") = "" Then
            MsgBox "You must enter a cheque number for each payment before you can exit", vbExclamation, "Reason for expenditure"
            CheckAnyMissingRequiredFields = True
            bLoop = False
        Else
            rs.MoveNext
        End If
    Loop
    rs.Close
    Set rs = Nothing
End Function

Is it possible then for you to open this form to find it in the display that you have shown? i.e. print there with cheque numbers missing?

form_beforeupdate is called if you make a change to the form

To capture it on form exit only, we can use the form_unload event

Private Sub Form_Unload(Cancel As Integer)
    if CheckAnyMissingRequiredFields = true then cancel = true
End Sub


since you havent got many rows, lets add a diagnostic, see if that helps


Private Function CheckAnyMissingRequiredFields() As Boolean

    Dim bLoop As Boolean
    Dim rs As dao.Recordset

    Set rs = Me.RecordsetClone
    rs.MoveFirst
    bLoop = True
    CheckAnyMissingRequiredFields = False
    Do While bLoop

'DIAGNOSTIC
if rs.EOF = False then
    msgbox "Print=>" & rs!Print & "<" & vbcrlf & "CNo=>" & Nz(rs!LastChqSentNo, "") & "<"
endif

        If rs.EOF = True Then
            bLoop = False
        ElseIf rs!Print = "print" And Nz(rs!LastChqSentNo, "") = "" Then
            MsgBox "You must enter a cheque number for each payment before you can exit", vbExclamation, "Reason for expenditure"
            CheckAnyMissingRequiredFields = True
            bLoop = False
        Else
            rs.MoveNext
        End If
    Loop
    rs.Close
    Set rs = Nothing
End Function

That diagnostic should display

Print=>Print<
CNo=>10001<

Print=>Print<
CNo=>10002<

Print=>Print<
CNo=><

Hi Rocki.
I think we have cracked it!!!
your main function code was spot on. I used this in the close button.

CheckAnyMissingRequiredFields
    If CheckAnyMissingRequiredFields = True Then
Exit Sub
End If
    DoCmd.Close

It seems to work.

If you close the form and any of the cheque fields are blank it then shows the error message.

Can you see a problem with using this code on close command??

Ah! one snag!

It still come up with the error message even when the field that contains the Word "print" is null.
but all my other test are working so just need to work out why it shows the message even when print field is Null!

Hi. Think I have got round this now.
I have un bound the field that contains the word "print"
I have then changed one line on the function code from:
       
ElseIf rs!Print = "print" And Nz(rs!LastChqSentNo, "") = "" Then
To
ElseIf Me.Text16 = "print" And Nz(rs!LastChqSentNo, "") = "" Then

This now seems to work perfectly

Thank you Rocki, you seem to always be the one who helps and give sound advice.
you truly are a database GURU

Hi Andy, sorry mate, I had to pop out after my last post. I just got in.

So Text16 was unbounded, no wonder it didnt work.

Little explanation

Recordsetclone is a copy of your form's recordset but one that you can meddle with without affecting the form's actual recordsource. This is wht this is used to check. Had you used the actual recordsource, the pointer to the current record is moved. Because we are checking bounded fields, we have to use the bounded fieldname, not the control name.
But since Text16 is unboundd, then in this case we have to use the control name.
Does that make sense?

With regards to closing form, you dont docmd.close that if its in the form_unload, it will prevent form from closing and will close if everything is fine.

Remember your other question, the one that mbizup reopened? I know you preferred my solution but I think since you didnt response to cap, thats probably why it got reopened and points adjusted. Funny thing is cap's answer is the accepted one, lol.

One final thing, it is always good practice to give your controls meaningful names Text16 doesnt mean much. If you revisit your vba code, say in a month's time, you might not remember so you need to look in the code.
If from the start of adding that control, you renamed it, say called this txtPrintStatus, it makes reading more meaningful, plus will help you remember much quicker.

Good luck with the rest of your project :)

Thanks again rocki,
I am very new to database programming and am trying to learn as i go, I have never used forums before for help but UNLUCKY for you, you have been so helpful that i will now be a pain in your side. :-)

It seems that i have had millions of problems with all my recent posts but I have just saved up all the items i was unable to do till now. I am about to post one more now.

Thanks again Rocki, its great that you dont just answer a question but you also explain your answers which helps us all learn.

No probs Andy, you not gonna learn if I dont explain? I can, like you know who, just give bits of code without explanation, doesnt help you though. Glad you appreciate my help, as it is, I am just a volunteer here.