INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Form creating phantom record.

Form creating phantom record.

(OP)
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?

RE: Form creating phantom record.

Is your main form also bound?

RE: Form creating phantom record.

(OP)
Yes, to a query.

RE: Form creating phantom record.

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

RE: Form creating phantom record.

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

RE: Form creating phantom record.

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

RE: Form creating phantom record.

(OP)
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#));

RE: Form creating phantom record.

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

RE: Form creating phantom record.

(OP)
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.

RE: Form creating phantom record.

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

RE: Form creating phantom record.

CODE -->

"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

RE: Form creating phantom record.

(OP)
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)

RE: Form creating phantom record.

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

RE: Form creating phantom record.

(OP)
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

RE: Form creating phantom record.

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.

RE: Form creating phantom record.

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


RE: Form creating phantom record.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close