Solved

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

Posted on 2009-04-14
18
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

690 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