Re: Copy a record to a new record in the same table with a button on form

On Jul 20, 1:04 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx> wrote:
The 2nd string does have a bad comma before the SELECT.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <saraqp...@xxxxxxxxx> wrote in message

news:1184950095.192188.48880@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



On Jul 20, 12:26 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
wrote:
Sara, I have no idea why this statement would fail.

You have the right number of fields, in the right order, separated
correctly
(commas, spaces, brackets.) The source and target are the same table, and
the field names match, so it cannot be a data type error. We assume that
POItemsKey is a Number field (not a Text field), so the WHERE clause is
correct without quote marks around the 66. I would add a space with the
brakets, i.e. "( POKey, ... POItemsKey )"

If one of the fields (POItemsKey?) was primary key, the insert would fail
if
the 66 already existed. Otherwise I can't see any problem here.

INSERT INTO tblPOItems
(POKey,
DeptNum,
MfrNum,
ItemDescription,
Pack,
Quantity,
CompRetail,
OurCost,
OurRetail,
GoodsTypeKey,
AdDate,
InStoreDate,
OrderPieceTypeKey,
CountPieceTypeKey,
Comments,
POItemAdded,
POItemsKey)
SELECT tblPOItems.POKey,
tblPOItems.DeptNum,
tblPOItems.MfrNum,
tblPOItems.ItemDescription,
tblPOItems.Pack,
tblPOItems.Quantity,
tblPOItems.CompRetail,
tblPOItems.OurCost,
tblPOItems.OurRetail,
tblPOItems.GoodsTypeKey,
tblPOItems.AdDate,
tblPOItems.InStoreDate,
tblPOItems.OrderPieceTypeKey,
tblPOItems.CountPieceTypeKey,
tblPOItems.Comments,
tblPOItems.POItemAdded,
tblPOItems.POItemsKey
FROM tblPOItems
WHERE tblPOItems.POItemsKey = 66

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org."sara"
<saraqp...@xxxxxxxxx> wrote in message

news:1184947842.503596.248630@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jul 20, 11:57 am, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
wrote:
I don't understand why Access thinks this is a "function."

Perhaps you can post the results of the debug.Print, so we can see
exactly
what it is trying to interpret.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <saraqp...@xxxxxxxxx> wrote in message

news:1184945417.355362.160470@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jul 17, 9:17 am, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
wrote:
Access does do some odd things with autonumbers, but my guess is
that
it
is
making 390 failed attempts to add a record to the table; hence the
Seed
jumps by that much.

For suggestions on where to look for this violation, see:
Why can't I append some records?
at:
http://allenbrowne.com/casu-19.html

"sara" <saraqp...@xxxxxxxxx> wrote in message

news:1184677467.104030.256730@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jul 16, 8:42 pm, "Allen Browne" <AllenBro...@xxxxxxxxxxxxxx>
wrote:
To debug your append query, add the line:
Debug.Print strSQL
just above the RunSQL line. When it fails open the Immediate
Window
(Ctrl+G), and see what it generated there.

You will find there is something wrong with the SQL statement,
e.g.
a
space
missing between words, or a delimiter that's not correct.

If necessary, copy the SQL statement, and paste it into SQL View
of
a
new
query. Then switch to design view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <saraqp...@xxxxxxxxx> wrote in message

news:1184601712.283748.110040@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Allen -
I had tried this approach before I posted and it didn't work,
but I
tried it again, carefully following your directions. Same
problem.

I don't know how to find the field that is "Type Mismatched", so
I
can't even begin to try to fix it. The other thing is the Query
works
- it's just when it gets into the code it fails....I must be
doing
something wrong..

lngPOItemsKey = Me.lstItemsOnPO.Column(1)

