setting value to a field in a different table than the form is linked to.

[Steven Scaife]

I need to subtract 2 values and then stick the value in a table that isnt
linked to the form. i have tried

[Book]![In_Stock] = [Book]![In_Stock] - [txtQuantityBook]

but have realised that it wont work because it doesnt give a row. So i
tried creating a recordset and use SQL instead but i get an error 2467 the
expression refers to an object that doesnt exist or is closed, using this
code

Dim rst As Recordset
Dim strSQL As String
strSQL = "UPDATE Book SET In_Stock ='" & txtQuantityBook & "' WHERE
Book_Title = '" & Combo12 & "'"
rst.Update 'i know i shouldnt use the default name that access gives me
but i forgot to change it.

what is it that i am missing?

thanks in advance for any help

 

[Steven Scaife]

thanks very much, i can adapt this for each of my forms where i need to
similar things.

Steven

You would normally only update the "In Stock" field when you are about to
commit the changes to the database. Use the following in the Form's
BeforeUpdate after any other checks to ensure that the data is valid. I've
assumed that the "In Stock" field is numeric and that you are using DAO

Dim db As Database
Dim strSQL As String

strSQL = "UPDATE Book SET Book.In_Stock = Book.In_Stock - " &
Me.txtQuantityBook & " WHERE Book.Book_Title = '" & Me.Combo12 & "'"
Set db = CurrentDB()
db.Execute strSQL, dbFailOnError
Set db = Nothing

The dbFailOnError will produce a trappable error should the update fail for
any reason. You'll need to add error trapping to the code

HTH

Andy

I need to subtract 2 values and then stick the value in a table that isnt
linked to the form. i have tried

[Book]![In_Stock] = [Book]![In_Stock] - [txtQuantityBook]

but have realised that it wont work because it doesnt give a row. So i
tried creating a recordset and use SQL instead but i get an error 2467 the
expression refers to an object that doesnt exist or is closed, using this
code

Dim rst As Recordset
Dim strSQL As String
strSQL = "UPDATE Book SET In_Stock ='" & txtQuantityBook & "' WHERE
Book_Title = '" & Combo12 & "'"
rst.Update 'i know i shouldnt use the default name that access gives me
but i forgot to change it.

what is it that i am missing?

thanks in advance for any help

 

[Andy Cole]

Glad to be of help

Andy

thanks very much, i can adapt this for each of my forms where i need to
similar things.

Steven

You would normally only update the "In Stock" field when you are about to
commit the changes to the database. Use the following in the Form's
BeforeUpdate after any other checks to ensure that the data is valid. I've
assumed that the "In Stock" field is numeric and that you are using DAO

Dim db As Database
Dim strSQL As String

strSQL = "UPDATE Book SET Book.In_Stock = Book.In_Stock - " &
Me.txtQuantityBook & " WHERE Book.Book_Title = '" & Me.Combo12 & "'"
Set db = CurrentDB()
db.Execute strSQL, dbFailOnError
Set db = Nothing

The dbFailOnError will produce a trappable error should the update fail for
any reason. You'll need to add error trapping to the code

HTH

Andy

I need to subtract 2 values and then stick the value in a table that isnt
linked to the form. i have tried

[Book]![In_Stock] = [Book]![In_Stock] - [txtQuantityBook]

but have realised that it wont work because it doesnt give a row. So i
tried creating a recordset and use SQL instead but i get an error 2467 the
expression refers to an object that doesnt exist or is closed, using this
code

Dim rst As Recordset
Dim strSQL As String
strSQL = "UPDATE Book SET In_Stock ='" & txtQuantityBook & "' WHERE
Book_Title = '" & Combo12 & "'"
rst.Update 'i know i shouldnt use the default name that access

gives
me

but i forgot to change it.

what is it that i am missing?

thanks in advance for any help