Form creating phantom record.

[weightinwildcat]

I am working on code for a form button to add a new record to a table. I have tried two approaches.

The first approach is:

Set rs2 = db.OpenRecordset("SELECT OrderNumber, OrderID, ProductID, ShipNumber, " & _
"ShipQty, DateShipped, Notes " & _
"FROM ShipPartial", dbOpenDynaset)

rs2.AddNew
rs2!OrderNumber = SONumber
rs2!OrderID = OrderID
rs2!ProductID = ProdID
rs2!ShipNumber = QtyOrdered
rs2!ShipQty = QtyOrdered
rs2!DateShipped = DateShipped
rs2!Notes = Notes
rs2.Update

rs2.Close
Set rs2 = Nothing

The second approach is:

strSQLShipPartial = "INSERT INTO ShipPartial (OrderNumber,OrderID," & _
"ProductID,ShipNumber,ShipQty,DateShipped,Notes) " & _
"VALUES (" & SONumber & "," & OrderID & "," & Chr(39) & ProductID & Chr(39) & "," & _
QtyOrdered & "," & QtyToShip & "," & Chr(35) & DateShipped & Chr(35) & "," & _
Chr(39) & Notes & Chr(39) & ")"

In both cases, in addition to adding a record the way that I want, I also am left with an additional record that has its own autonumber ID, an entry in the OrderID field, and an entry in the DateShipped field. This is causing problems with the display in my subform. Any thoughts as to what is causing it and how to work around it?

 

[MajP]

Is your main form also bound?

 

[weightinwildcat]

Yes, to a query.

 

[MajP]

If it is bound why do you need code to save a record? One or the other, but not both.

 

[weightinwildcat]

For partial shipment we need to create a new record for the items shipped, and then reduce the quantity of the original line item.

 

[MajP]

So what is the sql for the bound query? Does it include PartialShipped in it?

 

[weightinwildcat]

This is the query for the main form:

SELECT ORDERS.OrderNumber, CUSTOMERS.CompanyName, Sum(ORDERS.QuantityOrdered) AS SumOfQuantityOrdered, Min(ORDERS.OrderDate) AS MinOfOrderDate
FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CustomerID = ORDERS.CustomerID
GROUP BY ORDERS.OrderNumber, CUSTOMERS.CompanyName
HAVING (((Min(ORDERS.OrderDate))>=#1/1/2013#))
ORDER BY ORDERS.OrderNumber;

This is the query for the subform:

SELECT ORDERS.OrderID, ORDERS.QuantityOrdered, Trim([ORDERS].[ProductID] & " " & [ORDERS].[OptionConfig]) AS ProdID, ORDERS.OrderNumber, ORDERS.Message, ShipPartial.DateShipped, IsShippedCheckboxSetting([DateShipped]) AS IsShipped
FROM ORDERS LEFT JOIN ShipPartial ON ORDERS.OrderID = ShipPartial.OrderID
WHERE (((ORDERS.QuantityOrdered)>0 Or (ORDERS.QuantityOrdered)<>IsNull([QuantityOrdered])) AND ((ORDERS.OrderDate)>=#1/1/2013#));

 

[MajP]

So it still looks to me that you are adding values into an updateable query and then also running an insert query.

 

[weightinwildcat]

I do not add records directly through the query for the main Ship Partial form. In an order's line item is split and a partial shipment is made, a record would be added through the Orders Screen Query that pertains to the main ORDERS form. A new record can be added directly to the ShipPartial by using an INSERT statement, or else by using AddNew, setting the field values, and then using the Update command. In either case I get the phantom record; it turns out this happens when I run a Requery or Refresh command, right near the end of the subroutine.

 

[weightinwildcat]

It turns out the phantom record is actually inserted before the regular, correct record by several seconds.

 

[MajP]

"VALUES (" & SONumber & "," & OrderID & "," & Chr(39) & ProductID & Chr(39) & "," & _
QtyOrdered & "," & QtyToShip & "," & Chr(35) & DateShipped & Chr(35) & "," & _
Chr(39) & Notes & Chr(39) &

Are any of these controls from where these values come, bound to a controlsource?
SONumber, OrderID, ProductID, QtyOrdered, QtyToShip, DateShipped, Notes

 

[weightinwildcat]

Yes, they are. They are initialized as follows:

SONumber = CLng(Forms![Ship Partial Form].Form.[OrderNumberTextbox].value)
OrderID = CLng(Me.OrderID_Textbox.value)
ProdID = CStr(Me.ProdID_Textbox.value)
QtyOrdered = CLng(Me.QuantityOrderedTextbox.value)
QtyToShip = CInt(Me.QtyToShipBox.value)
Notes = Me.NotesTextbox.value
DateShipped = CDate(Me.DateShippedTextbox.value)tityOrderedTextbox.value)

 

[MajP]

Let me rephrase. Are any of these controls bound to a table or query?
OrderNumberTextbox
OrderID_Textbox
prodID_Textbox
QuantityOrderedTextbox
QtyToShipBox
NotesTextbox
DateShippedTextbox

 

[weightinwildcat]

Yes, they are.

Anyway, here is the kind of thing that is happening:

DateCreated OrderNumber OrderID ProductID ShipNumber ShipQty DateShipped Notes
10/4/2016 9:46:54 AM 89649 624654 MS-DBP48 25 1 10/4/2016 Test
10/4/2016 9:46:46 AM 624654 10/4/2016

 

[MajP]

Not sure how many more ways to say this, but you have a bound form so of course it is creating a record and then you run an insert query creating a second record.

 

[Mohan Chellappa]

I think You are not using access the way it is designed to be.
Don't use query to insert records. Rather save the value to the bound controls.

If both the forms are linked , then create an update button on the parent form.
in the click event vba code ,
Refer to the controls in subform
me.subformName!OrderNumber = SONumber

 

[weightinwildcat]

We have a solution! I simply had to set the Recordset Type on my subform to Snapshot. Now everything works.