Solved

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

Posted on 2009-04-14
18
269 Views
Last Modified: 2013-11-28
Hi, I have a form that shows records by "Continuous Forms". One of the fields "LastChqSentNo" I require to have a error message if it is Null and another field contains the word "print".
I cant use the function required field as this field is also used in other forms were it is not required.
If I use the following code:

If Text16 = "print" And IsNull(Me.Text8) Then
iAddrQuest = MsgBox("You must enter a cheque number for each payment before you can exit", vbExclamation, "Reason for expenditure")
Exit Sub
End If

If I use the code above it only works on the field if it is the selected record in the form.

How can I use VBA so that if any of the records on view in the Continuous Forms are null it will give an error message if selected or not?
0
Comment
Question by:andybrooke
  • 9
  • 9
18 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24136565
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

0
 

Author Comment

by:andybrooke
ID: 24136719
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?
0
 

Author Comment

by:andybrooke
ID: 24136770
I have also tried:
        ElseIf rs!Print = "Text16" And IsNull(rs!Text8) = True Then
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24136866
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24136895
hang on, dont run it yet
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24136923
ok, realised what was missing, gotta put in movefirst then set bLoop correctly

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
0
 

Author Comment

by:andybrooke
ID: 24136998
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?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24137113
Hi andy, was that from the last bit of code I posted? The one where I added a rs.movefirst
0
 

Author Comment

by:andybrooke
ID: 24137139
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:andybrooke
ID: 24137142
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24137348
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24137360
That diagnostic should display

Print=>Print<
CNo=>10001<

Print=>Print<
CNo=>10002<

Print=>Print<
CNo=><

0
 

Author Comment

by:andybrooke
ID: 24137430
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??
0
 

Author Comment

by:andybrooke
ID: 24137476
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!
0
 

Author Comment

by:andybrooke
ID: 24137554
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24138122
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 :)

0
 

Author Comment

by:andybrooke
ID: 24148152
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24148209
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.

0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now