Ok, so here's the whole picture...I hope it helps to create a better
picture
of what I'm up against.
The db was created to generate barcode numbers in a specific format and as
part of keeping track of what barcode is for what, it also allows the user
to
enter all other important info like distribution dates, valid dates,
delievery vehicle, etc. It uses a little code to generate the barcode
number
(when the Assign Barcode command button is pressed the code simply
increments
the last highest BC# and places this new number in the barcodeID field).
Since many of the tracking selections may be alike (dates, delievery
vehicle, etc) the user needs the ability to find a similar record, copy
that
record, then delete the now duplicate BC# on the copy and assign a new
number
to this "new/copy." One of the fields on the BC form is "CreatedBy" which
uses the before mentioned Dev Ashish API code to pull the Network ID of
the
current user and place it in the CreatedBy field.
Problem is, this code, as it's currently setup as default value = to the
module where the code resides; only inserts the current users ID in
"absolutely new" records. When the user copies an exsisting record (and
of
course assigns a new BC# to the copy), the SQL I'm using also copies the
user
that created the original record:
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, CreatedBy,
MoreThan10stores FROM tbl_MainBarcode_Single_new WHERE ([BarcodeID] =
Forms!frm_Entry_SingleItem.BarcodeID)"
...when I use " & Chr(34) & fOSUserName() & Chr(34) & " AS CreatedBy in
the
SQL to make a copy and "pull the current user for the new record" for some
reason it switches the ID's and puts the current user's ID in the record
that
was copied and the previous user in the "new/copy" record. I know it
makes
no sense, but given my limited VB/SQL background I'm stumped.
The other variables here are:
If I leave "CreatedBy" out of the SQL all together, and hope that when I
create a copy, the module that's tied to the default value of that field
will
do it's thing, I get:
1) If I hit the copy button, and then delete the barcode listed (which
would
be a duplicate at this point) and assign a new barcode to the copy [which
is
what we'll need to do to all copied records], this results in NO user name
listed for the "old" record I just copied from, and the "copy" shows the
user
name of the old record. As if it didn't pull the current username at all
and
just switched the null value and the username on the old record!
2) If I hit the copy button, and then DON'T change or assign a new
barcode to the the copy (creating an exact duplicate) then the old records
user name stays as it was but the "new" record has NO user name listed.
Should I just scrap this idea all together? Is there another/better way
for
me to provide examples?
Please help!
--
dl
:
The barcode number can't be different: you're assigning it in your SQL
statement:
SELECT BarcodeID, ...
If you're TRYING to change the value of BarcodeID, you need to put the
value
in the SQL statement, just as I showed you before how to get the User Id.
A word of advice: if you want additional help from me, stop making new
posts
about this. I don't have the time to find all of your different posts so
that I can get the whole picture (and I respond to far too many posts to
remember all of the details you've already posted).
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Hi Doug,
Yes, and thank you again for the code. However, it still switches
the
user name on the old record and the new - can't seem to figure out why.
(Did
you see my last reply from that thread? "subject: CurrentUser()" I
tried
to
explain further, what I was seeing)
I see now why you were confused as to why it mattered if the only
difference would be the user name, as long as the only difference was
the
user name. The problem is that it's not the only difference - the
Barcode
number is also different btwn the two records. So, if the user name
gets
switched, the user who assigned said barcode would then be incorrect.
--
dl
:
I gave you code a few days ago that showed you how to put the new
username
in.
There will be no difference between the two records other than the
username.
That's why I didn't understand how you could make a statement like "it
updated the existing row, not the new one". The end result is the two
records that are identical except for the user name, isn't it? What
problem
is it causing you?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
That was the first thing I tried, but get this:
When I do that, the new record shows the old record's username and
the
old
record that I was copying, no longer has ANY user name listed!
Here's the SQL:
DoCmd.RunSQL "INSERT INTO tbl_MainBarcode_Total_new SELECT
BarcodeID,
OfferDetail, DeliveryVehicle, DistArea, DistQty, DistStartDate,
DistEndDate,
ValidStartDate, ValidEndDate, ValidArea, NoteGeneral, Chain,
DateAdded,
Cancelled, PromoCouponName, GroupRequesting, OfferType, Requester,
HurdleType, HurdleNumber, ProgrammedOrNot, RewardsOrNot,
MoreThan10stores
FROM tbl_MainBarcode_Total_new WHERE ([BarcodeID] =
Forms!frm_Entry_TotalPurchase.BarcodeID)"
Any idea why?
Here's more to add to the complication...another fuction this form
has
is
to
generate a barcode number using this code:
Private Sub Assign_Barcode_Click()
If Right(Left(DMax("BarcodeID", "tbl_MainBarcode_Total_new"), 8),
1)
=
9
Then
Me.BarcodeID = Format(Left(DMax("BarcodeID",
"tbl_MainBarcode_Total_new"), 8) + 2, "00000000") & "0000000000"
Else
Me.BarcodeID = Format(Left(DMax("BarcodeID",
"tbl_MainBarcode_Total_new"), 8) + 1, "00000000") & "0000000000"
End If
End Sub
So...
1) If I hit the copy button, and then delete the barcode listed and
assign
a
new barcode to the "new" record (the copy) [which is what we'll need
to
do
to
all copied records], this results in NO user name listed for the
"old"
record
I just copied and the "new" record shows the user name of the old
record.
2) If I hit the copy button, and then DON'T change or assign a new
barcode
to the "new" record (the copy) then the old records user name stays
as
it
was
but the "new" record has NO user name listed.
Can someone help me here?
thank you!!!!
--
dl
:
I may be simplifying this, so if I am not understanding you
correctly,
please
let me know...
In your append SQL statement, remove the User Name field from the
statement.
If the default of the field is equal to fOSUserName, it will
populate
with
the correct data when the new record is created.
:
Hello,
First let me start by saying I'm quite limited in my knowledge
of
VB, but
I'm trying.
I have a form with a field which has a default value of
"=fOSUserName()"
which is a module created using Dev Ashish's "Get network user
name"
code
(
http://www.mvps.org/access/api/api0008.htm). By referencing
this
as
the
default value it inputs the network user ID when a new record is
created.
Works beautifully.
However, because the user will need to have the ability to copy a
record
that is similar to the record they are about to enter, to cut
down
on
entry
time; I need the copy button to insert the CURRENT user instead
of
copying
the previous user into the "new" record, which is what it does
now.
(the
copy button uses an append SQL statement in the [event
procedure])
I was hoping I could accomplish this by somehow adding something
to
