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

[estebistec]

Hello,

I have a subform running in continuous mode to edit child records.
Specifically, the records are "order items", whose parents are orders.
Not only that, but order items refer to the item being "instantiated"
in the order. So they are really line-items indicating items that
vendors provide.

Now for the problem. I'm allowing the ItemCode to be selected from a
combobox, and I want the user to be able to enter a new, first-time
item code. This is NOT the relation represented by the subform linking
(order to order-item). So my strategy is to capture ItemCode_OnExit or
Form_BeforeUpdate or whatever else might apply to attempt to pre-create
the required item record. Invariably, I get:

The Microsoft Jet database engine cannot find a record in the table
'Item' with key matching field(s) 'ItemCode'.

Now I've searched around in these forums so I've seen people reporting
this error message, and being told to ensure the parent record of the
relationship is there. But remember, I'm adding this via code. Further,
I'm even tracing code and seeing in the table that the record has been
created, an Item with the specific ItemCode. Still, it tells me that
the existing item code can't be found in the table. So access won't
save the current new record, and on trying to exit I get the ugly
messages about a record that it can't save, so on and so forth.

I was thinking that using the event methods would be more
straightforward than this. What else can be going wrong here? Thanks.

 

[John Vinson]

So my strategy is to capture ItemCode_OnExit or
Form_BeforeUpdate or whatever else might apply to attempt to pre-create
the required item record.

Usually one would use the Combo's Not In List event to do this - it
"stops the action" while you create the needed record, adds it to the
combo's rowsource, and then to the Control Source, all before the
record is saved. Might this be a solution?

John W. Vinson[MVP]

 

[estebistec]

Okay, I just saw in the forums that you have to have "LimitToList" set
to "Yes" for the event to fire. That explains that. The event itself
doesn't work like I thought though, it just keeps displaying the dialog
with the message that the selected item is not in the list when my code
explicitly inserts the record and tells the combo box to requery it's
source. So I've got some more work to do on that. Anyway, I don't know
that that clears up the original problem, so if anybody see's anything
else familiar here that would help, great!

 

[estebistec]

Well, now my NotInList event works, but it does the same thing that my
ItemCode_OnExit event did, and so I'm back to where I was really. One
other detail I should mention is that the source of the sub-form is a
query, though this shouldn't be a problem, as it worked before I tried
to handle the issue of new itemcodes. It's still adding child records
to the one table and still trying to find the parent beforehand (which,
as I said at the top, does in fact exist when it gets to this point).

 

[John Vinson]

Well, now my NotInList event works, but it does the same thing that my
ItemCode_OnExit event did, and so I'm back to where I was really. One
other detail I should mention is that the source of the sub-form is a
query, though this shouldn't be a problem, as it worked before I tried
to handle the issue of new itemcodes. It's still adding child records
to the one table and still trying to find the parent beforehand (which,
as I said at the top, does in fact exist when it gets to this point).

Please post your current code.

John W. Vinson[MVP]

 

[estebistec]

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

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;

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.

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

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.

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:

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

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. 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.

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. This is wierd since I was able to
set other fields after the insert and before the save. But the main
point is that you see here where I was saying I KNOW I have records
inserted, and when I get to the requery call or the Dirty=False
statement, it complains that the exact data doesn't exist.

Thanks a bunch ahead of time for the help. I guess sometimes a problem
like this just needs a fresh set of eyes. I'm a software developer so
I'm not unfamiliar with these ideas. I just feel pretty restricted once
I get into the Access environment to construct a user-friendly UI. I
feel like I'm working hard to force it to do stuff that's very easy to
build up from scratch in C#, Java, or Python. Using access as a backend
and one of these others would be perfect, except as always the
restriction is that I don't know that I"ll be able to modify the
person's environment to support the result. I must simply be
approaching it wrong, as I've seen impressive access apps out there,
but I had thought that what I was trying to do was very straightforward.

 

[John Vinson]

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]

 

[estebistec]

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.

Well, the idea was to allow the creation of a new item, so the name
would come from here if it all worked out.

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.

Well that pretty much explains my problem then. I was hoping I could
sneak in the required items before the insert, and that the requery
would find the newly inserted record. So the form query MUST be able to
deal with the new record even before insertion is finalized.

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?

This is true, and probably a relation I should have gone on ahead and
removed. Before the tables evolved the idea was that an order might
contain items from more than one vendor. That's not the case now.
Looking into what this could make easier now...

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.

This is not really true as the order item needs to keep the vendor's
price at the particlar time the order was placed. The vendor's prices
may change but old orders should show what was effective at the time.

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.

Got it. Check.

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?

The name of an item is NOT stored twice, only once. The idea was to
allow it to be updated from an order, or not really updated, but set
for the creation of a new item inline with the current order. The
distinction of a vendor-item is that several vendors may offer the same
item at different prices. The structure of item is (ItemCode,
ItemName).

Thanks.

 

[estebistec]

Well John, thanks for all your help. It works now. I'm still not
certain what specific thing it was, but I sort of went back over the
hole thing "cleaning it up" in various ways. However, your statement
about the query never allowing the subform to insert new records did
not turn out to be the case, as the form is now working exactly as I
had planned. Here are the things I changed (since I don't know what
made it "just work"):

* Removed Vendor from OrderItem (since it can be obtained through
Order)
* Replaced any "OnChange" events with "AfterUpdate"
* Commented out/removed any redundant events that simply performed
requeries that are now working automatically as they should
* Changed ALL sub-form control names to be different from source-query
and table field-names (e.g., changed ItemCode to cboItemCode, etc.).

Of all of these changes, I don't know which had the most impact.
Anyway, thanks for helping to stimulate my thought about what might
have been wrong. My best "educated" guess is that my usage of
BeforeInsert, AfterInsert, and OnChange for some of the individual
controls was perhaps getting the sequence of data updating out of
proper state for me to be trying to do the other manual things I was
doing (the Item insert). That isn't a full explanation yet, so I'll see
if I can come up with a final word on it.