Stop a form from opening problem

[papic1972]

I have a command button (command187) that opens a form. Before the user can enter the form, a pop-up box is produced that requires a date to be entered. This gets passed to the query & hence opens the form with the required data.

If no date is entered & 'cancel' is selected on the pop-up box, the form still opens.


This is the code on the command button:



On Error GoTo Err_Command187_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAllOrdersEnteredinaDay"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command187_Click:
Exit Sub

Err_Command187_Click:
MsgBox Err.Description
Resume Exit_Command187_Click



Does anyone know how I could stop the form from opening in this instance?

 

[SarahG]

Hi,
I presume that if no date is specified, then an empty form is displayed? If so, you could try the following code in the Open event of the form:

Private Sub Form_Load()
If Me.Recordset.RecordCount = 0 Then
MsgBox "There are no records to display", vbInformation, "No data"
DoCmd.Close
End If
End Sub

Which will display the message above and basically cancel the opening of the form.

 

[TheAceMan1]

How are ya papic1972 . . .

Apparently your using a [blue]Parameter[/blue] query.

Be aware: the query runs 1st, just before the form is actually loaded.

Post the [blue]SQL[/blue] of the query!

See Ya! . . . . . .

 

[papic1972]

Hi TheAceMan1,


Here is my query:


SELECT tblOrderInput.[Order Taken By], tblOrderInput.OrderID, tblOrderInput.TBAReleaseNumber, tblOrderInput.OrderEntryDate, tblOrderInput.EnteredBy, tblOrderInput.Customer, tblOrderInput.ContactName, tblOrderInput.ContactNo1, tblOrderInput.ContactNo2, tblOrderInput.SiteTitle, tblOrderInput.[Street Address], tblOrderInput.Suburb, tblOrderInput.MapRef, tblOrderInput.DeliveryInstructions, tblOrderInputPrelim.ReqDate, tblOrderInputPrelim.ActualDate, tblOrderInputPrelim.Supplier, tblOrderInputPrelim.Type, tblOrderInputPrelim.SubType, tblOrderInputPrelim.ProductDescription, tblOrderInputPrelim.Qty, tblOrderInputPrelim.Units, tblOrderInputPrelim.NoofPacks, tblOrderInputPrelim.TransportID, tblOrderInputPrelim.SupplierYardNo, tblOrderInputPrelim.SupplierPickupNumber, tblOrderInputPrelim.DateStockOrdered, tblOrderInputPrelim.ScheduledPickupDate, tblOrderInputPrelim.[PrestigeBuyPrice(exGST)], tblOrderInputPrelim.[PrestigeSellPrice(exGST)], tblOrderInputPrelim.[LocalCartage(exGST)], tblOrderInputPrelim.[InterstateCartage(exGST)], tblOrderInput.Comments, tblOrderInputPrelim.JDNumber, tblOrderInput.[Truck Type], tblOrderInput.[Street Address], tblSupplier.Supplier, tblOrderInputPrelim.SupplierID, tblTransport.Transport, tblOrderInputPrelim.InterstateTPTID, tblOrderInputPrelim.InterstateSupplierYard, tblOrderInputPrelim.InterstateSupplierID, tblOrderInputPrelim.JDDriverID, tblOrderInputPrelim.OrderedWith, tblOrderInput.AccountorCashSaleID, tblOrderInput.[Customer Purchase Order ID], tblOrderInputPrelim.StockSource, tblInterstateTransport.InterstateTPT, tblOrderInputPrelim.DelivYorN, tblOrderInputPrelim.ProductAvailDate, tblOrderInput.CancelledOrder, tblOrderInput.DeliveryInstructions, tblOrderInput.JobNo, tblOrderInput.CustomerType, tblOrderInput.MiscellaneousInfo, tblOrderInput.PrimaryContactName, tblOrderInput.ContactNo, tblOrderInput.FaxNo, tblOrderInput.EMailAddress, tblOrderInput.InvoiceAddress, tblOrderInput.InvoiceSuburb, tblOrderInput.State, tblOrderInput.Postcode, tblCustomer.WhoseCustomer, tblOrderInput.CreditCardType, tblOrderInput.CreditCardExp, tblOrderInput.CreditCardNumber, tblOrderInput.POBox, tblOrderInput.Mobile, tblOrderInputPrelim.ProductDescriptionComments, tblOrderInput.TimeofInput, ([PrestigeSellPrice(exGST)]*[Qty])-(([PrestigeBuyPrice(exGST)]*[Qty])) AS Profit, (([PrestigeSellPrice(exGST)]*[Qty])-([PrestigeBuyPrice(exGST)]*[Qty]))/([PrestigeBuyPrice(exGST)]*[Qty]) AS Margin, ([PrestigeSellPrice(exGST)]*[Qty]) AS TotalSell, ([PrestigeBuyPrice(exGST)]*[Qty]) AS TotalBuy
FROM tblCustomer RIGHT JOIN (tblInterstateTransport RIGHT JOIN (tblOrderInput LEFT JOIN (tblTransport RIGHT JOIN (tblSupplier RIGHT JOIN tblOrderInputPrelim ON tblSupplier.SupplierID = tblOrderInputPrelim.SupplierID) ON tblTransport.TransportID = tblOrderInputPrelim.TransportID) ON tblOrderInput.OrderID = tblOrderInputPrelim.OrderID) ON tblInterstateTransport.InterstateTPTID = tblOrderInputPrelim.InterstateTPTID) ON tblCustomer.Customer = tblOrderInput.Customer
WHERE (((tblOrderInput.TBAReleaseNumber) Not Like "*BULK")) OR (((tblOrderInput.TBAReleaseNumber) Is Null));


