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

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
 
S

Steven Scaife

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...
 
S

Steven Scaife

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...
 
A

Andy Cole

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...
 
A

Andy Cole

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Doughnut Chart linked with the slicer in the pivot table 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Access Returns different values if code is run from "close" event 8
Save selected items NOT selected in an Extended List Box based onother condition 6
Can't seem to retrieve primary key number of newly created record in VB 1
Filtered Records as source for appending records to a table 1
Using a Form to Mass Update a Table 1
Setting field value if a value exists in another table 3

Top