插入如果没有访问存在 [英] INSERT IF NO EXISTS in access

问题描述

我有[订单]表单，您可以在其中注册订单以保存在[订单]表格中。在这种形式中有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].

但是，订单是针对一个NEW提供者，而他的信息不在我的表格中。在这种情况下，用户必须手动输入名称和代码。如何使用此信息将[INSERT]作为[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 ?

我被告知有关：插入....如果不存在......但我似乎无法在VBA查询中写入。含义;我想将我的（我！提供者）和（我！代码）插入[提供者]表中。我尝试了下面的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表，如果没有，然后运行Insert Into 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屋！