My MS Access control displays no text for appended items following an append query

Question

Relevant Tables

                                  #One#
+----------+         +--------------+
|Quotations|  --->   |PurchaseOrders|
+----------+    |    +--------------+
         <One>  |  <Many>           |
                |                   v  #Many#
                |    +-----------+        +------------+ 
                v    |QuotedItems|  --->  |OrderedItems|
                     +-----------+        +------------+
                   <Many>      {One}    {Many}

---

Form/Subform

PurchaseOrders - Master Form, many-to-one with OrderedItems
OrderedItems - Datasheet-style subform, many-to-one with PurchaseOrders

Quotations- A table that is one-to-many with PurchaseOrders and used for querying QuotedItems - A table that is one-to-many with OrderedItems and used for querying

---

Combo Box

The Item control resides in the OrderedItems subform. It is a combo box that runs the following ad hoc query:

SELECT [ID], [Code]
FROM   [QuotedItems]
WHERE  [QuotedItems].[Quotation] = Forms.PurchaseOrders.Quotation;

The combo box query runs the way I expect it to; there are no problems here. The [ID] column is hidden from view (column-width is zero).

---

Problem

The problem comes when I try to append values from the QuotedItems table (filtered on the current quotation ID selected in the form) at the click of a button.

I created an append query to achieve this called CopyQuotedItems2OrderedItems:

INSERT INTO OrderedItems ( PurchaseOrder, Item, Quantity )
SELECT PurchaseOrders.ID, QuotedItems.Item, QuotedItems.Quantity
FROM (    Quotations
          INNER JOIN
          PurchaseOrders
          ON Quotations.ID = PurchaseOrders.Quotation
     )

     INNER JOIN

     QuotedItems

     ON Quotations.ID = QuotedItems.Quotation  

WHERE (((Quotations.ID)=[Forms].[PurchaseOrders].[Quotation]));

The Copy Quoted Items button in the PurchaseOrders form then runs the following code:

Private Sub CopyQuotedItems_Click()
    DoCmd.OpenQuery "CopyQuotedItems2OrderedItems"
End Sub

The append works as it should. However, the second field of the INSERT statement - which ties in to the Item control does not display anything, even after refreshing. The only way to make the item's Code visible is to select the combo box and choose an item from it.

---

1000 words...

Before clicking Copy Quoted Items (note that combo box has two entries in this case):

After Clicking Copy Quoted Items:

Questions

• Firstly, why can't I see my Code by default after running the append query? Is it because the value inserted by the append query is not bound to the combo box in some way?
• Secondly, am I barking up the wrong tree? If so, how else can I get it to display the Code column automatically?

Answer 1

I am not sure I get you, but have you tried a Requery rather than a refresh?

 <Me or form/subform name>.CodeCombo.Requery

It may be necessary to requery the form or subform, but I do not think so.

EDIT re Further Information

The problem here is that you are using look-up fields in tables. This is an anti-feature and will, IMHO, continue to make your life difficult. Because of this, you are not updating the item field with an item code but an item id: 2 rather than 30105-250G. Furthermore, 30105-250G is a value made up of parts. There are several approaches to fixing this, the easiest is probably to match up the model id again and extract the code field. However, I strongly recommend that you get rid of all look-up fields and work with relational design ideas.

Answer 2

It looks like Access has not changed much from wayyyyyyy back in the 90s. ;}

<>

We used to write procs which "faked it out" to force the control to refresh. I don't have the code any longer; we used the old sendkeys equivalent brute-force.

I'm willing to be wrong, but in your situation that's the only thing we had which reliably worked.