strSQL = "INSERT INTO tblPOItems (POKey, DeptNum, MfrNum,
ItemDescription, " _
& " Pack, Quantity, CompRetail, OurCost, OurRetail,
GoodsTypeKey, AdDate, " _
& " InStoreDate, OrderPieceTypeKey, CountPieceTypeKey,
Comments, POItemAdded, " _
& " POItemsKey) " _
& " SELECT tblPOItems.POKey, tblPOItems.DeptNum,
tblPOItems.MfrNum, " _
& " tblPOItems.ItemDescription, tblPOItems.Pack,
tblPOItems.Quantity, " _
& " tblPOItems.CompRetail, tblPOItems.OurCost,
tblPOItems.OurRetail, " _
& " tblPOItems.GoodsTypeKey, tblPOItems.AdDate,
tblPOItems.InStoreDate, " _
& " tblPOItems.OrderPieceTypeKey,
tblPOItems.CountPieceTypeKey, tblPOItems.Comments, " _
& " tblPOItems.POItemAdded , tblPOItems.POItemsKey " _
& " FROM tblPOItems " _
& " WHERE tblPOItems.POItemsKey = " & lngPOItemsKey

DoCmd.RunCommand strSQL

I even tried running the Query, and since I couldn't find how to
put a
"Where" clause on the DoCmd.OpenQuery "apqCopyPOItem",
acViewNormal
statement.

I put the selection in the Query, but I'm getting: "Undefined
Function [tblPOItems].[POItemsKey]=Forms![frmItemsOnPO]!
[lstItemsOnPO].column" in expression." I even tried the Sum
tool
and
a "Where" clause, but I got the same Undefined Function Error.

Any ideas?

thanks -
sara

NOW, things are very strange...
First, let me say I very much appreciate your instructions as I
find
I
learn more "doing" it myself, but here I'm baffled.

The SQL statement seems fine. When I put it in SQL in a new
Query
and
run it, I get Access won't add the one record due to 'Key
Violations'. The ONLY key is the Key field, which is an
autonumber.
Nothing else has Unique Index on it.

SO, I thought maybe I had to add the table in again with an
alias?
I
tried that, and I get the record added, but the AutoNumber jumps
by
the total number of records on the table. I have tblPOItems and
tblPOITems_1 linked on POItemsKey as an InnerJoin. There are 339
records on the table. (If I don't join, the query wants to
append
ALL
records).

The last key is 389. I am copying key# 381. The query puts it
on
the
table as key # 729! 729 - 339 = 390, which "should" be the next
key
number. Maybe it doesn't matter, but I'm confused as to WHY this
behavior. If I go into the table and copy/paste manually, I get
the
next key #, which now happens to be 730.

I am completely baffled, and appreciate whatever help you can
give
here. \
Sara- Hide quoted text -

- Show quoted text -

Allen -
(Sorry for the posting delay - posts seem to either take forever to
show or never show up!)

I continue to struggle with this - nothing seems to be working! I
brought the issue to my user group (meets once/month) and they, too,
were baffled. We "settled" on my writing an Make table (later,
Append) Query to put the data on a temp table and then insert from
the
temp table, but the problem (again) is that I get:

"Undefined Function [tblPOItems].[POItemsKey]=Forms![frmItemsOnPO]!
[lstItemsOnPO].column" in expression." I even tried the Sum tool
and
a "Where" clause, but I got the same Undefined Function Error.

Why doesn't Access recognize the "Forms!frmname!Controlonform" in
the
criteria? It works just fine if I put the actual key in the query.

I have tried everything and it's just not working. I'm desperate
for
a solution!- Hide quoted text -

- Show quoted text -

Here it is:
INSERT INTO tblPOItems (POKey, DeptNum, MfrNum, ItemDescription,
Pack, Quantity, CompRetail, OurCost, OurRetail, GoodsTypeKey, AdDate,
InStoreDate, OrderPieceTypeKey, CountPieceTypeKey, Comments,
POItemAdded, POItemsKey) SELECT tblPOItems.POKey,
tblPOItems.DeptNum, tblPOItems.MfrNum, tblPOItems.ItemDescription,
tblPOItems.Pack, tblPOItems.Quantity, tblPOItems.CompRetail,
tblPOItems.OurCost, tblPOItems.OurRetail,

...

read more »- Hide quoted text -

- Show quoted text -

Allen-
I took it out and apparently it didn't matter, as the result is the
same.
Any idea why in a regular Append Query I can't use the "forms!frm...."
syntax - choosing the key of the item in the list box?

Sara

.

Relevant Pages