Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Form validation on continuous forms

    Hi
    I have an issue where I have a continuous form (filtered from a previous form) that forces the user to enter certain form fields. To achieve this there are various If, Then, Else statements in VB attached to the exit button.

    The problem I have is that the validation is only checking the first record it comes to and not any others.

    How can I get around this, I have thought of using the DCount funtion and make the validation skip through the records one by one until it has forced entry into each applicable record, but I don't know how to do this.

    Also I'm not certain if this will work, as if I enter into the say 2nd record that should have data entered into certain fields the validation still doesn't work, only the first?

    Regards
    John
    Last edited by Sticker; 05-20-04 at 11:15.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Sticker

    You could try running the validation after a change is made to a record but before the changes are saved to the record source. If you put your routines into the Form_BeforeUpdate event then it will validate as you go, rather than loads of processing on exit. Also, it will be more obvious to the user what they need to change if there is invalid data.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Pootle Flump,
    Thanks for the reply, unfortunately I don't think I can't utilise your suggestion because the data is already in the form from a data import. The required fields are those that the data import doesn't provide and therefore requires the user to enter when they go into that particulaar record.

    Regards
    John

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - so the data that is imported may be invalid rather than the data the user inputs? or both?
    Tell you what - post your code and it will give us a better idea of what you do. Having reread your posts I'm starting to get a sneaky suspicion that maybe what you want is not possible...but let's see
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi,
    All the imported info is valid but more information is required to be entered. So what I'm trying to do is twofold, [*]Force the User to enter the info[*]Make sure that the info is correct [/list]

    I've attached a sample of the code below

    Private Sub Exit_Click()


    DoCmd.Requery
    If IsNull(Forms!Security_subform!comments_code) Then
    Beep
    MsgBox "You have not entered a Comment Code, please enter one.", vbCritical, "Data not entered"
    Forms!Security_subform!comments_code.SetFocus

    ElseIf IsNull(Forms!Security_subform!Chaser) Then
    Beep
    MsgBox "You have not entered who is currently chasing this item, please enter one.", vbCritical, "Data not entered"
    Chaser.SetFocus

    ElseIf Forms!Security_subform!DiaryDate < Now() And Forms!Security_subform!mustsave = True Then
    Beep
    MsgBox "You have entered a Diary Date before Today, please enter a future one.", vbCritical, "Data not entered"
    DiaryDate.SetFocus

    Else: DoCmd.RunMacro "Chk_Chase_macro"

    End If

    End Sub


    Regards
    John

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi John

    Sorry - been chocka. Yeah, I think you are right - you'll have to enumerate and loop through your records. Unless you can apply the rules prior to import. I'll get some code to you later if no one beats me to it or comes up with something better.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Pootle Flump,
    Thanks very much for your time, its much appreciated.

    Maybe one day I'll be able to give something back to all the people on this forum that have helped me out

    Regards
    John

  8. #8
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Anyone got any ideas on how I can achieve this, it giving me real grief!!

    Regards
    John

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi John

    Apologies again- been a bit slow. Ok - I'm not really sure that this is the best you can do at all, however you are clearly still stuck. I thought about dlookups or recreating your recordset with your validation in the where statement and counting the records etc but none seemed to work. Anyway, the below loops through each record in turn and checks the data. The advantage is it stops on the first invalid record so, with some .setfocus methods or similar, you can make it blindingly obvious what data needs completing. I would run your validation routines in the Form_Current event too though - get some of the data fixed as the user goes throught the records. Also, if you have loads of records on the form this routine might take a while to run. And finally, maybe think about letting the user out if they really want to (vbYesNo message box or something) cause not letting a user get out of a form is one way to make yourself very unpopular....

    Code:
     
    Private Sub Command8_Click()
    On Error GoTo Err_Command8_Click
    	Dim rst As DAO.Recordset
    	Dim i As Integer
    	
    	Set rst = Me.RecordsetClone
    	
    	With rst
    	
    	.MoveFirst
    	
    	For i = 1 To .RecordCount - 1
    		
    		'insert your own validation code here
    		If IsNull([MyField]) Then
    			
    			MsgBox "Invalid data"
    			
    			Me.Bookmark = .Bookmark
    			
    			Exit For
    			
    		Else
    		
    			.MoveNext
    			
    			Me.Bookmark = .Bookmark
    			
    		End If
    		
    	Next i
    	
    	End With
    	
    	Set rst = Nothing
     
    Exit_Command8_Click:
    	Exit Sub
    Err_Command8_Click:
    	MsgBox Err.Description
    	Resume Exit_Command8_Click
    	
    End Sub
    Like I say, I'm not proud of it but at least you have something to start with. Err.. it doesn't close your form either, but perhaps a test on i will sort that
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    John,

    This is just my personal experience here ... When I design a form, I let the user do whatever they want in whatever order they want. I don't care. Until they try to save/update then I care ... It's at that point that I validate that all requirements are met ... In your situation, I'd let sleeping dogs lie ... UNLESS they made some kind of mod and then I'd force them to perform all required changes/additions ...

    And I just had another thought: Since you import, couldn't you test the fields and add another marker to indicate missing info ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    hi pootle flump,
    Thanks very much for the reply.

    I've put your code to work but unfortunately have fallen at the first hurdle

    on the line of code "Dim rst As DAO.Recordset" I get an error message
    "Compile error User-defined type not defined" What does this mean?


    Mike thanks for you thoughts, I am indeed letting the user enter what they want right up until the point that they want to save the record, then its at this point that the validation kicks in. I'll give some thought about testing the import and where necessary flagging the data.

    Cheers
    John

  12. #12
    Join Date
    May 2004
    Location
    Moscow, Russia
    Posts
    29
    Dim rst As DAO.Recordset

    add reference to Microsoft data access object library (Microsoft DAO)

    vba editor -> tools -> references

  13. #13
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Cheers for the radid reply AlexeyK, that solved that problem..............now on to the next!!!

    Regards
    John

  14. #14
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    OK I slipped the code into a test button and it seems to be only validating the first record. A subset of the code looks like the folllowing (I've removed the other field validation to shorten the lebght of the code.

    Private Sub Command283_Click()
    On Error GoTo Err_Command283_Click

    Dim rst As DAO.Recordset
    Dim i As Integer

    Set rst = Me.RecordsetClone

    With rst

    .MoveFirst

    For i = 1 To .RecordCount - 1

    DoCmd.Requery
    If IsNull(Forms!Security_subform!comments_code) Then
    Beep
    MsgBox "You have not entered a Comment Code, please enter one.", vbCritical, "Data not entered"
    Forms!Security_subform!comments_code.SetFocus

    ElseIf IsNull(Forms!Security_subform!Chaser) Then
    Beep
    MsgBox "You have not entered who is currently chasing this item, please enter one.", vbCritical, "Data not entered"
    Chaser.SetFocus

    Me.Bookmark = .Bookmark

    Exit For


    Else: DoCmd.RunMacro "Chk_Chase_macro"


    Me.Bookmark = .Bookmark

    End If

    Next i

    End With

    Set rst = Nothing


    Exit_Command283_Click:
    Exit Sub

    Err_Command283_Click:
    MsgBox Err.Description
    Resume Exit_Command283_Click

    End Sub


    Could somone explain what each part of the code prior to the line "DoCmd.Requery" and after the line "Else: DoCmd.RunMacro "Chk_Chase_macro" is doing up until "Exit_Command283_Click:"

    Regards
    John
    Last edited by Sticker; 05-26-04 at 06:32.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi John

    Aplogies for not mentioning about DAO. My original code would have been much better as:

    Code:
      
    Private Sub Command8_Click()
    On Error GoTo Err_Command8_Click
     
       Dim rst As DAO.Recordset
    
    'reference you recordset to the record source of  your form
    	Set rst = Me.RecordsetClone
    	
    	With rst
    
    'move to the beginning of the recordset
    	.MoveFirst
    
    'loop through the records of the recordset until you hit the end
    	Do While Not .EOF
    
    'insert your validation code here
    		If IsNull(.Fields("FieldToTest").Value) Then
     
    'If IsNull evaluates to true, let the user know and...
    			MsgBox "Invalid data"
     
    Display the invalid record on the form
    			Me.Bookmark = .Bookmark
     
    'Exit the loop
    			Exit Do
    			
    		Else
    
    'If the record is valid, go on to the next one		
    			.MoveNext
    		End If
     
    'Keep looping
    	Loop
    	
    	End With
    
    'Unbind the object variable
    	Set rst = Nothing
     
    Exit_Command8_Click:
    	Exit Sub
    Err_Command8_Click:
    	MsgBox Err.Description
    	Resume Exit_Command8_Click
    	
    End Sub
    Ok - why is it stopping at the first record? Most obvious reason is because your first record violates one of your validation rules. The code is designed to stop at the first record that fails a validation test. As it stands there is an error in your code, but probably unrelated. You would want to look at your if statements - if either of your IsNull functions evaluate to true then you want to run:

    Me.Bookmark = .Bookmark

    Exit For

    Also, it may have something to do with the macro pehaps - what does Chk_Chase_macro do?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •