Pop up box for data entry

[debraj007]

I've researched this and found some similar questions, but not exactly what I
was looking for, hoping someone can help. I have an order form with an order
details subform. On the order details subform, there is a combo box to add a
new product.

After choosing a product from the list, I would like a pop up box to pop up
and allow you to edit product information. (Each product is custom made per
order and there are about 20 checkboxes that pertain to this particular
product in this particular order). My problems are here:

I've tried, after update and on dirty, but can't get the pop up box to
reference the particular product ID that was selected from the combo. Should
I be referencing the order detail id instead?

And, when I close the pop up box, now it actually creates an entirely new
order detail id in the table and the checkboxes are not referencing a
particular product.

Any suggestions? Here's hoping it's something simple I'm overlooking and
that I explained this clearly

Thanks, Deb

 

[Ken Sheridan]

Deb:

As the customization is per product per order you don't want to edit the row
for that product in the Products table. That would change the specification
of the product for all orders, not just the current one. The situation her
is analogous to that for the Unit price of a product in the sample Northwind
database. If you take a look at that you'll see there are UnitPrice columns
in both the Products and OrderDetails tables. In the Order Details Subform
the current unit price is looked up from the Products table by code in the
AfterUpdate event procedure of the ProductID control and assigned to the
UnitPrice control in the subform. This control is bound to the UnitPrice
column in the OrderDetails table, so if the unit price is subsequently
changed in the Products table the prices for each order remain unchanged.

So you need columns equivalent to the relevant ones in your products table
in your order details table. Whether you need to keep them in the Products
table as well depends on whether you want to have current values for them
which act as defaults for each order, so that it’s a question of editing
values assigned in the same way as for the unit price in Northwind, or
whether you simply enter each value from scratch for each product per order.
If the latter then there is no real point having the columns in the Products
table too. Whichever is the case, however, you would have controls in the
subform bound to the columns in question so that you can either enter the
values from scratch or edit the values looked up from products and assigned
to them. You would not need to pop up a separate form.

If you do want to store the current values in the Products table and need
any help with the code assigning them to the controls in the subform post
back with details of the columns in question.

Another thing which sets my antennae twitching is that you have "about 20
checkboxes that pertain to this particular product". This might be perfectly
legitimate, but having a set of Boolean (Yes/No) columns in a table often
points to a design flaw. It suggests that you might be doing what's known as
'encoding data as column headings'. In a relational database data should
only be stored as values at column positions in rows in tables (its called
the 'Information Principle'). What's usually a better design is to have a
related table with a separate row for each value per product per order, i.e.
with a composite foreign key of OrderID and ProductID and columns for the
type of value (the equivalent of your 20 column headings) and the value
itself (the equivalent of the values in your 20 columns). Data entry could
be via a second continuous subform correlated to the first. Without knowing
just what these columns represent I can't at present be more categoric than
that, however, but would be glad to help with implementing this if you post
back with more details of what these 20 columns represent.

Ken Sheridan
Stafford, England

 

[debraj007]

Thanks Ken, I was a little iffy on the setup of those tables, you are right,
I moved things around and now it makes more sense. Now my issue is, how to I
pull the current product id from the order when the pop up screen comes up?
There are bound to be multiple products and my order detail form is
continuous. Thanks, Deb

 

[Ken Sheridan]

Deb:

I don't see why you are using a pop up form at all here. If you've put the
fields in question in the order details table, and that is the table
underlying the subform, then you just need to add controls bound to those
fields to the subform. If you want to assign values looked up from the
products table to them as defaults then you'll need some code along the lines
of that in the Northwind database's order details subform to which I
referred. I'm assuming you haven't gone as far as decomposing the table as I
discussed in the final paragraph of my original reply.

Ken Sheridan
Stafford, England

 

[debraj007]

I would like to use the pop up form for the data entry so my form is not too
cluttered. There is no reason to see all the custom fields on the main order
form once they are entered, as a work order is printed out, and they won't be
accessed again by the user (the only 1 user of this database). And yes, I did
actually decompose the table as you suggested, as I mentioned in my reply

Deb

 

[Ken Sheridan]

Deb:

What you've done is not in fact decomposition of the table as far as I can
tell. That would involve the creation of a new related table of the type I
described in the final paragraph of my original reply, with separate rows
representing your current 20 or so columns. It sounds to me as though you've
added the extra 20 or so columns to your order details table. Anyway, the
following assumes you've done the latter rather than the former.

