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

  • Thread starter Thread starter Steven Scaife
  • Start date Start date
S

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
 
thanks very much, i can adapt this for each of my forms where i need to
similar things.

Andy Cole said:
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

Steven Scaife said:
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
Click to expand...
Click to expand...
 
Glad to be of help

Andy

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

Andy Cole said:
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

Steven Scaife said:
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
Click to expand...
gives
me
but i forgot to change it.

what is it that i am missing?

thanks in advance for any help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top