Append/copy calling a module switches data

[dl]

Hello everyone, (this is being reposted here, since the subject has changed
from a form question to a query question and the previous posting was very
old)

Ok, I've taken the SQL from the previous posting (below)(graciously
provided by Doug Steele, which prints the SQL as a string to troubleshoot the
problem) I followed the directions given and here is what was written out as
SQL:

INSERT INTO tbl_MainBarcode_Single_new SELECT BarcodeID, OfferDetail,
DeliveryVehicle, DistArea, DistQty, DistStartDate, DistEndDate,
ValidStartDate, ValidEndDate, ValidArea, NoteGeneral, Chain, DateAdded,
Cancelled, PromoCouponName, GroupRequesting, OfferType, Requester,
HurdleType, HurdleNumber, ProgrammedOrNot, RewardsOrNot, "dlongton" AS
CreatedBy, MoreThan10stores FROM tbl_MainBarcode_Single_new WHERE [BarcodeID]
= 159010080000000000

When I run the query just by itself it does create a copy and put "dlongton"
into the new record, leaving the UserName in the original record as it was
(which is what I want it to do). However, since this will of course need to
pull different UserNames I'll have to use "fOSUserName()" to pull the
current user (a module created using API code from
http://www.mvps.org/access/api/api0008.htm to pull the
network user name) When I use the code below, it still switches the
UserName (aka
CreatedBy) in the original record and the newly copied record...it's driving
me crazy!

DoCmd.RunSQL "INSERT INTO tbl_MainBarcode_Single_new SELECT BarcodeID,
OfferDetail, DeliveryVehicle, DistArea, DistQty, DistStartDate, DistEndDate,
ValidStartDate, ValidEndDate, ValidArea, NoteGeneral, Chain, DateAdded,
Cancelled, PromoCouponName, GroupRequesting, OfferType, Requester,
HurdleType, HurdleNumber, ProgrammedOrNot, RewardsOrNot, " & Chr(34) &
fOSUserName() & Chr(34) & " AS CreatedBy, MoreThan10stores FROM
tbl_MainBarcode_Single_new WHERE [BarcodeID] = " & Chr(34) &
Forms!frm_Entry_SingleItem.BarcodeID & Chr(34)

I'm not sure if this would affect anything, but in the Private Sub where
this SQL resides (for the command button), there are also these things
happening:

Private Sub Copy_Click()
DoCmd.RunMacro "mcr_a_BeforeCopy_single" **unlocks the barcode &

createdBy field**
DoCmd.SetWarnings 0 **self explanitory**
DoCmd.RunSQL .... **as noted
above**
DoCmd.RunMacro "mcr_AfterCopy_single" **sets focus on barcode
field**
DoCmd.RunMacro "mcr_msgBox for Copy" **instucts user to assign

barcode to the new record before

proceeding**
End Sub

Oh, the form also has a macro on the "after update" event that relocks
the barcode field and createdBy field ...not sure if I even need to
unlock and relock the CreatedBy aka UserName field for this to work, but I
figured it wouldn't hurt.

Thank you for any help!
--
dl

That doesn't make any sense.

Try something along the lines of:

Dim strSQL As String

strSQL = "INSERT INTO tbl_MainBarcode_Single_new " & _
"SELECT BarcodeID, OfferDetail, DeliveryVehicle, " & _
"DistArea, DistQty, DistStartDate, DistEndDate, " & _
"ValidStartDate, ValidEndDate, ValidArea, " & _
"NoteGeneral, Chain, DateAdded, Cancelled, " & _
"PromoCouponName, GroupRequesting, OfferType, " & _
"Requester, HurdleType, HurdleNumber, ProgrammedOrNot, " & _
"RewardsOrNot, " & Chr(34) & fOSUserName() & Chr(34) & _
" AS CreatedBy, MoreThan10stores " & _
"FROM tbl_MainBarcode_Single_new " & _
"WHERE [BarcodeID] = " & Forms!frm_Entry_SingleItem.BarcodeID

Debug.Print strSQL

DoCmd.RunSQL strSQL

Go to the debug window (Ctrl-G) and see what gets written out as the SQL.
Copy that into a query and run it.

If BarcodeID is text, change that last line to

"WHERE [BarcodeID] = " & Chr(34) &
Forms!frm_Entry_SingleItem.BarcodeID & Chr(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well, the form shows the field that stores the UserName, so after hitting the
command button, which runs the SQL, the new (copy) record has the same
UserName as the one I just copied - instead of pulling my UserName and
putting it on the new/copied record. Does that make sense?

So I'm able to see that the UserName doesn't change when a copy is made (the
record I'm copying of course has another person's UserName, so I can see if
it replaces it with mine). To double-check I always look within the table
itself, as well.

thanks for any help you can give!