Disable Button

[rblair11]

I want to disable the button "cmdNew" unless certain criteria are met. Here is the code that runs in Form_Current :

Private Sub Form_Current()

Me.cmdNext.Enabled = (Me.RecordsetClone.RecordCount > Me.CurrentRecord)
Me.cmdPrevious.Enabled = Me.CurrentRecord > 1
Me.cmdLast.Enabled = (Me.RecordsetClone.RecordCount > Me.CurrentRecord)
Me.cmdFirst.Enabled = Me.CurrentRecord > 1

If (Me.cboStatus.Value = "Approved" Or Me.cboStatus.Value = "Approved as Noted" Or IsNull(Me.cboStatus) Or Me.RecordsetClone.RecordCount > Me.CurrentRecord) Then
    Me.cmdNew.Enabled = False
Else
    Me.cmdNew.Enabled = True
End If

End Sub

The navigation buttons work flawlessly but the cmdNew button still is enabled when it shouldn't be. THe idea is that is will be disabled if:

-current record is not the last record
-cbostatus is blank(null)
-cbostatus is "approved"
-cbostatus is "approved as noted"

The only code that seems to work is the "me.recordsetclone.recordcount > Me.currentrecord" .

Am I going about this the wrong way? How can I check what is in the cbostatus box and have the cmdnew button enabled/disabled based on its value.

Thank you

 

[RuralGuy]

What is the RowSource of your ComboBox and which column is the bound column?

 

[aleb]

In the properties of you combo box you need to put on change event
a call for your sub

Private Sub cboStatus_Change()
Call Form_Current
End Sub

 

[rblair11]

Ruralguy, the rowsource is a value list.

Aleb, I have code in the afterupdate event and it works. The problem is that when I navigate through the record set (single form view) there are records that have the cmdNew enabled when it should be disabled. The problem with relying on the afterupdate event is that the code only fires after an update (obviously).

This seems like it should be easy...I must be missing something???

 

[RuralGuy]

Do you have your ComboBox cboStatus bound to a field in the recordset? You might also change this OR: IsNull(Me.cboStatus) to
Len(Me.cboStatus & "") = 0
That will cover both Null and zero length strings (ZLS)

 

[rblair11]

Yes, cboStatus is bound to a field in the Recordset.

I tried the suggestions here and I still don't have a working solution. The button seems to disable correctly when I'm not on the last record, but when I am on the last record the button is enabled even if cbostatus is blank.

After a new record is created the button also stays enabled.

 

[RuralGuy]

Early quote:

The problem is that when I navigate through the record set (single form view) there are records that have the cmdNew enabled when it should be disabled.

Later on:

The button seems to disable correctly when I'm not on the last record...

It sounds like the button is working better than it was when this thread began.

I would replace the following code in the Current event:

If (Me.cboStatus.Value = "Approved" Or Me.cboStatus.Value = "Approved as Noted" Or IsNull(Me.cboStatus) Or Me.RecordsetClone.RecordCount > Me.CurrentRecord) Then
    Me.cmdNew.Enabled = False
Else
    Me.cmdNew.Enabled = True
End If

With:

If Me.cboStatus = "Approved" Then
   Me.cmdNew.Enabled = False
ElseIf Me.cboStatus = "Approved as Noted" Then
   Me.cmdNew.Enabled = False
ElseIf Len(Me.cboStatus & "") = 0 Then
   Me.cmdNew.Enabled = False
ElseIf Me.RecordsetClone.RecordCount > Me.CurrentRecord Then
   Me.cmdNew.Enabled = False
Else
   Me.cmdNew.Enabled = True
End If

Then you can single step through the code and hover over conditions and see what value they contain.

 

[aleb]

check this out

 

[rblair11]

Thank you both of you. The code still doesn't completly work. If I go into cboStatus and give it a value then the rules kick in. Even if I delete the entry so it is null (blank) the rules still work. Even if I close the form and enter into the same recordset the rules continue to work. But if I don't click into cboStatus and I'm on the last record then the cmdNew button is enabled even though the value is null (blank).

So I guess the problem is how to have the on_current rules work before cboStatus is updated.

Sorry this is so confusing.

