Close form if less that 2 records

[dbl]

Hi I have a form called "OpenAccounts" that opens on a after update
procedure, from my Incident Data form. The "OpenAccounts" form is linked to
a query which finds matching open accounts, that part works fine, apart from
it fines the account that I have just opened. The qry searches by
Registration Number on the Incident Data Form.

What I want to do is close the form "OpenAccounts" if there is less than 2
records found, how do I do that? I have entered the following on the on
open event procedure but the Me.Count(*) part isn't right. How do you count
the records open in a form?

Private Sub Form_Open(Cancel As Integer)
If Me.Count(*) < 2 Then
DoCmd.Close
End If
End Sub

Bob

 

[Allen Browne]

To prevent opening the form if there are too few records, cancel the form's
Open event.

Example:

Private Sub Form_Open(Cancel As Integer)
Dim bClose as Boolean
With Me.RecordsetClone
If .RecordCount = 0 Then
bClose = True
Else
.MoveLast
If .RecordCount < 2 Then
bClose = True
End If
End If
End With

If bClose Then
Cancel = True
MsgBox "Too few records"
End If
End Sub

 

[dbl]

Allen that works fine (Thank You) but I get an "Open Action Form Failed To
Open Error Message" when the form has less than 1 record how do I build in
error codes into the following function so that doesn't happen or should
there be an amendment to the code ?

Private Sub Field137_AfterUpdate()
Dim Docname As String
Dim LinkCriteria As String

Docname = "frmOpenCustomers"

DoCmd.OpenForm Docname, , , LinkCriteria

End Sub

Thanks for your help it is really appreciated.

Bob

 

[Douglas J Steele]

You need to put error trapping in.

Unfortunately, off the top of my head, I don't remember the specific error
number that's raised: you'll have to figure it out yourself.


Private Sub Field137_AfterUpdate()
On Error Goto Err_Field137_AfterUpdate

Dim Docname As String
Dim LinkCriteria As String

Docname = "frmOpenCustomers"

DoCmd.OpenForm Docname, , , LinkCriteria

End_Field137_AfterUpdate:
Exit Sub

Err_Field137_AfterUpdate:
If Err.Number = nnnn Then
Resume Next
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume End_Field137_AfterUpdate
End If

End Sub

Replace nnnn with the appropriate value.

 

[dbl]

Thanks Allen that works fine, thanks again for your help.

Bob