I also have the following macro run in the "On Open" event:

[Enter Required Entry Date]=[OrderEntryDate] And [CancelledOrder]=False

 

[TheAceMan1]

[blue]a pop-up box is produced that requires a date to be entered. [purple]This gets passed to the query[/purple] & hence opens the form with the required data.[/blue]

I scanned the SQL a number of times and don't see where anything is passed to the query.

You need to point this out!

See Ya! . . . . . .

 

[papic1972]

Hi TheAceMan1,

You actually just gave me a brainwave!!

I got rid of the macro & fixed the code:

SELECT tblOrderInput.[Order Taken By], tblOrderInput.OrderID, tblOrderInput.TBAReleaseNumber, tblOrderInput.OrderEntryDate, tblOrderInput.EnteredBy, tblOrderInput.Customer, tblOrderInput.ContactName, tblOrderInput.ContactNo1, tblOrderInput.ContactNo2, tblOrderInput.SiteTitle, tblOrderInput.[Street Address], tblOrderInput.Suburb, tblOrderInput.MapRef, tblOrderInput.DeliveryInstructions, tblOrderInputPrelim.ReqDate, tblOrderInputPrelim.ActualDate, tblOrderInputPrelim.Supplier, tblOrderInputPrelim.Type, tblOrderInputPrelim.SubType, tblOrderInputPrelim.ProductDescription, tblOrderInputPrelim.Qty, tblOrderInputPrelim.Units, tblOrderInputPrelim.NoofPacks, tblOrderInputPrelim.TransportID, tblOrderInputPrelim.SupplierYardNo, tblOrderInputPrelim.SupplierPickupNumber, tblOrderInputPrelim.DateStockOrdered, tblOrderInputPrelim.ScheduledPickupDate, tblOrderInputPrelim.[PrestigeBuyPrice(exGST)], tblOrderInputPrelim.[PrestigeSellPrice(exGST)], tblOrderInputPrelim.[LocalCartage(exGST)], tblOrderInputPrelim.[InterstateCartage(exGST)], tblOrderInput.Comments, tblOrderInputPrelim.JDNumber, tblOrderInput.[Truck Type], tblOrderInput.[Street Address], tblSupplier.Supplier, tblOrderInputPrelim.SupplierID, tblTransport.Transport, tblOrderInputPrelim.InterstateTPTID, tblOrderInputPrelim.InterstateSupplierYard, tblOrderInputPrelim.InterstateSupplierID, tblOrderInputPrelim.JDDriverID, tblOrderInputPrelim.OrderedWith, tblOrderInput.AccountorCashSaleID, tblOrderInput.[Customer Purchase Order ID], tblOrderInputPrelim.StockSource, tblInterstateTransport.InterstateTPT, tblOrderInputPrelim.DelivYorN, tblOrderInputPrelim.ProductAvailDate, tblOrderInput.CancelledOrder, tblOrderInput.DeliveryInstructions, tblOrderInput.JobNo, tblOrderInput.CustomerType, tblOrderInput.MiscellaneousInfo, tblOrderInput.PrimaryContactName, tblOrderInput.ContactNo, tblOrderInput.FaxNo, tblOrderInput.EMailAddress, tblOrderInput.InvoiceAddress, tblOrderInput.InvoiceSuburb, tblOrderInput.State, tblOrderInput.Postcode, tblCustomer.WhoseCustomer, tblOrderInput.CreditCardType, tblOrderInput.CreditCardExp, tblOrderInput.CreditCardNumber, tblOrderInput.POBox, tblOrderInput.Mobile, tblOrderInputPrelim.ProductDescriptionComments, tblOrderInput.TimeofInput, ([PrestigeSellPrice(exGST)]*[Qty])-(([PrestigeBuyPrice(exGST)]*[Qty])) AS Profit, (([PrestigeSellPrice(exGST)]*[Qty])-([PrestigeBuyPrice(exGST)]*[Qty]))/([PrestigeBuyPrice(exGST)]*[Qty]) AS Margin, ([PrestigeSellPrice(exGST)]*[Qty]) AS TotalSell, ([PrestigeBuyPrice(exGST)]*[Qty]) AS TotalBuy
FROM tblCustomer RIGHT JOIN (tblInterstateTransport RIGHT JOIN (tblOrderInput LEFT JOIN (tblTransport RIGHT JOIN (tblSupplier RIGHT JOIN tblOrderInputPrelim ON tblSupplier.SupplierID = tblOrderInputPrelim.SupplierID) ON tblTransport.TransportID = tblOrderInputPrelim.TransportID) ON tblOrderInput.OrderID = tblOrderInputPrelim.OrderID) ON tblInterstateTransport.InterstateTPTID = tblOrderInputPrelim.InterstateTPTID) ON tblCustomer.Customer = tblOrderInput.Customer
WHERE (((tblOrderInput.TBAReleaseNumber) Not Like "*BULK") AND ((tblOrderInput.OrderEntryDate)=[Enter Date Required])) OR (((tblOrderInput.TBAReleaseNumber) Is Null) AND ((tblOrderInput.OrderEntryDate)=[Enter Date Required]));


It now works as it should.

Thanks a million!!!!!