Subform as Datasheet but need to see the Header, Need Ideas 2 Part Question.

[burrina]

Hi, I am in a quandary! I have a subform viewed as a datasheet for entering products for my order. I need to be able to use message boxes and command buttons for the criteria I need. Do I add a main form to it, make it a popup or what? The subform is on my Main form where I do Order Entry. On this form I need a vba message box to run before they agree to finalize the order.Here is my code for it; The form is a Order Details form for entering products. Here is a pic og main form with subform on it.


Option Compare Database


Private Sub Command22_Click()
Dim AddItemsAsInventorymsgbox As String
AddItemsAsInventorymsgbox = MsgBox("Do you want to Allocate these items to inventory?", vbInformation + vbYesNo, "OrderDetailsSubform")

If (AddItemsAsInventorymsgbox = vbYes) Then


DoCmd.SetWarnings False
[Post].Enabled = True
[Post].Visible = True
DoCmd.OpenQuery "UpdateProductsTableFromOrderDetailsTableProdu cts"
DoCmd.SetWarnings True
DoCmd.Save
End If
End Sub

[Rod]

Hi,

I'm not exactly sure what your problem is. At first glance your code looks fine - you will prompt the user for confirmation before updating the products table from the order details. I assume the "Post" field controls this update and avoids duplication. What is your quandary?

However I have a few concerns regarding the logic. There are three things that could happen: order line added; order line deleted; order line changed. Are you catering for all three situations? A query seems a cumbersome way of going about this and I would hate to be asked to write the SQL (even if it's possible to do it in one step). Also you enable the "Post" field which suggests it is the user that manipulates the check box - something I would expect to be done automatically. I may of course have totally misunderstood the requirement.

Back to your original question, there are a number of ways of doing what you want and you seem to have thought of most of them. The way you choose depends very much on the look and feel of your GUI and the complexity of the additional controls (buttons, textboxes, etc.) that you need.

1. Add the controls to the main form - yes, by adroit use of boxes and lines you can make these controls look as if they exist on the subform. The main form 'knows' about the subform and can address all controls, methods and properties of the subform. The only thing to watch is that by clicking on one of these controls, you move the focus away from the subform and thereby trigger an update of the subform - something you do not always want to happen but I can see no problem in your case; it's something you want to happen.
2. Embed your subform in an intermediate main form which contains your additional controls. This new form becomes the subform of your main form and the order detail is 'pushed' one level further down the hierarchy. This is simply a more comlex form of 1 above and I would only suggest it if you want to reuse the subform mechanism somewhere else.
3. You could use - as you have - one or more message boxes. This is probably the simplest to implement and you can display up to three action buttons in the box (e.g. Yes, No and Cancel). You could trigger the display of this message box from the subform's control On Exit event.
4. You could design a dialog form if the situation demands more complexity than can be handled by standard message boxes. There's a 'trick' to this, so if you want to implement this method get back to me.
5. You could design a Shortcut Menu that is displayed after a right-click. This is not that easy to implement and requires some experience with VBA and objects.
6. ...

All this is because you are using a Datasheet as your subform. Does it have to be a Datasheet? Datasheets are wonderful for dealing with lists of thousands of records - but for order detail?

In summary, there is no one best solution for your quandary and the choice is, in the last analysis, yours. A solution that works is always preferrable to one that doesn't.

[burrina]

I see this as the only efficient way for a user to add multiple products at a time to the order. I am running into some complex situations though. Such as subtracting form inventory and adding to inventory which I have never done. Customer Order, subtract, Purchase Order, add to inventory. I am only maybe half way into the design and implementation. Yes, message boxes are the way to go. Next step at this point is to test the query to see if it subtracts the correct inventory items from the form. The checkbox has since been read only and code fired from command button. I am NOW using a continuous form which looks like a datasheet. I will get back with you after my brain has had time to cool off.

Thanks,

[Missinglinq]

In response to the 'Subform as Datasheet but need to see the Header' portion of your post, the usual way to do this is to change the Form from Datasheet View to Continuous View, then tweaking the Controls, in Design View, until it looks like a Datasheet View Form. Then you can add whatever your heart desires, in the Header Section.

Linq ;0)>

[burrina]

Thanks, did that already.