2. ASPRunnerPro General questions
  3. Table to track changes in another table
This topic is locked

Table to track changes in another table

 		12/11/2008 2:42:13 PM
ASPRunnerPro General questions
I
 Inter1908 author

Hi all,

I would like to build a dynamic inventory database an I am not familiar with SQL VBScript....... <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=10384&image=1&table=forumtopics' class='bbc_emoticon' alt=':lol:' />

I have 03 Tables,

  • Inventory Table containining (Item, ItemSerialNumber, and Location) fields.
  • Moves master table containing (destination and otherinfo) fields
  • MoveDetails tables containing (Item, ItemSeriaNumber) fields
    I would like to select items from Inventory and insertthem into MoveDetails,then validate destination in Moves master table so Location field's value in Inventory will be changed to destination's value and changes stored in Moves and move detais as history.
    what is the best way to do it? can someone help me please (with code if possible)
    my best regards,

    Tarek <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=10384&image=2&table=forumtopics' class='bbc_emoticon' alt=';)' />
    ASPrunner Pro 6.0 build 672
J
 		Jane 12/12/2008

Hi,
you can check entered values in the Before record added/updated event on the Events tab for MoveDetails table and update Inventory table if needed.

Here is a just a sample:

Set rstmp = server.CreateObject("ADODB.Recordset")

Set rstmp2 = server.CreateObject("ADODB.Recordset")
'select location

str = "select Location from Inventory where Item=" & dict("Item")

rstmp.open str,dbConnection
'select destination

str2 = "select destination from Moves where MasterKey=" & dict("DetailKey")

rstmp2.open str2,dbConnection
'compare location and destination and update location field if needed

if rstmp("Location")<>rstmp2("destination") then

dbConnection.Execute "update Inventory set Location='" & rstmp2("destination") & "' where Item=" & dict("Item")

end if
rstmp.close

set rstmp=nothing

rstmp2.close

set rstmp2=nothing
I
 		Inter1908 author 12/12/2008

Thanks Jane for your fast answer,

I made some modifcations on your sample code, and I think it is working since its giving me results I was looking for <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=35935&image=1&table=forumreplies' class='bbc_emoticon' alt=':lol:' />

great forum btw
Regards,

Tarek