Results 1 to 12 of 12
  1. #1
    ricky99 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Italy
    Posts
    16

    Question MsgBox with the list of all required fields not populated (null)

    Godday guys,
    It's since this morning I am trying to complete this automation without any result.
    Scenario: A form with some (required) textboxes and some other (not required) controls, a button to save all form's records into a table.
    Targets: before saving records the procedure needs to verify:
    a. if required fields are null (or blank), everyone or some of them;
    b. if some (or all) required fields are blank/null a message has to come up with the name of fields not populated in the form requesting an action by the user. No records have to be written in the table if these fields are not filled in.

    I've tried with the following code but it doesn't work. Better, it works but not very well because the MsgBox comes up even if all required fields are filled in (it doesn't show any field to complete). I know why, the MsgBox is after Next statement, so it comes up every time.
    Could you assist me please?

    Many thanks.



    Riccardo

    Code:
    Private Sub cmdSave_click()
    Dim ctl As Control
    Dim testo As String
    Dim messaggio As String
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox
                If ctl.Tag = "Required" And IsNull(ctl) Then
                testo = ctl.Controls(0).Caption
                messaggio = messaggio & vbCrLf & "- " & testo
                End If
        End Select
    Next ctl
    MsgBox "Following fields are required: " & vbCrLf & messaggio
    'DoCmd.Runcommand acCmdSaveRecord
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Test the length of the messaggio variable and only put up the message box if it's greater than 0.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ricky99 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Italy
    Posts
    16
    Noooo, is it really so stupid?
    I cannot believe it!
    Many thanks, waste of time for nothing.
    Bye.

    Riccardo

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help. Ciao!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Paul's answered the immediate question of what to do if the Messagebox is not needed, but you have some major problems here, in accomplishing your task of having all Required Controls populated!

    First off, Validation code, like this, needs to be in the Form_BeforeUpdate event. Period. Your code, as written, and in the cmdSave_Click event, will Save the Record when the line

    DoCmd.Runcommand acCmdSaveRecord

    fires! Regardless of everything else you have here, the Record will be Saved! That's because you've done nothing to Cancel the Update. And you can't, not in the OnClick event of a Command Button. So, first off, let's move the code to the Form_BeforeUpdate event, where the Update can be Canceled.

    Secondly, you're popping up a Messagebox listing all 'empty' Controls that need to be filled in. Problem here is that the user will have to close the Messagebox, before attempting to correct the problem, and the list disappears! Which Controls need filling in? Who knows! Who can remember?

    So, we need to check each appropriate Control, and check them one at a time, then notify the user, move Focus back to the offending Control, and provide a pause for them to correct that single problem.

    We can then, again, attempt to Save the Record. If there is another empty Control, it will now be listed and can be dealt with, appropriately. And so forth thru the entire Record. This modification of your original code will do just that:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim ctl As Control
    Dim testo As String
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox
                If ctl.Tag = "Required" And IsNull(ctl) Then
                  testo = ctl.Controls(0).Caption
                  MsgBox "Following field is required:  "  & testo
                  Cancel = True
                  ctl.SetFocus
                  Exit Sub
                 End If
        End Select
    Next ctl
    
    End Sub


    And lastly, to the question of your 'Save Button!' Save buttons, in Access, are unnecessary and go against normal way Access Databases are intended to function. In Access, when you move to another Record, new or existing, or Close a Form, or Close Access, itself, the Current Record is Saved, barring intervention. And all experienced Access users know this and expect this behavior.

    As well as being unnecessary, these Buttons often cause problems, such as the ones outlined above, for the reasons given. If you simply have to have a Save Button, to reassure your particular users, simply have it Call the Form_Before Update event:
    Code:
    Private Sub cmdSave_click()
     Call Form_BeforeUpdate(False)
    End Sub


    You may or may not also need to have an 'abort' button to your Form, in case the user doesn't have the needed info for the required Controls and simply needs to dump the Record, but that can be dealt with at a later point in time, if need be.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    ricky99 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Italy
    Posts
    16
    Hello Missinglinq,
    many thanks for that interesting reply. I have just read it with great attention. About the first part, your suggestion seems to be useful. Moving through all null controls is a better idea indeed! Thanks.
    About the last point, the Save button. I understand your words, but this idea came up looking at Northwind2007 database. The customer form (even if it is a bound form to the related table with a filter 1=0 to display only one blank record) has a button to save data and move ahead to another new blank record. I don't like too much a form for inserting something where the user can also moving through all other records or even saving the current record by pressing TAB button from the last form control.
    What would you suggest me to do? What is better for you?
    Thnaks again, bye.

    Riccardo

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ricky99 View Post
    Hello Missinglinq,

    ...but this idea came up looking at Northwind2007 database. The customer form (even if it is a bound form to the related table with a filter 1=0 to display only one blank record) has a button to save data and move ahead to another new blank record...
    And half of the code in that event, running the command acCmdSaveRecord, is useless, as anyone here will tell you. The simple act of Moving to a New Record will Save the Current Record, with no extra code needed!

    The problem is that people, such as yourself, in this very example, also start using 'Save' buttons to run Validation code (such as was given above), to make sure that needed Fields are populated, and find out that the Records are being saved with needed data missing, which is exactly what you would have experienced had you continued using your code. That's because there is no way to Cancel code in a Button's OnClick event, as I explained above. The Command Button, however you want to label it, should simply Move to a New Record, if that's your requirement. If you go back and look at that Form in Northwind, you might notice that the developer didn't bother to have a Button that simply 'Saved' a Record. How do you save the Record if you have no need for going to a New Record? You simply Close the Record, one of the ways Access is intended to Save a Record! The 'Wizards of Redmond' don't always follow best practices!

    Quote Originally Posted by ricky99 View Post
    Hello Missinglinq,

    ...I don't like too much a form for inserting something where the user can also moving through all other records...
    Did I miss something, or are you posing a new question? If you're saying that you want users to be able enter New Records but not be able to view pre-existing Records

    In Form Design View
    • Go to Properties - Data
    • Set the Data Entry Property to 'Yes'

    This will allow New Records to be entered but not show Records from previous Access sessions.

    Quote Originally Posted by ricky99 View Post
    Hello Missinglinq,

    ...or even saving the current record by pressing TAB button from the last form control.
    In Form Design View
    • Go to Properties - Cycle
    • Set the Cycle Property to 'Current Record'

    This will move the user back to the first Control on the Form, rather than automatically moving them to a New Record.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    ricky99 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Italy
    Posts
    16
    Ok Missinglinq, many thanks for your kind explanation. I am going to try tomorrow your suggestions and improve day by day my knowledgement. Bye.

    Riccardo


    edit: Ok, I have used the Form_BeforeUpdate event to set all checks and configured Data entry = Yes and Cycle property = Current record in the Form properties.
    The button click event is this:
    Code:
    Private sub cmdSave_click()
    Call Form_BeforeUpdate(False)
    DoCmd.GoToRecord , , acNewRec
    It is obviously wrong because it doesn't work.
    When I click on the button and all fields (or the one of the fields required) are blank the first MsgBox comes up: "The followind field ... is required". I press OK and the same MsgBox comes up again, then I press OK and instead of moving to the control a message error it is showed:
    "You can't go to the specific error".
    Furthermore, another check not mandatory (but with a MsgBox with vbYesNo) doesn't work as well, when all required fields are not blank. Instead of moving to the specific control when vbYes = true the routine goes ahead and move to the new record.
    I would like to avoid the user clicking on the Save button before having completed all required fields, but no luck on this side.
    Last edited by ricky99; 08-24-2012 at 02:48 AM.

  9. #9
    ricky99 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Italy
    Posts
    16
    The Form_BeforeUpdate should be the best way to validate data inserted in the form, but on my side it does not work, at all.
    Here is the code for that event:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    '### check if mandatory fields are blank/null ###'
    Dim ctl As Control
    Dim testo As String
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox
                If ctl.Tag = "Required" And IsNull(ctl) Then
                  testo = ctl.Controls(0).Caption
                  testo = Left(testo, Len(testo) - 1)
                  MsgBox "The following field is required: " & vbCrLf & vbCrLf _
                  & "- " & testo
                  Cancel = True
                  ctl.SetFocus
                  Exit Sub
                End If
        End Select
    Next ctl
    
    '### Check lenght of Latitude field ###'
    If Len(txtLat) <> 7 Then
        MsgBox "Latitude is not correct, please complete all components of the field."
        Cancel = True
        txtLat.SetFocus
        Exit Sub
    ElseIf IsNull(txtLat) Then
        Dim textMessage As Integer
            textMessage = MsgBox("Latitude data is not mandatory, but the distance calculation feature in the " _
            & "flight data center will not be available. Do you want to add latitude?", vbYesNo)
                If textMessage = vbYes Then
                    txtLat.SetFocus
                    Exit Sub
                Else
                End If
    Else
    txtLat = UCase(txtLat)
    End If
    
    '### Check lenght of Longitude field ###'
    If Len(txtLon) <> 8 Then
        MsgBox "Longitude is not correct, please complete all components of the field."
        Cancel = True
        txtLon.SetFocus
        Exit Sub
    ElseIf IsNull(txtLon) Then
        Dim textMessage2 As Integer
        textMessage2 = MsgBox("Longitude data is not mandatory, but the distance calculation feature in the " _
            & "flight data center will not be available. Do you want to add longitude?", vbYesNo)
                If textMessage2 = vbYes Then
                    txtLon.SetFocus
                    Exit Sub
                Else
                MsgBox "All data you inserted will be saved shortly."
                End If
    Else
    txtLon = UCase(txtLon)
    End If
    
    '### checks for the Latitude field components ###'
    If Mid(txtLat, 2, 2) >= 90 Then
        MsgBox "Latitude cannot be higher than 90° degrees, please edit the field accordingly"
        Cancel = True
        txtLat.SetFocus
    ElseIf Mid(txtLat, 4, 2) >= 60 Then
        MsgBox "Minutes of Latitude field cannot be higher than 60, please edit the field accordingly"
        Cancel = True
        txtLat.SetFocus
    ElseIf Mid(txtLat, 6, 2) >= 60 Then
        MsgBox "Seconds of Latitude field cannot be higher than 60, please edit the field accordingly"
        Cancel = True
        txtLat.SetFocus
    Else
    txtLat = UCase(txtLat)
    End If
    
    '### checks for the Longitude field components ###'
    If Mid(txtLon, 2, 3) >= 180 Then
        MsgBox "Longitude cannot be higher than 180° degrees, please edit the field accordingly"
        Cancel = True
        txtLon.SetFocus
        Exit Sub
    ElseIf Mid(txtLon, 5, 2) >= 60 Then
        MsgBox "Minutes of Longitude field cannot be higher than 60, please edit the field accordingly"
        Cancel = True
        txtLon.SetFocus
    ElseIf Mid(txtLon, 7, 2) >= 60 Then
        MsgBox "Seconds of Longitude field cannot be higher than 60, please edit the field accordingly"
        Cancel = True
        txtLon.SetFocus
    Else
    txtLon = UCase(txtLon)
    End If
    End Sub
    Could anyone suggest me a code for the SAVE button in the form, which has to do this actions:
    1. fire the BeforeUpdate event to check if all required fields are ok, if the Lat/Lon fields are completed in correct way;
    2. save the current record into the bound table;
    3. move to the next new record.

    I have also tried with the Me.dirty property but no luck again.
    Thanks bye.

    Riccardo

  10. #10
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ricky99 View Post

    Could anyone suggest me a code for the SAVE button in the form, which has to do this actions:
    1. fire the BeforeUpdate event to check if all required fields are ok, if the Lat/Lon fields are completed in correct way;
    2. save the current record into the bound table;
    3. move to the next new record.
    As I said, before, all you need to do, to accomplish all of this, is to Move to a New Record. This one line, behind your 'Save' button, will do that:

    DoCmd.GoToRecord , , acNewRec

    What part of the Validation code are you having problems with? Some of the If...Thens below the line

    '### checks for the Latitude field components ###'

    are missing the Exit Sub command, which is needed when doing multiple Validations like this, so you need to correct this. Omitting this line means that after you clear the Messagebox, the code moves on to the next Validation, not stopping for you to correct the failed one, if it occurs.

    Other possible problem with it, in this kind of thing, could be a misplaced or unmatched If..Then...Else. I'll put your code into a module, tomorrow, and check it for you, if you're still having trouble with it.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    ricky99 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Italy
    Posts
    16
    Negative. I have commented all checks in the beforeupdate event except for the first one. As said before, when the form is entirely blank And press on the save button it returns an error saying it is not possible to reach next record. Before the debug a Msgbox comes up twice showing the control to modify, i press on it And then the debug with the error told you. I think to return all checks within the cmdSave button, it's defitinively better...

  12. #12
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by ricky99 View Post
    ...I think to return all checks within the cmdSave button, it's defitinively better...
    Well, you keep right on thinking that.
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Table fields populated from query
    By jcarstens in forum Access
    Replies: 1
    Last Post: 04-08-2012, 05:55 PM
  2. Help Required on Report List
    By cap.zadi in forum Reports
    Replies: 9
    Last Post: 09-23-2011, 05:10 PM
  3. Issues with Auto populated fields
    By denise1005 in forum Queries
    Replies: 3
    Last Post: 03-17-2011, 10:50 AM
  4. If Query result Is Null...MsgBox..Okl
    By Bruce in forum Forms
    Replies: 28
    Last Post: 03-10-2010, 10:57 AM
  5. Access 2000 Auto Populated Fields
    By Cylena in forum Access
    Replies: 1
    Last Post: 05-18-2009, 07:50 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums