upgrading *.mdb, and adding a history tracker for assets

[hammerman]

I'm trying to upgrade an old MDB Access dB written by unknown party. It uses minimal tables and scores of queries to manipulate the single most populated table of test equipment. The current dB has no traceability of usage; it is direly needed. When an item is 'removed from circulation' and is being used by Mr. X, his name is entered into a field in the record in the table. When he returns it, his name is replaced by 'nothing'. (empty field).
These 'Ins and outs' are done using queries called by forms' buttons or even by 'key-up' triggers. When the instrument is returned to the crib, the 'name' is deleted from the record; but before it is, I need to copy three pieces of info into a table I'm creating and appending. This info is the name of who is doing the I/O action, stating what that action is, (and based on the form/query being used, that is hard-coded), and the asset-ID number. Only the asset-ID number is entered into the form that calls the update query.


My question is this: "How do I snag the name that was previously entered in the field when it was removed for use?" The problem is, when the user returns the item to the crib, his/her name is not needed in the original pgm.
Also, this is a 'one-PC' program; only one data entry point. Therefore, Login is as a 'kiosk' PC with a generic ID and PW. So, Environ(username) won't work.
The following is my function I want to call to do the table update, (append, I know).

[Code]
Public Sub AddUserAction(Name As String, Activity As String, BarCode As Variant)
' "debug.print X,Y,Z parameters" does print the 'passed-in value, or should I say 2 of three correct values. the problem one is focus of my question.
CurrentDb.Execute "INSERT INTO tblUserHistory (UserName, Activity, BC_Num) VALUES ('" & Name & "', '" & Activity & "', '" & BarCode & "')"
End Sub
[Code]

[Micron]

First, Name is a reserved word and should not be used for object/field names.
allenbrowne.com/AppIssueBadWord.html
If you cannot make use of the Windows login username, then you'll need a db login routine, maybe with passwords.
Can you just not log transactions in a table like OutBy, InBy, OutDate, InDate, EntryBy? If a status was required you could add that too, but I think this would make life easier and provide more traceability. If the item is out, the InBy and InDates are null. If it's not, all dates are there, plus who returned it (InBy) and who logged it in (EntryBy). The first action is probably an append query, the return is an update. It's also doable with a status if you want to link an item status table, but you'd still need the transaction info, so my preference would be just the transaction table.

[hammerman]

No, due to the need to know who used a specific item during what time frame, we need to log who used what, when. these items are used for testing new designs and documenting successful completion of those tests includes the corroboration that it was a calibrated instrument. If, at calibration cycle time, a device is found to be out of cal, we need to know who all used it, even before we know what tests were done with it. its a long needle in a haystack process currently. adding the logging function and history tracker will cut that down significantly. Once the table is populated over the next year, I can sort by instrument, by time frame, by user, to find out what tests that person who used this item completed.
Moreover, getting compliance to the current workflow and 'crib' documentation in the dB, is less than 'good'. Adding more 'hoops' in their process to sign-our or sign-in will dissuade better compliance! Sadly! So, I don't want to throw more blank fields in the forms at the user, that will need to be filled in.
The primary table has the information; getting it before the update query is triggered is the speedbump to my successfully adding the 'return to crib' notation in the table.
Thanks for your suggestion, though.

[Micron]

I do understand something about traceability, having worked in ISO in the past, so I don't see why the suggestion doesn't provide traceability. Assuming the numbers relate to names in some table, the following shows who took which caliper on which date, who returned it on what date, and who logged it in (or out as the case may be).

ItemID	OutBy	InBy	OutDate	InDate	EntryBy
A1254	2	2	12/1/2016	12/1/2016	21
B654897	25	23	12/1/2016	12/5/2016	21
A12555	15	15	12/5/2016	12/6/2016	10
B54872	20		12/5/2016

Does it show what jobs an item was used on? No, but you probably have that covered already and it would be a different table if the same db. It does show that the last item is still out. I don't get what your meaning regarding blank fields. The return form and loan form don't have to show all fields or even be the same form, though I'd probably use the same one and change/hide labels/controls to suit.

How do I snag the name that was previously entered in the field when it was removed for use

I must be missing something here though, as I don't see how it could ever be possible to get data that has already been removed. I've re-read your code and question and have to guess that the value you need has to be retrieved before the usual record edit takes place. That could be a DLookup on a table field if written in your code, or simple Select query execution.

[hammerman]

Micron:
Sorry, I misunderstood; I am trying to make a new table with ID (PK), Transaction_date, User, Transaction (in or out), and asset_number. And I understand that I need to grab the user before the query runs that deletes that info from the main table record. I also did come across the DLookUp function as a 'solution' while going over notes and other resources. I've tried that. and while I can 'debug. print ' the name, the asset number and the action (inserted as a literal string), I can't get the info into the new table. That will take some more investigation and playing around. The code steps through all the instructions, but nothing appears in the table. {I've listed the 5 fields, the PK is auto-number; the date has a default value= Date(); and everything else is passed in through a sub routine as/with parameters.
The subroutine first immediate windows the values passed in; then 'executes a SQL 'insert into table_name ( username, activity,barcode) VALUES (name variable, activity variable, barcode variable(variant) ) *all with proper grammar and punctuation*;
I've tried DoCmd.runSQL sql_variable; with sql_variable defined similar to above literal sql statement. But nothing is copied into the new table. So I will muddle some more, and maybe revisit this thread for more questions.

[Micron]

without seeing anything of your attempts or results, it's virtually impossible to know what the issue is. It could be as simple as a pair of missing quotes. Your entire INSERT INTO statement would be a start, and if you cannot post the code, maybe just the parts that use it . If you can get all the proper values to print in the immediate window but not get them into the table, I suspect that's where the problem lies.

[hammerman]

HI, Micron:
Here's the subroutine's code. I've set a breakpoint prior to this sub being called, and watched it step through this, yet the actual information isn't being inserted into the table.
(also, the table has 5 fields; I'm only updating 3, the other 2, the ID, (PK, as replication ID type) and ActivityDate, [required and default value = DATE()] are updated when the record is created.)
<
Option Compare Database
Option Explicit

Public Sub AddUserAction(strName As String, Activity As String, BarCode As Variant)

Debug.Print strName
Debug.Print Activity
Debug.Print BarCode
CurrentDb.Execute "INSERT INTO tblUserHistory( UserName, Activity, BC_Num) VALUES ('" & strName & "', '" & Activity & "', '" & BarCode & "')"
End Sub
>

[aytee111]

You need to Debug.Print the execute string, copy it to a new query and see what happens.

[Micron]

agreed, and FWIW, I pretty much always use the parameter ,dbFailOnError with db.Execute. Fairly certain your use of the method does not raise an error if the query doesn't work so you don't have any clue. Pretty sure that parameter raises an error if the query fails. Please add that first to prove me wrong/right and let us know. It won't fix the failure to write to the table, but may prove the value of the parameter. Then paste the output into a new query as suggested. The offending part should get highlighted. I suspect it will be a data type mismatch or field reference issue.

[hammerman]

Okay! I wrote a new query, and entered hard-coded data. that worked. In the process, I found that the ID was not an "Autonumber" data-type field, (just a 'number-type'). Looks like that was the crux of my problem. (after I discovered "DLookUp", which worked extremely well. )
Thanks, Micron and aytee111. Very much appreciated! Happy New Year.