verify fields are populated in form

[chanman525]

I have some code that is setup on a form to determine if required fields have been populated. They are required in the table but I wanted to specify the field they missed and set the focus to that field. It works great except when they choose "Next" and it goes to a new record. Then when you click "Previous" it states that a field is null, click OK and then goes to the previous record. (the previous and next button calls the sub below as well)So how do I get it to just move back a record without prompting for the null field when they "Next" to many times? Or is there a way to stop that and force them to click the New record button to get to a new record? I don't want the form to be data entry only becasue they can browse through all the records to view all the projects recorded.

Here is the code for the field verification:

Private Sub Verify()
If Me.NewRecord Then
If Me.Status.Value <> "Closed" Then
    If IsNull(Me.ProjectName) Then
    MsgBox "A Project Name has not been identified!", vbExclamation, "Required field is missing data"
    Me.ProjectName.SetFocus
    Exit Sub
        ElseIf IsNull(Me.Details) Then
        MsgBox "Details of the project have not been identified!", vbExclamation, "Required field is missing data"
        Me.Details.SetFocus
        Exit Sub
        
        ElseIf IsNull(Me.DueDate) Then
        MsgBox "A Due Date has not been identified!", vbExclamation, "Required field is missing data"
        Me.txtDueDate.SetFocus
        Exit Sub
         
        ElseIf IsNull(Me.Category) Then
        MsgBox "A Category has not been identified!", vbExclamation, "Required field is missing data"
        Me.Category.SetFocus
        Exit Sub
         
        ElseIf IsNull(Me.Severity) Then
        MsgBox "A Severity has not been identified!", vbExclamation, "Required field is missing data"
        Me.Severity.SetFocus
        Exit Sub
         
        ElseIf IsNull(Me.Requestor) Then
        MsgBox "A Requestor has not been identified!", vbExclamation, "Required field is missing data"
        Me.Requestor.SetFocus
        Exit Sub
    End If
End If
End If
End Sub

I understand this is probably not the best way to handle this but my attempt was to try and steamline the required fields and the interface to the user. Any input or suggestions would be appreciated, and thank you for your time!
A2

 

[Remou]

Have a look at this faq702-2071.

You can use the Before Update event of the form to verify, it has a Cancel event.

 

[chanman525]

Remou,
Pardon my ignorance but I am not clear on how this will help eliminate the null check on a field when it is a new record? Especially when the new record can be accessed by either using "Next" to the end of the recordset or clicking "New" to add a new record?

 

[Remou]

Sorry, I misread your post. You can disable the Next button if the record is the last one in the set:

lngCurrRec = Me.RecordsetClone.AbsolutePosition + 1
lngRecCount = Me.RecordsetClone.RecordCount
        
If lngCurrRec = lngRecCount Then
    Me.cmdNext.Enabled = False
Else
    Me.cmdNext.Enabled = True
End If

 

[chanman525]

I put the code in place and received a "Method or data member not found" on the cmdNext portion of the Me.cmdNext.Enabled = False line. I thought maybe this was just a reference that you placed in there for me to utilize so I commented it out and added the docmd.gotorecord command on the IF statement and it still let it go to a new record. So I am not sure what I am missing on this?

Arghhhh- somethings so simple to some can be the downfall of others!!

 

[Remou]

The general idea is that the button that you are using to move next should not be clicked if it will add a new record, so if you have reached the end of the recordset, the button should be disabled. You can simplify

'The number of the current record
lngCurrRec = Me.RecordsetClone.AbsolutePosition + 1
'The number of records in the recordset
lngRecCount = Me.RecordsetClone.RecordCount

'If the current record is the last record ...        
If lngCurrRec = lngRecCount Then
    '... disable the Next button
    '(cmdNext should be changed to the name of
    'the Next button) ...
    Me.cmdNext.Enabled = False
Else
    'otherwise, enable the Next button,
    Me.cmdNext.Enabled = True
End If[code]

I hope this is a little clearer.

 

[chanman525]

After I posted a response and went back to look over it, it clicked that you was referring to the cmd button on my form. DUH! Like I said "something so simple..." But it still is not working correctly. I put a message box just before the IF statement to show my the counts of the current record vs the rec count and it is showing the same (26 and 26) Even if the form is on record 2 of 26 it still displays the same aforementioned count?

Here is what the sub looks like for the button;

Private Sub BtnNext_Click()
Dim lngCurrRec As Long
Dim lngRecCount As Long

On Error GoTo Err_BtnNext_Click

    lngCurrRec = Me.RecordsetClone.AbsolutePosition + 1
    lngRecCount = Me.RecordsetClone.RecordCount
        
        MsgBox lngCurrRec & " " & lngRecCount
        
        If lngCurrRec = lngRecCount Then
            Me.BtnNext.Enabled = False
            DoCmd.GoToRecord , , acNext
        Else
            Me.BtnNext.Enabled = True
            DoCmd.GoToRecord , , acNext
        End If

Exit_BtnNext_Click:
    Exit Sub

Err_BtnNext_Click:
    MsgBox Err.Description
    Resume Exit_BtnNext_Click
