Copy field from one table to another existing record in another table (1 Viewer)

RAY-IT

New member
Local time
Today, 05:52
Joined
Feb 7, 2022
Messages
3
I have an access DB acting as a front end to MySQL using ODBC Linked Tables. It's for a python based clocking in system generating the records. I have a Staff Maintenance form that allows HR to update staff information including assigning a Fob ID. The Fob ID is unique and when scanned on a fob reader is placed in a table called "scannedfob" in a field called "fob_number".

The form is called Staff Maintenance and edits information in the "users" table.

I would like to be able to scan a fob, have the linked table "scanned fob" refresh, copy the fob_number.scannedfob field into the users.rf_uid field for the current record I am looking at in the form and then delete the record out of the scannedfob table. I expect I will need a button to carry out these actions but not sure where to start really.

I should add, I am not a database specialist nor a programmer but an IT manager. I am learning quickly but not quick enough to get this finished! Any help greatly appreciated.

Thanks

Graham
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:52
Joined
May 7, 2009
Messages
19,169
does ScannedFob table has Autonumber field?
if you have, after scanning has been done you can get the Last scanned fob_number by using dlookup() function.
say ID is the autonumber field:

Dim UniqueID as Variant
UniqueID = DLookup("fob_number", "scannedfob", "ID = " & DMax("ID", "scannedfob")

if you have a form and want to put the UniqueID to rf_uid:

Me!rf_uid = UniqueID
 

RAY-IT

New member
Local time
Today, 05:52
Joined
Feb 7, 2022
Messages
3
Yes there is an auto number but it doesn't matter about finding the latest one really, the intention is that there will only ever be one record in there and that will get deleted as soon as the value is copied to the user table.
if you have a form and want to put the UniqueID to rf_uid:

Me!rf_uid = UniqueID
Well, I only want to do this if there isn't one in there already or the fob is being changed so I don't want anything to happen automatically.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:52
Joined
May 7, 2009
Messages
19,169
so simply dlookup it:

Dim UniqueID as Variant
UniqueID = DLookup("fob_number", "scannedfob")

Me!rf_Uid = UniqueID

'delete the 1 record from scannedfob
currentdb.execute "delete * from scannedfob;"
 

RAY-IT

New member
Local time
Today, 05:52
Joined
Feb 7, 2022
Messages
3
so simply dlookup it:

Dim UniqueID as Variant
UniqueID = DLookup("fob_number", "scannedfob")

Me!rf_Uid = UniqueID

'delete the 1 record from scannedfob
currentdb.execute "delete * from scannedfob;"

Thanks, that's working brilliantly thank you and I had not understood the concept of linked tables so I don't need to do a refresh either :)
 
Last edited:

Users who are viewing this thread

Top Bottom