如果访问中不存在则插入 [英] INSERT IF NO EXISTS in access

问题描述

我有一个 [Orders] 表格，您可以在其中注册要保存在 [Orders] 表中的订单.在该表格中有 2 个字段；您从组合框中选择了提供商的名称，当您这样做时，该提供商的公司代码会自动填充一个名为 [providers] 的表中的 dlookup.

I have an [Orders] form where you register orders to be saved in an [Orders] table. Within that form there are 2 fields; you chose a provider's name from a combo box, and when you do that the company code of that provider is autofilled with a dlookup from a table called [providers].

不过，有时订单是针对新供应商的，其信息不在我的表格中.在这种情况下，用户必须手动输入名称和代码.如何使用此信息将其作为 [providers] 表中的新记录插入，以便下次出现此提供程序时，他的信息会在组合框中给出?

Sometimes, though, the order is for a NEW provider whose info is not in my table. In that case, the user has to input the name and code manually. How can I use this info to INSERT this as a new record in the [providers] table, so that the next time this provider appears his info is given in the combo box ?

有人告诉我:INSERT ....IF NOT EXISTS .. 但我似乎无法在 VBA 查询中写出来.意义;我想将我的 (Me!providers) 和 (Me!code) 插入 [providers] 表.我尝试了以下 SQL 语句:

I was told about: INSERT ....IF NOT EXISTS .. but i can't seem to write that in a VBA query. Meaning; I want to insert my (Me!providers) and (Me!code) into [providers] table. I tried the following SQL statement:

INSERT INTO providers (provider,code) VALUES ('"&Me!provider&"','"&Me!code&"') IF NOT EXISTS

但这没有用.任何人都可以帮我正确的 SQL 吗?

but that didnt work. Can anyone please help me with the proper SQL?

推荐答案

我会这样做:检查代码是否存在于 Orders 表中，如果不存在，那么运行您的插入到 SQL.您可能需要稍微尝试一下，具体取决于您的代码字段是 TEXT 还是 INT，但这应该可以帮助您完成大部分工作.

I would do something like this: check to see if the code exists in the Orders table and if it doesn't, then run your Insert Into SQL. You might have to play with this a bit, depending on if your Code field is a TEXT or an INT, but this should get you most of the way there.

Dim db as Database
Dim rec as Recordset
Dim sSQL as String

Set db = CurrentDB
Set rec = db.OpenRecordset("Select * from Orders WHERE Code = '" & Me.Code & "'")

'This refreshes the dataset so you can get an accurate record count
rec.MoveFirst
rec.MoveLast

'If your record count is 0, then the code isn't in the DB yet so you need to add it
If rec.RecordCount = 0 Then
  sSQL = "INSERT INTO providers (provider,code) VALUES ('"&Me!provider&"','"&Me!code&"')";
  DoCmd.RunSQL sSQL
EndIf

'Always set your connection variables to Nothing so the connection closes!
Set db = Nothing
Set rec = Nothing

这篇关于如果访问中不存在则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！