使用INSERT INTO语句时如何覆盖旧记录？ [英] How to overwrite an old record when using INSERT INTO statement?
问题描述
My Access DB处理产品配方和规格。在公式表格中，许多文本框（例如成本或成分声明）是基于公式中每个成分数据的计算字段。
一旦公式完成，它就成为一种成分。成分表（表名TProduct）中的新成分ID和版本应该继承公式ID和版本。我构建了一个命令按钮，使用INSERT INTO语句将ID，版本，成本和其他信息从公式表单复制到成分表单。
但是，我遇到了一个问题。如果记录已存在，则INSERT INTO语句不会更新现有记录中的新信息。用户不会注意到成分表中的记录未更新。
我不知道如何修改代码（1）警告用户在Ingredient表中存在相同的ID和Version; （2）删除现有的配料记录; （3）使用公式表中的更新信息创建新记录。
以下是我当前的INSERT INTO代码。有一个简单的方法来处理这个？谢谢。
My Access DB handles product formulation and specification. In the formula form, many text boxes, such as Cost or ingredient statement, are calculated fields based on each ingredient data in the formula.
Once a formula is complete, it becomes an ingredient. The new ingredient ID and Version in the ingredient table (table name TProduct) is supposed to inherit the formula ID and Version. I built a command button to copy ID, Version, cost, and other information from Formula form to Ingredient form using INSERT INTO statement.
However, I am stuck with one issue. If the record already exists, INSERT INTO statement does not update new information in the existing record. User will not notice that the record in Ingredient Table is not updated.
I don’t know how to modify the code to (1) warn user that same ID and Version exists in Ingredient table; (2) delete the existing ingredient record; (3) create a new record with updated information from formula table.
Below is my current INSERT INTO code. Is there a simple way to handle this? Thanks.
推荐答案
Unfortunatley Access没有类似INSERT OR的内容IGNORE"像SQLite，但你可以做的是
1）根据PK做一个DLOOKUP
2）如果DLOOKUP返回NULL（即：未找到）执行步骤3否则执行第4步
3）INSERT语句
4）更新声明
欢呼，
Unfortunatley Access does not have something like "INSERT OR IGNORE" like SQLite but what you could do is
1) do a DLOOKUP based on the PK
2) If the DLOOKUP returns NULL (ie: not found) the do step 3 else do step 4
3) INSERT statement
4) UPDATE statement
cheers,
当您用INSERT记录替换现有记录时，您可以先发出DELETE * FROM tblX x WHERE EXISTS（从tblY y中选择ID，其中x.id = y.id）
这将删除欺骗，INSERT将添加最新信息。
When you replace the existing record with the INSERT record, you could first issue a DELETE * FROM tblX x WHERE EXISTS (select ID from tblY y where x.id = y.id)
This will remove the dupes and the INSERT will add them with the most recent information.
虽然Nico5038的方法看起来更容易，但我已经使用mshmyob''完成了它方法。这两种方法都是我的vba期刊的宝贵教训。
感谢你们两位。
Joe
Although Nico5038''s method looks easier, I have already got it done using mshmyob''s method. Both methods are valuable lesson for my vba journal.
Thanks for both of you.
Joe
这篇关于使用INSERT INTO语句时如何覆盖旧记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
