Here's my dilemma, I usually use drop down menus to do selections, but the db guy who has been helping me likes things to be built the same way he does them. Apparently he believes that drop down menus to choose from is "too dangerous" and "easy to change" and recommended I use a edit/update query to modify the data in the record to add a secondary step to the process. Frankly I havent done one before-Im no expert and still learning. Essentially I have this:
The "VID" (vendor ID) is pulled out for both Old and New out of the 09_Vendor table
The "Worksheet" needs to contain the OldVendor and NewVendor IDs to link to the 09 table data.
Initially I had them choose the Old and New based on the combo boxes with a refresh on click that would update the two (new and old) subforms from 09 linked by the VIDs to show the vendor info).
Basically is there a way to do this:
ComboNew - choose vendor (doesnt send info to worksheet)
Button-with "expression" that would then send that info across based on the VID and put it in the NewVendor field, with refresh that updates the subform on the main form.
Do I need to do an update query and if so how? I thought they only updated all records...not specific ones.
Do you have a table that maps OldID to NewID, if so you could run an update query to update your ID to NewID where ID = OldID. If this is the case you will have to be carefull you do not break your database integrity by leaving current ID in child tables with no link back to parent table.