Open Form "Not Found" Msg (1 Viewer)

ECEK · Oct 2, 2017

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 ?

Code:

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 · Oct 2, 2017

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 · Oct 2, 2017

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 · Oct 2, 2017

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 · Oct 2, 2017

As Arnelgp suggested do a DCount

Code:

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

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

ECEK · Oct 2, 2017

Thank you all.
My code is:

Code:

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 · Oct 2, 2017

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 · Oct 9, 2017

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 · Oct 9, 2017

ECEK said:
...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_ · Oct 9, 2017

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 · Oct 16, 2017

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

ECEK · Oct 16, 2017

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.

Open Form "Not Found" Msg (1 Viewer)

ECEK

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

missinglinq

AWF VIP

Mark_

Longboard on the internet

ECEK

Registered User.

ECEK

Registered User.

Similar threads

Users who are viewing this thread