Hi,
Have been trawling the forums, but have had no luck..
I am using a combo with a filter to populate a subform.
When I use a filter that retruns no records, the subform automatically enters a 'new record state' (Me.NewRecord = True). This causes some buttons on my form to wrongly display (Prev Record). But I want the subform not to embark on a new record (so that by Prev & Next buttons are disabled and there is a New button if the User wants to create a new record). Any ideas?
Dave
This is the after update for the combo:
Private Sub cboProjectID_AfterUpdate()
On Error GoTo cboProjectID_AfterUpdate_Error
' Set the Filter
Me.Filter = "ProjectID = " & Me!cboProjectID
Me.FilterOn = True
' Requery Form
Forms![fmTasks].Requery
' Requery SubForm
Me!fmTasksSubForm.Requery
On Error GoTo 0
Exit Sub
cboProjectID_AfterUpdate_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboProjectID_AfterUpdate of VBA Document Form_fmTasks"
End Sub
And this is my button controls:
Private Sub Form_Current()
' This is to control the PREVIOUS and NEXT Buttons
Dim recClone As Recordset
Dim intNewRecord As Integer
' Make a clone of the recordset underlying the form so we can move
' around that without affecting the form's recordset
On Error GoTo Form_Current_Error
Set recClone = Me.RecordsetClone
' If this is a new record then disable the <NEXT> button and enable
' the <PREV> button and then exit the procedure
If Me.NewRecord Then
btnPrevious.Enabled = True
btnNext.Enabled = False
Exit Sub
End If
' IT NEVER REACHES HERE (MY PROBLEM)
' If we reach here, we know we are not in a new record so need to
' see if there are no records. If this is the case then we must
' disable both the <PREV> and <NEXT> buttons.
If recClone.RecordCount = 0 Then
btnPrevious.Enabled = False
btnNext.Enabled = False
Else
' Synchronise the current pointer in the two recordsets
recClone.Bookmark = Me.Bookmark
' If there are records, see if we are on the first record.
' If so, we should disable the <PREV> buttons.
recClone.MovePrevious
btnPrevious.Enabled = Not (recClone.BOF)
recClone.MoveNext
' And then check whether we are on the last record.
' If so, we should disable the <NEXT> buttons.
recClone.MoveNext
btnNext.Enabled = Not (recClone.EOF)
recClone.MovePrevious
End If
' Finally, we close the cloned recordset
recClone.Close
On Error GoTo 0
Exit Sub
Form_Current_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_fmTasks"
End Sub
Have been trawling the forums, but have had no luck..
I am using a combo with a filter to populate a subform.
When I use a filter that retruns no records, the subform automatically enters a 'new record state' (Me.NewRecord = True). This causes some buttons on my form to wrongly display (Prev Record). But I want the subform not to embark on a new record (so that by Prev & Next buttons are disabled and there is a New button if the User wants to create a new record). Any ideas?
Dave
This is the after update for the combo:
Private Sub cboProjectID_AfterUpdate()
On Error GoTo cboProjectID_AfterUpdate_Error
' Set the Filter
Me.Filter = "ProjectID = " & Me!cboProjectID
Me.FilterOn = True
' Requery Form
Forms![fmTasks].Requery
' Requery SubForm
Me!fmTasksSubForm.Requery
On Error GoTo 0
Exit Sub
cboProjectID_AfterUpdate_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboProjectID_AfterUpdate of VBA Document Form_fmTasks"
End Sub
And this is my button controls:
Private Sub Form_Current()
' This is to control the PREVIOUS and NEXT Buttons
Dim recClone As Recordset
Dim intNewRecord As Integer
' Make a clone of the recordset underlying the form so we can move
' around that without affecting the form's recordset
On Error GoTo Form_Current_Error
Set recClone = Me.RecordsetClone
' If this is a new record then disable the <NEXT> button and enable
' the <PREV> button and then exit the procedure
If Me.NewRecord Then
btnPrevious.Enabled = True
btnNext.Enabled = False
Exit Sub
End If
' IT NEVER REACHES HERE (MY PROBLEM)
' If we reach here, we know we are not in a new record so need to
' see if there are no records. If this is the case then we must
' disable both the <PREV> and <NEXT> buttons.
If recClone.RecordCount = 0 Then
btnPrevious.Enabled = False
btnNext.Enabled = False
Else
' Synchronise the current pointer in the two recordsets
recClone.Bookmark = Me.Bookmark
' If there are records, see if we are on the first record.
' If so, we should disable the <PREV> buttons.
recClone.MovePrevious
btnPrevious.Enabled = Not (recClone.BOF)
recClone.MoveNext
' And then check whether we are on the last record.
' If so, we should disable the <NEXT> buttons.
recClone.MoveNext
btnNext.Enabled = Not (recClone.EOF)
recClone.MovePrevious
End If
' Finally, we close the cloned recordset
recClone.Close
On Error GoTo 0
Exit Sub
Form_Current_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_fmTasks"
End Sub
