Keep form from opening if no data? 1

[Turb]

All,
I am at my wits end trying to get this to work...
I have a form, based on a query (that requests user input before continuing).
I would like to keep the form from opening if the user does not enter information in the intital query (clicks "ok" with no data in the field).
This is simple to do with reports, but I can't seem to get this working on my forms. Please help!

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[HarleyQuinn]

What have you got so far?

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[ZmrAbdulla]

Try

Private Sub Form_Load()
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "No data to display... Closing the form"
        DoCmd.Close
    End If
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[Turb]

Harley,
Thank you for responding.
I have tried the suggestions in thread702-789119 and in thread702-988240 and many of my own variations thereof. But nothing seems to work. I am using Acc97 BTW.
Thanks again

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[Turb]

ZmrAbdulla,
Amazing! This is so similar the the code from thread789119

Private Sub Form_Open(Cancel As Integer)
Cancel = Me.RecordsetClone.RecordCount = 0
End Sub

But yours works like a charm!
Thank you very much!
A star for you!

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[TheAceMan1]

How are ya Turb . . . . .

Can't be sure without knowing whats returned from the query when no data is entered!

[blue]Post the query![/blue]

With the [blue]RecordSource[/blue] of the form set to "", in the [purple]Open event[/purple] of the form, Input boxes are setup to receive data entry. If validation of the data fails, opening of the form is aborted with the [blue]Cancel property[/blue], otherwise, [purple]data entry is concatenated properly into the SQL, and the RecordSource of the form is set to the SQL.[/purple]

[purple]Your thoughts?[/purple]

See Ya! . . . . . .

 

[Eupher]

Turb,

Private Sub Form_Open(Cancel As Integer)
Cancel = Me.RecordsetClone.RecordCount = 0
End Sub

...works for me in Access 2002. But I prefer the If..Then so as to display a MsgBox. Just curious, what happens if you put

Cancel = True

in your If..Then block? Note: this will only work in the Open event, the Load event does not include the Cancel argument.

Ken S.

 

[Turb]

AceMan,
SQL view of the Query as follows:

SELECT DMFTable.DMFNO, DMFTable.PARTNO, DMFTable.DRAWINGNO, DMFTable.REVNO, DMFTable.LOTNO, DMFTable.TOTQTY, DMFTable.QTYINSP, DMFTable.QTYREJ, DMFTable.INSPTESTBY, DMFTable.PARTDESC, DMFTable.DATERECD, DMFTable.DATETODAY, DMFTable.IP, DMFTable.TP, DMFTable.SUPPDEPT, DMFTable.POJOBNO, DMFTable.ACCEPTCRITA, DMFTable.NONCONCRITA, DMFTable.MATLTRANS, DMFTable.NONCONFCLEARED, DMFTable.CLEAREDDATE, DMFTable.COQSTANCOST, DMFTable.COQMATLCOST, DMFTable.COQLABCOST, DMFTable.COQSORTCOST, DMFTable.COQREWORKCOST, DMFTable.COQSORTTIME, DMFTable.COQREWORKTIME, DMFTable.COQTOTCOST, DMFTable.MRBDEUAI, DMFTable.MRBDEREWORK, DMFTable.MRBDEREPAIR, DMFTable.MRBDESORT, DMFTable.MRBDENU, DMFTable.MRBDEINIT, DMFTable.MRBDEDATE, DMFTable.MRBPURUAI, DMFTable.MRBPURREWORK, DMFTable.MRBPURREPAIR, DMFTable.MRBPURSORT, DMFTable.MRBPURNU, DMFTable.MRBPURINIT, DMFTable.MRBPURDATE, DMFTable.MRBPCUAI, DMFTable.MRBPCREWORK, DMFTable.MRBPCREPAIR, DMFTable.MRBPCSORT, DMFTable.MRBPCNU, DMFTable.MRBPCINIT, DMFTable.MRBPCDATE, DMFTable.MRBPRODUAI, DMFTable.MRBPRODREWORK, DMFTable.MRBPRODREPAIR, DMFTable.MRBPRODSORT, DMFTable.MRBPRODNU, DMFTable.MRBPRODINIT, DMFTable.MRBPRODDATE, DMFTable.MRBMEUAI, DMFTable.MRBMEREWORK, DMFTable.MRBMEREPAIR, DMFTable.MRBMESORT, DMFTable.MRBMENU, DMFTable.MRBMEINIT, DMFTable.MRBMEDATE, DMFTable.MRBQAUAI, DMFTable.MRBQAREWORK, DMFTable.MRBQAREPAIR, DMFTable.MRBQASORT, DMFTable.MRBQANU, DMFTable.MRBQAINIT, DMFTable.MRBQADATE, DMFTable.MRBINSTRUC, DMFTable.SPECINSTRUC, DMFTable.CORRACT, DMFTable.CORRACTIMP, DMFTable.CORRACTDATE, DMFTable.REMARKS, DMFTable.QEREVIEW, DMFTable.QEREVIEWSIGN, DMFTable.QEREVIEWDATE
FROM DMFTable
WHERE (((DMFTable.DMFNO) Like [Enter DMF number like "##-C####"]));

Eupher, I like the If..Then as well.
I actually have the code open another form which has it's own underlaying code on a "re-try" button.
See below:
Code from ZmrAbdulla (altered)

    If Me.RecordsetClone.RecordCount = 0 Then
        DoCmd.Close
        DoCmd.OpenForm "DMFWarning4frm"
    End If

The code under the "Try Again" button of my warning form:

On Error GoTo Err_TRYAGAIN_Click

    Dim stDocName As String
    
    DoCmd.Close acForm, "DMFWarning4frm"
    stDocName = "ModifyDiscFormDMF"
    DoCmd.OpenForm stDocName, acNormal


Exit_TRYAGAIN_Click:
    Exit Sub

Err_TRYAGAIN_Click:
    MsgBox err.Description
    Resume Exit_TRYAGAIN_Click

All of this keeps my users from opening the form just to print it out blank and fill it in by hand, thus by-passing the database.
Thank you all, for all you help!

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[ZmrAbdulla]

You could have another method also. Check the Record count of the query then open form/warning form depending to the count. This is faster than the other method because you don't have to wait the form to load and check the Recordset Count.

Private Sub cmdOK_Click()
    If DCount("[DMFNO]", "QueryName") = 0 Then
        DoCmd.OpenForm "DMFWarning4frm"
    Else
        DoCmd.OpenForm "YourFormToOpen"
    End If
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[seducedbycodes]

Hi, I just tried to implement the code given above in cmdOK_Click() but get Run-time error '2001':
You canceled the previous operation on my DCount line:

If DCount("Name", "QUOTE") = 0 Then

 

[ZmrAbdulla]

DCount("*", "QueryName") will bring the count of records.
Also "Name" is a reserved word. If you have a field name named "Name" then you need to replace it with some prefix like "FirstName"
Search "DCount Function" in the VBA file

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.