Solved Do not open the form if no Record. (1 Viewer)

theinviter

Registered User.
Local time
Today, 06:57
Joined
Aug 14, 2014
Messages
237
Dears;
I have a list in the form and upon double click open another form and filter to selected record,

i need if NO record , do not open the form and proceed to next .


i tried this code below but it show the message and open the form .
Private Sub List6_DblClick(Cancel As Integer)
On Error GoTo errhandler:
n = 1 / 0 ' cause an error
'Move to the record selected in the control


With Me.MRN_Query1_subform.Form.RecordsetClone
.FindFirst "[Item Code] = '" & Me.List6.value & "'"
If .NoMatch Then
MsgBox "No Record Found!"
DoCmd.OpenForm "MRN_Query1_Delivered", acNormal, , " [Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = " & Me.MRN_Query1_subform.Form![Location_Code]
Else
Me.MRN_Query1_subform.Form.Bookmark = .Bookmark
End If
End With

Exit Sub
errhandler:
' error handling code
Resume Next




so what to do .
 

June7

AWF VIP
Local time
Today, 05:57
Joined
Mar 9, 2014
Messages
5,423
If you don't want form to open then why is the command in the .NoMatch True result?
When would you expect form to open? Because if there is a record, code goes to record in subform, not opens a form.
 

theinviter

Registered User.
Local time
Today, 06:57
Joined
Aug 14, 2014
Messages
237
If you don't want form to open then why is the command in the .NoMatch True result?
When would you expect form to open? Because if there is a record, code goes to record in subform, not opens a form.
if no, then will open the form and show the record in another form. but if the record not present in another form then only to show message .
 

onur_can

Active member
Local time
Today, 06:57
Joined
Oct 4, 2015
Messages
180
You have written the command that opens the form when there is no registration. Of course it will open. Use Exit Sub instead of this command.
Code:
DoCmd.OpenForm "MRN_Query1_Delivered", acNormal, , " [Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = " & Me.MRN_Query1_subform.Form![Location_Code]
remove.
 

theinviter

Registered User.
Local time
Today, 06:57
Joined
Aug 14, 2014
Messages
237
You have written the command that opens the form when there is no registration. Of course it will open. Use Exit Sub instead of this command.
Code:
DoCmd.OpenForm "MRN_Query1_Delivered", acNormal, , " [Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = " & Me.MRN_Query1_subform.Form![Location_Code]
remove.
i need this form, what i mean if no record in this form then do not open
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:57
Joined
May 7, 2009
Messages
19,169
use DCount() to check if there is at least a record that will meet the criteria:

If DCount("1", "yourQuery", " [Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = " & Me.MRN_Query1_subform.Form![Location_Code]) <> 0 Then
DoCmd.OpenForm "MRN_Query1_Delivered", acNormal, , " [Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = " & Me.MRN_Query1_subform.Form![Location_Code]
End If
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 28, 2001
Messages
26,999
As everyone has suggested, you must test for the criteria BEFORE you decide to open the form. Here is why: Once you start to open the form, the first event is the Form_Open event, which has a "Cancel" option. But the form's controls have not been loaded yet (see Form_Load event) and the recordset hasn't been fully evaluated yet (see Form_Current event). So you COULD try to establish a record count for your form's recordset to see if you have any records and implement the Cancel if there are none. If you cannot determine that before you do the Exit Sub from the Form_Open event, you will have to go through the Form_Load and Form_Current events before you can reasonably close the form. OR you can avoid the whole mess by testing BEFORE you open the form, and just not open it if the external test says it would return with 0 records.
 

Users who are viewing this thread

Top Bottom