Open form automatically if 1 record found?

[Tony Pokkinen]

I have a form (Customers Line) with parameter value to find customers.
How to make Access to open automatically customer detail form (Customers Sgl) if ONLY 1 record found?

[Adam Tippelt]

You could do a DCount to find how many records there are, and then if the answer is 1, open the form.

DCount(Expressi on, Domain, [Criteria])

In your case you don't actually need a criteria as you just want to know if one record exists, so it would something like this:

If DCount("Customer_ID", "YourCustomerTable") = 1 then
    DoCmd.OpenForm "Customers Sgl"
Else
    <DO SOMETHING ELSE>
End If

Hope that helps.

Adam.

[Tony Pokkinen]

THANKS ADAM!
Where and how should I put this? Macro? Or?

[NeoPa]

Interesting question, and particularly interesting is where the code needs to go to work.

I have recently done some work in this area myself and I would recommend putting the code in the Form_Timer() event procedure. The reason for this is that I find that certain properties related to the form's recordset are not available within the Form_Open() event procedure. Not yet initialised I suspect. However, if the Timer is triggered then this code only runs after the form has been completely initialised and all such code has completed. If the timer fires before the other code is complete it simply flags that it should run, but doesn't start until existing executing code is complete (Very much like a DPC or Deferred Procedure Call within Windows itself).

The basic code to support this is :

Private Sub Form_Open(Cancel As Integer)
    With Me
        .OnTimer = "[Event Procedure]"
        .TimerInterval = 50
    End With
End Sub

Private Sub Form_Timer()
    With Me
        .TimerInterval = 0
        .OnTimer = ""
        ' Payload here
    End With
End Sub

This is very general for any code that requires access to, as yet uninitialised, resources of your form.

At this point I would then suggest determining the value of Me.Recordset.Re cordCount. Unfortunately, this is less than straightforward as in some cases, especially with large datasets, this value isn't available until after the record pointer has moved somewhat. I use a procedure in my forms to get around this. NB. This may cause time delays with massive datasets.

Private Function RecCount() As Long
    With Me.Recordset
        If .BOF And .EOF Then
            Call .MoveLast
            Call .MoveFirst
        End If
        RecCount = .RecordCount
    End With
End Function

So, your Payload code within Form_Timer() would be something like :

If RecCount = 1 Then Call DoCmd.OpenForm("[Customers Sgl]")