Updating a table through vba for After Instert() of a form 2

[nerd19]

i have a form that is tracking some parts, and the user will enter a machine name, the new serial number that is going to be used, and the old serial that they are bringing back. What i need to do is automatically update the status of each of those serials. The table that the form is bound to is a "tool change" logging table and that is used to track all of the changes, and the status field of the serials is in a "tools" table, which is used to track information specific to the tools. So right now i am using:

Private Sub Form_AfterInsert()
[Forms]![BM & Tool Change Form v6]![Tools status out].[Form]![Status] = "Out"
End Sub

This is in the mainform and it calls a subform that changes the tools status to "out". i have another form that does the same thing but changes the status to "in". So what i get now is in the "tools" table it will create a new record with the corresponding serial number and the new status. What i need it to do is to update/edit the corresponding record and its previous status, not make a new one.

now i know that this may not be the most efficient way for this specific action but my table layout is best for the rest of the project. Thanks for the help.

 

[Remou]

You need something on the lines of:

[tt]Set db=CurrentDB
strSQL="Update tblTools Set Status='Out' Where ID=" & Me.ID
db.Execute strSQL, dbFailOnError[/tt]

 

[nerd19]

do those go in the after insert() of the mainform then? also do i need to change CurrentDB to the actual name of my database? ... thats the right idea tho, thanks!

 

[Remou]

CurrentDB means this db, it is not likely that you will need to change it. It is difficult to say which event best suits your application. After Insert may be best, but After Update is another event to consider.

 

[nerd19]

I forgot to mention i also get an error that is: Runtime 3061 Too Few Parameters. Expected 1. and when i pull up the debugger it highlights the:

db.Execute strSQL, dbFailOnError

i tried putting brackets around the strSQL and dbFailOnError, so i dont know the right syntax

 

[PHV]

Seems that ID is not defined as numeric:

strSQL = "UPDATE tblTools SET Status='Out' WHERE ID='" & Me!ID & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nerd19]

Now when i run it i get, run-time error 3075: sytax error in string in query expression "[Serial]=A253".

 

[nerd19]

Private Sub Form_AfterInsert()
Set db = CurrentDb
strSQL = "UPDATE [Tools v3] SET [Status]='Out' WHERE [Serial]=" & Me.[Serial New] & "'"
strSQL1 = "UPDATE [Tools v3] SET [Status]='In' WHERE [Serial]=" & Me.[Serial Old] & "'"

DoCmd.RunSQL (strSQL)
DoCmd.RunSQL (strSQL1)
End Sub

 

[PHV]

strSQL = "UPDATE [Tools v3] SET [Status]='Out' WHERE [Serial]=[!]'[/!]" & Me.[Serial New] & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nerd19]

it confirms to updates zero rows.

 

[nerd19]

got it....had to put a DoCmd.Save at the end