Open Form "Not Found" Msg

[ECEK]

I have a command button that opens a form to specific.

I want to create a "No policy found" message but can't work out how to do it.

I tried this on the OnOpen of my form.
Could anybody point me i the right direction please ?

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Policy_reference) Then
Exit Sub
Else
DoCmd.Close acForm, "frm_tranche_single_formPS"
MsgBox "Policy Not Found"
End If
End Sub

 

[Minty]

I would do this check on you open form command from the calling form.

Assuming you do.cmd open with a where clause "[Policy_reference] = '" & someControlOnYourForm & "'"

Simply check for the policy before opening the form?

 

[arnelgp]

Do it in the click event of the button, use dcount against the table. If dcount is not 0 then open the form else show the message.

 

[ECEK]

Thanks Minty
I'm unsure how to check if the policy_reference exists on the opening form prior to opening it.

My understanding was that you had to open the form before you could check if the [policy_reference] existed.

 

[Minty]

As Arnelgp suggested do a DCount

DCount("[policy_reference]","YourTable","[policy_reference] = '" & Me.YourPolicyRefControl & "'")

If it's 0 then there isn't one.

 

[ECEK]

Thank you all.
My code is:

Private Sub Command511_Click()

NUM = DCount("[policy_reference]", "tbl_tranches", "[policy_reference] = '" & Me.Text509 & "'")

If NUM = 0 Then
DoCmd.Close acForm, "frm_tranche_single_formPS"
MsgBox "Policy Not Found"
Exit Sub
Else
DoCmd.OpenForm "frm_tranche_single_formPS", acNormal, "", "[Policy_reference] = '" & Text509 & "'"

End If

 

End Sub

Thanks to your help I was able to do some of my own research. Which is the point .

Cheers

 

[Minty]

Glad you have it sorted.
I'm a bit mystified by why you are closing the form in the first part of the code if this command button is the one opening it? Shirley it's not open so doesn't need closing?

 

[ECEK]

Hi Minty
Quite right. It was left over in error from the previous solution. with it not causing an error I was blind to it. It has been removed.
Cheers Again

 

[missinglinq]

...I'm unsure how to check if the policy_reference exists on the opening form prior to opening it....

Just to clarify, you can't/don't, of course! You check it against the RecordSource, in this case, tbl_tranches, that the Form is be based on.

Linq ;0)>

 

[Mark_]

ECEK,

You may want to adopt a naming convention for your controls. Command511 and Text509 convey little information as to WHAT they are for. In two months when you see these again you may spend a fair bit of time trying to remember what they are for.

 

[ECEK]

Thanks Mark
Yes I had become rather lazy. Your advice has been taken.

 

[ECEK]

Missinglinq
Yes that's correct.
I was under the assumption that one calculates to what is on the Form.
The DCount is done on the underlying table. In this case tbl_tranches.