update query code (Access 2000)

Topic

New Reply

I have an update query which works, it is the following:

UPDATE products SET products.branch0 = [stock], products.items0 = [items];

However, when i want to express it with a function it seems that it doesnt work.

My funnction is the following:

Public Function MyUpdate()
Dim strSQL As String
strSQL = ” UPDATE products SET products.branch0 = [stock], products.items0 = [items]”
CurrentDb.Execute strSQL
End Function

Why is it so? and why the function is wrong?

Reply | Quote

Replies

You haven’t provided enough information. What do you mean it doesn’t work? Do you get an error message, do fields not get updated, or what? What are [stock] and [items]? Are they fields in the products table or something else?

Reply | Quote

The fields stock ,items,branch0 and item0 are fields in the table products.You could see that from my query where i write products.stock, which means this is a field stock from the table products.Also i do not receive any error, but the fields are not updated.Also,as i mentioned ,the query i have shown is working, but i cannot explain myself why the function is not working, which means that the function is not updating.

Reply | Quote

[indent]

---

The fields stock ,items,branch0 and item0 are fields in the table products. You could see that from my query where i write products.stock

---

[/indent] You didn’t write products.stock anywhere in your post

So then this is your layout?
table name
products
field list
stock
items
branch0
item0

This brings the question, why do you want a pair of identical fields in the same table, branch0=stock and items=item0?

[indent]

---

Also i do not receive any error, but the fields are not updated.Also,as i mentioned ,the query i have shown is working, but i cannot explain myself why the function is not working, which means that the function is not updating.

---

[/indent] That may be obvious to you, but we have no way of knowing that you didn’t receive any error messages, or that the field didn’t update to a vaule you weren’t expecting or any of a dozen other possible reasons that you would cause you to say that the function doesn’t work.

All that being said, I would guess, based on the little information provided, is that your SQL statement should probably be:

strSQL = “UPDATE products SET products.branch0 = ” & [stock] & “, products.items0 = ” & [items] & “;”

Reply | Quote

Your suggestion did update ! Thank you.I feel obliged to expplain why i am doing this.The reason is that one ofmy fields will be obsolete in the future the other is brand new and i want to copy the availabe information
best regards

Reply | Quote