Lookup field for records that don't exist (returning a "" value)

[Dylan Snyder]

okay, I think I'm in the right area. I'm not sure if this will require code or not.
I have a form that allows the user to make changes to the Quantity of items on Orders. Simple enough, but I need the user to be able to add items to orders where there currently aren't any instances of that item

The Order number is contained in the PO header table. THe item number is contained in a PO Detail table. I need to have a nonuser editable field on the form that lists the original quantity. (I'm currently just using a hidden form tied to a query to accomplish this) If an item is not on the orignal PO-as in a new item that the user is adding to a PO, how would I get the orignal Order Quantity lookup field to display something like "Item added to PO" I'm not Sure if VBA or some fancy unbound fields will be the ticket here. Any ideas?
HasValue? is that applicable here???

 

[ajetrumpet]

It sounds like the process you go through here is very complicated Dylan. I don't think a lot of people could answer this, because of that complication.

Obviously this is possible, but in order to give you a concrete answer, you'll have to provide more information on this...

 

[Dylan Snyder]

Synopsis

There are Four tables in this situation: (And the fields)

Purchase Order Header
PO #
Worksheet

Purchase Order Detail
Worksheet
Item
QTY

Item Master
Item

REquested PO CHange
PO #
Item
New QTY
QTY

PO Header and PO Detail are linked in One to many by Worksheet. What I want to do is have a form that allows me to select a PO # from the PO Header and select any Item from the Item Master. Then, I want a field on the form to lookup the qty on a PO by searching for the PO # and Item on the PO Detail table. Then, I want to be able to key in the new quantity and save the info to the Requested PO CHange table. This is simple. WHat I don't know how to do, though, is return a "0" in the QTY field on the form when there is no associated record in the PO Detail table, meaning that the Item is not currently on the PO. I just get an error when this occurs. I need to turn this into a "0" in the Qty field on the form. Also, I have the Tables on an SQL Server if that makes any difference.