Re: Adding a new parent key for a new child record on a subform

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On 11 Feb 2006 12:02:07 -0800, estebistec@xxxxxxxxx wrote:

Okay, I'll try to keep this compact or readable. The subform's query
is:

Answers inline...

SELECT
OrderItem.PONumber,
OrderItem.Vendor,
OrderItem.ItemCode,
OrderItem.Teacher,
OrderItem.DateReceived,
OrderItem.OrderItemPrice,
OrderItem.Quantity,
OrderItem!OrderItemPrice*OrderItem!Quantity AS TotalPrice,
Item.Name AS ItemName
FROM
OrderItem
INNER JOIN Item
ON OrderItem.ItemCode=Item.ItemCode;

It's not *essential* to include the Item table in this query. Your
combo box can *display* the item name while storing the item code; or,
you can have the combo display the item code and use a Form Textbox to
display the (hidden) second column of the combo box.

The above query will make it impossible to display or enter OrderItems
for items which do not yet exist, since the INNER JOIN will fail to
find any records.

All of the fields are on the sub-form. ItemCode is a combobox with
RowSource: SELECT ItemCode FROM Item;

Now, since this is a sub-form, the linking condition is the PONumber
field of the parent form (whose main source is the orders table) to the
same field of this sub-form.

If you're using PONumber as the Master Link Field/Child Link Field, it
will fill in automatically!

So here are my events so far:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.PONumber = Forms!EditOrders!PONumber
Me.Vendor = Forms!EditOrders!Vendor
End Sub

.... so this code would appear to be unnecessary. What's with copying
the vendor anyway? YOu can see it on the mainform; why show it a
second time on the subform?

This one works so far. Basically I don't want to show these fields, and
I want them auto-filled from the parent. Every order goes to one
vendor, simple enough.

ah... *to* one vendor, not bought *from* one vendor. Nevertheless, the
Vendor would appear to be related to the Order as a whole, not to each
orderitem - no?

Next, after the itemcode has been changed a want to pull the vendor's
latest price and insert it as this order-line-items new price:


Private Sub ItemCode_Exit(Cancel As Integer)
If Not IsNull(Me.ItemCode) Then
Me.OrderItemPrice = GetCurrentVendorPrice(Me.ItemCode,
Me.Vendor)
End If
End Sub
This also currently works. Any time the order price of an item, or the
quantity is updated, either automatically or by the user, I want the
total price for the line item to update:

If that's working, fine - but it's an unnecessary complication. You
can simply include the current vendor price in the RowSource query of
the Item combo box, and use = Me.cboItemCode.Column(n) to pick up the
price, I'd expect.

Private Sub Quantity_Change()
Me.TotalPrice.Requery
Forms!EditOrders.Requery
End Sub

The AfterUpdate event would be preferable - Change() fires *at every
keystroke in the textbox*; AfterUpdate when the user has entered a
price and left the control.

This also currently works. That second line there is to tell the parent
form to update it's total (a sum of the total prices for all of the
order's line-items).

And now for the piece of code that doesn't work. Note that I've tried
this code in a few different events just to see if I was getting
sequencing wrong, but here it is in it's current form (yes, LimitToList
is currently set to True/Yes, and in tracing this code I see that it is
called):

0 Private Sub ItemCode_NotInList(NewData As String, Response As
Integer)
1 If MsgBox("Do you want to add new item " & NewData & "?",
vbYesNo, "Add Item?") = vbYes Then
2 If IsNull(Me.ItemName) Then
3 Me.ItemName = " "
4 End If
5 UpdateOrInsertItem NewData, Me.ItemName
6 UpdateOrInsertVendorPrice NewData, Me.Vendor, CCur(0)
7 Me.OrderItemPrice = GetCurrentVendorPrice(NewData, Me.Vendor)
8 Response = acDataErrAdded
9 Me.Dirty = True
10 'Me.Requery
11 Me.ItemCode = NewData
12 Else
13 Response = acDataErrContinue
14 End If
15 End If

Okay, so I have a few things to explain here. Line 3 is to ensure that
when I attempt to insert the new item, if need be, that the name of it
not be null.

Unless you have the field's Allow Zero Length String property set to
true, this won't work. Access trims trailing blanks, so setting a
field to " " (or to "" for that matter) will in fact make it NULL.

Lines 5 and 6 are to insert the indicated record types
(item or vendoritem), if the given data does NOT exist. These are the
calls that I have thoroughly tested, and I know they are doing what I
want them to do and what I think they do. I've set breakpoints, I've
checked tables visually and with sanity-test queries after those have
executed and before the next code. Line 7 works just fine as always, as
it just does the same thing as Quantity_Change.

I guess I don't undrestand your table structure, then. Why should the
name of the item be stored redundantly? What's the distinction between
an item and a vendoritem? What is the structure of the Item table?

Line's 9 and 10 are what (only using one at a time) give me the error
quoted at the very top of this thread. If I do neither of those two
options then the following line (11), says that I have to save the
record before I can change a field.

Almost surely because of the Query listed above. If you want to
include the item table in this form's Recordsource, you must add the
ItemID to both the Item table and the OrderItem table.


John W. Vinson[MVP]
.

Relevant Pages

  • Re: Date in VBA Problem ?
    ... Why not use a query to effect the changes? ... [Price] number ... Private Sub Command4_Click ... how to change today's date item apple price to 200 ...
    (microsoft.public.access.formscoding)
  • RE: Form and Table issue - help needed.
    ... tables so you can query the data and then use forms. ... item and price tables. ... would make vendor ID and Item ID my primary and foreign keys. ... select an item and vendor then a subform for price. ...
    (microsoft.public.access.gettingstarted)
  • Forms, subforms, and totals
    ... primary key for their vendor number, a text fields for the vendor name, ... I have a query which calculates the to price, ... grand totals, when I look at it, the field says #Name? ...
    (microsoft.public.access.gettingstarted)
  • Re: Lookup question
    ... >the DLookup and it seems to put the price on the next line. ... has no Control Source, or a DLookUp expression for a control source) ... name in the query upon which the combo is based. ... Private Sub cbo_TestName_AfterUpdate ...
    (microsoft.public.access.formscoding)
  • RE: Message Request Box
    ... The query might look like: ... Parameter [Vendor Name?] Text; ... of the vendor and filter the report based on that. ... Private Sub Report_Open ...
    (microsoft.public.access.reports)