I should also note that the form is designed to open to the last record in the recordset.

 

[RuralGuy]

What have you done to assure that Me.RecordSetClone.RecordCount is accurate? Are aware that it is not guaranteed accurate until you have loaded the entire recordset, usually with a .MoveLast?

 

[rblair11]

I've seen mention of .movelast before but quite honestly I do not understand how access handles recordset and recordsetclone.

I'll play with it but do I put the code in the on_load or on_current?

Thanks for the suggestion, I'll let you know what I find.

 

[RuralGuy]

If you are changing the .RecordCount by adding or deleting records, which I believe you are, then put it in the Current event; otherwise the Load event would work just fine, the Open event being too early in the process. All you need is: Me.RecordSetClone.MoveLast in the Current event.
The RecordSetClone is always valid and available while RecordSet is valid. No Set is required.

 

[rblair11]

Got it working

Thanks again to both of you. I think I had some other small issues (a result of trying to fix the original problem). The main issue being that somehow the default value of cboStatus got set to " " .

In case it helps someone, here is the final code I ended up with:

Option Compare Database
Option Explicit

Private Sub cboStatus_Enter()
SendKeys "%{DOWN}", True
End Sub

Private Sub cboStatus_LostFocus()
Call Form_Current
End Sub

Private Sub Comments_LostFocus()
Me.Submitted_Date.SetFocus
End Sub

Private Sub Form_Current()

Me.cmdFirst.Enabled = Me.CurrentRecord > 1
Me.cmdPrevious.Enabled = Me.CurrentRecord > 1
Me.cmdNext.Enabled = (Me.RecordsetClone.RecordCount > Me.CurrentRecord)
Me.cmdLast.Enabled = (Me.RecordsetClone.RecordCount > Me.CurrentRecord)

If (Me.cboStatus.Value = "Approved" Or Me.cboStatus.Value = "Approved as Noted" Or IsNull(Me.cboStatus) Or Me.RecordsetClone.RecordCount > Me.CurrentRecord) Then
    Me.cmdNew.Enabled = False
Else
    Me.cmdNew.Enabled = True
    End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Call Form_Current
End Sub


Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
Me.txtProjectNameHeader = Forms!frmProjectSelection!txtProjectName
If (Me.CurrentRecord = 0) Then
Me.cmdNext.Enabled = True
Me.Refresh
End If
End Sub

Private Sub cmdCloseForms_Click()
On Error GoTo Err_cmdCloseForms_Click


    DoCmd.Close

Exit_cmdCloseForms_Click:
    Exit Sub

Err_cmdCloseForms_Click:
    MsgBox Err.Description
    Resume Exit_cmdCloseForms_Click
    
End Sub
Private Sub cmdNew_Click()
Dim F As Integer
Dim n As Integer

    F = Me.SubmittalID
    Me.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
    n = DMax("Revision_NO", "tblSubmittalRevisions", "[SubmittalID] = " & F)
    Me.Revision_No = n + 1
    Me.SubmittalID = F
    Submitted_Date.SetFocus
    Me.Refresh
      

Exit_cmdNew_Click:
    Exit Sub

Err_cmdNew_Click:
    MsgBox Err.Description
    Resume Exit_cmdNew_Click
    
End Sub
Private Sub cmdFirst_Click()
DoCmd.GoToRecord , , acFirst
End Sub
Private Sub cmdNext_Click()
DoCmd.GoToRecord , , acNext
End Sub
Private Sub cmdPrevious_Click()
DoCmd.GoToRecord , , acPrevious
End Sub
Private Sub cmdLast_Click()
DoCmd.GoToRecord , , acLast
End Sub

Private Sub cmdGoToSubmittalSelection_Click()
On Error GoTo Err_cmdGoToSubmittalSelection_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    DoCmd.Close
    stDocName = "frmSubmittalSelection"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdGoToSubmittalSelection_Click:
    Exit Sub

Err_cmdGoToSubmittalSelection_Click:
    MsgBox Err.Description
    Resume Exit_cmdGoToSubmittalSelection_Click
    
End Sub

Private Sub cboStatus_Change()
Call Form_Current
End Sub