Error message when linking to a form with no records

[LeighW]

Hi all,

I have a form, Form 1 with primary key PK_ID
I have a second form, Form 2 with foreign key FK_ID

I link through to Form 2 using the field PK_ID.

It is also possible to open Form 2 from Form 1 filtered by PK_ID again in add mode which allows the user to add a new record in Form 2 that automatically links to the record from Form 1.

What I'd like is an error message which traps the user on Form 1 if there are no related records in Form 2.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
If Me.RecordsetClone.RecordCount = 0 Then
        Cancel = True
        MsgBox "Please add a new Sub-Argument first before linking"
End If
Exit_Form_Open:
    Exit Sub

Err_Form_Open:
    MsgBox Err.Description, vbCritical & vbOKOnly, _
    "Error Number " & Err.Number & " Occurred"
    Resume Exit_Form_Open

This code in the OnOpen event of Form 2 works perfectly for that method, however, I'd like the trap NOT to work if the user wants to ADD a new record.

I tried simply adding the If Not Me.NewRecord part to the code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
If Not Me.NewRecord Then
    If Me.RecordsetClone.RecordCount = 0 Then
        Cancel = True
        MsgBox "Please add a new Sub-Argument first before linking"
    End If
    End If

Exit_Form_Open:
    Exit Sub

Err_Form_Open:
    MsgBox Err.Description, vbCritical & vbOKOnly, _
    "Error Number " & Err.Number & " Occurred"
    Resume Exit_Form_Open
End Sub

But it still doesn't work.
Any ideas?

Thanks for any help,

Leigh

[MikeTheBike]

Hi

You have talked about Forms but not Tables.

If Form1 is based on Table1 and Form2 is based on Table2 then in the code Sub procedure the that opens Form2 I would add a Domain DCount() function to see if any record exist, and if not then don't open Form2 ie. something like this

    If DCount("FK_ID", "Table2", "FK_ID = " & Me.PK_ID) = 0 Then
        MsgBox "Please add a new Sub-Argument first before linking"
    Else
        DoCmd.OpenForm "Form2", acNormal, , "FK_ID = " & Me.PK_ID
    End If

MTB

p.s. this assumes PK_ID is numeric ?

[LeighW]

Hi MTB,

Thanks very much for the reply.

Yeah sorry I forgot to add the info on the tables but what you have said is correct and PK_ID is numeric yes.

I've tried the DCount method and it works great. Forgot about using a DCount to be honest!

Very grateful for the help,
Thank You!