Purchase order and enforcing standards. help please

[Ade F]

Hoping someone could help me with a simple or not so simple problem here. I have a purchase order form that using a form has the top half populated with the order details such as ORDER ID (using a table named ORDERS) , Date, time etc and in a sub form I have the order details derived from a table called 'order details' the order details form has three table entries

1. Order ID - derived from ORDER ID table.
2. Product Name - From the products table
3. Quantity - direct entry into Order details table / sub form.

My problem at the moment is that an employee is able to fill in the top half of the form and exit without having filled in details for the products within the sub form thus leaving and ORDERID generated but no actual items related to the sub form. It seems not to be just a matter of setting the sub form parameters to Required - 'yes' as the ORDER ID in the sub form is not created until someone actually enters a product entry thus the creation of the ORDERS table entry but not the sub form.

My question is this ,is there a way of enforcing in some way that upon the ORDERS table ID creation an item product item and quantity must be entered.

Alternatively or additional to this is there a way of an employee having filled out the top half of the ORDER and then wishes to exit clicking a button and exiting the form without the initial order information being saved.

Help would be greatly appreciated, as I don't wish for an ORDER generated without any products assigned.

I may be committing unholy access sins here so please enlighten me as to my best approach to solving this problem. Shoud you require more information then please do not hesitate to ask.

Regards

Ade

My order looks as follows

 

[Rich]

It can be done to a point a form level but it's not perfect, the easiest way is to run a query and look for missing child related records, an unmatched query will do that for you. Post back if you need further help with either method

 

[Ally]

One way of solving the subform query is:

In design view of your subform, place a textbox in the form footer called for example, txtCount. In the textbox write: =Count(*)

This will count how many records are there when it's open.

Then on the close / exit button you have, you'll need some code along the lines of:

    If Me![NmOfYour subform].Form![txtCount] = 0 Then
                msgbox "No orders created.  Please enter order(s) _ 
                        etc, etc, etc ...", _
                vbOKOnly, "Error"

            Exit Sub
            
            Else
                Close code here ...
                
            End If

Hope this works for you.

Not sure about the saving issue but someone else'll be able to help you.

 

[Ade F]

Forgive my ignorance

I'm not quite on the ball when it comes to VB

Could you please peruse my code for the exit button below and tell me what I have done wrong.

At the moment upon pressing the exit button it gives me the error

"compile error invalid use of property"


-------------------------

Private Sub Command86_Click()

On Error GoTo Err_Command86_Click

If Me![Order_Guts].Form![txtCount] = 0 Then
MsgBox "No orders created. Please enter order(s)" _

vbOKOnly , "Error"

Exit Sub

Else
DoCmd.Close

End If

Exit_Command86_Click:
Exit Sub

Err_Command86_Click:
MsgBox Err.Description
Resume Exit_Command86_Click

End Sub

 

[Ally]

You're missing a comma after order(s)"

If Me![Order_Guts].Form![txtCount] = 0 Then
MsgBox "No orders created. Please enter order(s)", ...

 

[Ade F]

Still having trouble sorry

Is the following code correct?

Private Sub Command134_Click()

If Me![Order_Guts].Form![txtCount] = 0 Then

MsgBox "No orders created. Please enter order(s)", vbOKOnly, "Error"

Exit Sub

Else
DoCmd.Close

End If

Exit_Command86_Click:
Exit Sub

Err_Command86_Click:
MsgBox Err.Description
Resume Exit_Command86_Click

End Sub

If so then my system keeps giving me the message

runtime error 2465

cant find the field Order_guts refered to in your expression

It is as if it cannot see the sub form.

I'm stuck... or is there another way araound this problem?

 

[Ally]

VB isn't my strong point and I can't figure it out just by looking at it - are you able to post your db? (unless any of the "not so confused with VBers" else posts with a solution in the meantime)!

Is Order_Guts exactly the name of your subform?

 

[fuzzygeek]

Possible solution or just blabbering

"cant find the field Order_guts refered to in your expression"

The following is an example from the subform object in the help screen:

(Example
The following example uses the Form property to refer to a control on a subform.

Dim curTotalAmount As Currency

curTotalAmount = Forms!Orders!OrderDetails.Form!TotalAmount)


Notice that the form is Forms!<name of form>!<Name of subform on form>.Form!<name of control on subform>. Is Order_guts the name of the subform on the form ... or the name of the form? It seems one of the two is not included.

 

[Rich]

There are problems with this method, as soon as you leave the main form to enter the sub your main form record has been saved, there is also nothing in the code to prevent users from entering a record in the main form and moving to another new record on the main.
The only way, and again it's not perfect is to use a CanClose boolean field and toggle it's property from the subform, you also need to add your own navigation buttons, again trapping the record movement depending on the status of CanClose

 

[snyderman]

macro?

just a quick fix, perhaps a macro to launch the subform upon save or close. The only problem with that is the circumstance where the user HAS entered the data.

 

[Ade F]

Sub form integrity

Sorry for the delay Order_Guts (apologies for the name) is the name of the sub form.

It appears from different people that this one will have to be given a lot more thought by myself in order to enforce some kind of standard here. This is not a huge problem at the moment as the DB is in it's infancy but given time, blank entries could be detrimental to records keeping in regards to future reference and manufacturing.

Many thanks to Rich for the help in the past and present.

Again any more work around by example would be greatly appreciated.

Regards

Adrian