End Sub

I added in the docmd NEXT on each side of the IF just because I was playing with it and could not get it to go anywhere. But even in the current state of the code, when I get to the last record it still advances to the new record.

 

[Remou]

You need to put the code in the Current event for the form rather than the click event for the button.

 

[chanman525]

Ok, i moved the code to the Current Event. I set a message box to show me the current record and the record count (as stated before). When the form opens it displays 0 for the current record and 0 for the record count. I click next and it displays the 0's again and moves to the next record and then disables the Next button? I have checked over all that i can think of and it looks good. The form is set to open and go to the first record. Any ideas?

Here is the Current event;

Private Sub Form_Current()
Dim lngCurrRec As Long
Dim lngRecCount As Long


MsgBox lngCurrRec & " " & lngRecCount
    
    lngCurrRec = Me.RecordsetClone.AbsolutePosition + 1
    lngRecCount = Me.RecordsetClone.RecordCount
        
        If lngCurrRec = lngRecCount Then
            Me.BtnNext.Enabled = False
        Else
            Me.BtnNext.Enabled = True
        End If

End Sub

Sorry to be a bother with this but i am trying to understand the whole picture and from my knowledge base i can not see any issues with this setup and why it will not work. Thank you for your time!

 

[chanman525]

ok, I found one problem. I had the msgbox before the actual set of the current and record count. That is why i got the 0 and 0. So know that i canged that i noticed that i get record 1 of 26 at the open of the form. Click next and i get 26 of 26 and it disables the button. But in actuality i am only on record 2?

 

[Remou]

Ok. This may be better:

Private Sub Form_Current()
    Me.RecordsetClone.Bookmark = Me.Bookmark
    Me.RecordsetClone.MoveNext
    If Me.RecordsetClone.EOF() Then
        Me.btnNext.Enabled = False
    Else
        Me.btnNext.Enabled = True
    End If
End Sub

 

[MajP]

Not sure if this is related, by I was recently running similar code where I was calling the recordsetclone on the oncurrent event. On the second and subsequent times I instantiated the recordsetclone the bookmark was at the last record. No idea why. Seems like you have a similar problem. A possible solution would be to declare the recordset (not the clone) object as a public variable at the top of the form

public rs as dao.recordset

then in your code

lngCurrRec = rs.AbsolutePosition + 1
lngRecCount = rs.RecordCount

If that works you could make lngRecCount a public variable as well and get the value on the Forms onOpen event one time.

 

[chanman525]

well right now the code from Remou is working. Thank you for staying with me on it Remou! I will keep the reference from MajP in case it starts to give me problems.

However, I thought that if it works for disabling the Next button that it would work for the Previous button by simply adding to the code and using Me.RecordsetClone.BOF() to enable or disable the button. But it is not working?

    Me.RecordsetClone.Bookmark = Me.Bookmark
    Me.RecordsetClone.MoveNext
    
    If Me.RecordsetClone.EOF() Then
        Me.BtnNext.Enabled = False
    Else
        Me.BtnNext.Enabled = True
    End If

    If Me.RecordsetClone.BOF() Then
        Me.BtnPrevious.Enabled = False
        Else
        Me.BtnPrevious.Enabled = True
    End If

 

[TheAceMan1]

How are ya chanman525 . . .

Realize since your validating in code yourself you don't need required set in the table. This also gets rid of the system related messages that get in the way.

A typical secnario for this would use the forms [blue]BeforeUpdate[/blue] event to run your validation code. Anytime a required field is null you set focus to that field and use the [blue]Cancel[/blue] arguement to stay on the record.

So . . . in the [Blue]Tag[/blue] property of those fields that require validation enter a question mark(no quotations please), then copy/paste the following to forms [blue]BeforeUpdate[/blue]:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, ctl As Control
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If IsNull(ctl) Then
         Msg = "'" & ctl.Name & "' is required and can't " & _
               "be left blank!" & DL & _
               "Go back and enter some data! . . ."
         Style = vbInformation + vbOKOnly
         Title = "Missing Required Data Error! . . ."
         MsgBox Msg, Style, Title
         Cancel = True
         ctl.SetFocus
         Exit For
      End If
   Next[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TheAceMan1]

chanman525 . . .

BTW: you won't have to worry about moving next or previous as the [blue]Cancel[/blue] arguement will keep you on the record until all required fields have data! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

You can just make a small change to that, chanman525

    Me.RecordsetClone.Bookmark = Me.Bookmark
    Me.RecordsetClone.MoveNext
    
    If Me.RecordsetClone.EOF() Then
        Me.BtnNext.Enabled = False
    Else
        Me.BtnNext.Enabled = True
    End If

    Me.RecordsetClone.Bookmark = Me.Bookmark
    Me.RecordsetClone.MovePrevious

    If Me.RecordsetClone.BOF() Then
        Me.BtnPrevious.Enabled = False
        Else
        Me.BtnPrevious.Enabled = True
    End If

 

[chanman525]

Thank you all for your help. I have learned alot and incorporated all the code you provided!! Things are on the uphill swing of closing this project out!

Thanks again!
A2