Firstly you need to create another form bound to the order details table, in
single form view this time. This will be the form you open from your
subform. Lets assume the new form is called frmOrderDetails. Set its
AllowAdditions property to False ('No' in its properties sheet) as you'll
only be using it to edit a record already added in the subform, not add a new
one.

What you have to do is to open the new frmOrderDetails form filtered to the
current record in the subform. Each row in the order details table is
uniquely identified by two columns in combination, one which references the
key of the orders table and one which references the key of the products
table. These would usually be called something like OrderID and ProductID
and be of long integer number data type. The products combo box in the
subform would have ProductID as its ControlSource property, though it would
normally hide the ProductID and show the product name. The OrderID would not
normally be shown on the subform as its value is automatically inserted into
the order details table behind the scenes by the link with the parent form.
So when you open the frmOrderDetails form it needs to be filtered on these
two columns.

You want the frmOrderDetails form to open as soon as you select a product in
the combo box on the subform, so the code will go in the combo box's
AfterUpdate event procedure. Before you can open the frmOrderDetails form at
the current record, however, you need to save the record. So the code for
the AfterUpdate event procedure would go like this:

Dim strCriteria As String

' first ensure that a product has been selected
If Not IsNull(Me.ProductID) Then
' save the current record
Me.Dirty = False

' build criterion to filter frmOrderDetails
' when it is opened
strCriteria = "OrderID = " & Me.OrderID & _
" And ProductID = " & Me.ProductID

' open the form filtered to the current record
' and in dialog mode
DoCmd.OpenForm "frmOrderDetails", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
End If

By opening the form in dialog mode the user has to close it (or strictly
speaking at least hide it, but we don't need to consider that possibility)
before they can return to the subform. This ensures that they can't move
focus back to the subform or its parent form, leaving the frmOrderDetails
form open at an unsaved record.

I haven't included any code which looks up the current values for the 20 or
so fields from the Products table, analogous to the looking up of the unit
price in the Northwind database. You haven't said that you want to do this,
so I'm assuming its not required.

Ken Sheridan
Stafford, England

 

[ÏòÈÙ]

dddddd
eeeee

Deb:

What you've done is not in fact decomposition of the table as far as I can
tell. That would involve the creation of a new related table of the type
I
described in the final paragraph of my original reply, with separate rows
representing your current 20 or so columns. It sounds to me as though
you've
added the extra 20 or so columns to your order details table. Anyway, the
following assumes you've done the latter rather than the former.

Firstly you need to create another form bound to the order details table,
in
single form view this time. This will be the form you open from your
subform. Lets assume the new form is called frmOrderDetails. Set its
AllowAdditions property to False ('No' in its properties sheet) as you'll
only be using it to edit a record already added in the subform, not add a
new
one.

What you have to do is to open the new frmOrderDetails form filtered to
the
current record in the subform. Each row in the order details table is
uniquely identified by two columns in combination, one which references
the
key of the orders table and one which references the key of the products
table. These would usually be called something like OrderID and ProductID
and be of long integer number data type. The products combo box in the
subform would have ProductID as its ControlSource property, though it
would
normally hide the ProductID and show the product name. The OrderID would
not
normally be shown on the subform as its value is automatically inserted
into
the order details table behind the scenes by the link with the parent
form.
So when you open the frmOrderDetails form it needs to be filtered on these
two columns.

You want the frmOrderDetails form to open as soon as you select a product
in
the combo box on the subform, so the code will go in the combo box's
AfterUpdate event procedure. Before you can open the frmOrderDetails form
at
the current record, however, you need to save the record. So the code for
the AfterUpdate event procedure would go like this:

Dim strCriteria As String

' first ensure that a product has been selected
If Not IsNull(Me.ProductID) Then
' save the current record
Me.Dirty = False

' build criterion to filter frmOrderDetails
' when it is opened
strCriteria = "OrderID = " & Me.OrderID & _
" And ProductID = " & Me.ProductID

' open the form filtered to the current record
' and in dialog mode
DoCmd.OpenForm "frmOrderDetails", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog
End If

By opening the form in dialog mode the user has to close it (or strictly
speaking at least hide it, but we don't need to consider that possibility)
before they can return to the subform. This ensures that they can't move
focus back to the subform or its parent form, leaving the frmOrderDetails
form open at an unsaved record.

I haven't included any code which looks up the current values for the 20
or
so fields from the Products table, analogous to the looking up of the unit
price in the Northwind database. You haven't said that you want to do
this,
so I'm assuming its not required.

Ken Sheridan
Stafford, England

 

[debraj007]

Thank you. I did create a seperate table with the 20 custom fields which I
called tblProductDetails, and I got this to work.