使用seek查找是否存在记录 [英] Using seek to find if a record exists
问题描述
我正在尝试使用seek来检查
保存之前是否存在记录，因此没有重复的条目（还有其他方法吗？）。我/ b $ b有一个群组桌子，已经
GroupID
岛
GroupName
我的代码权限现在给我一个类型不匹配错误，这就是
看起来像：
Dim rsGroups作为记录集
设置rsGroups = CurrentDb.OpenRecordset（" ; tblGroups"）
rsGroups.Seek Form_frmGroups.txtGroupName
如果rsGroups.EOF那么
MsgBox"找不到值
''DoCmd.Save
''Form_frmGroups.AllowAdditions = False
''Form_frmGroups.AllowEdits =错误
其他
MsgBox已在数据库中找到的价值
结束如果
有人可以提供一些建议或提示吗？
凯文
I''m trying to use seek to check for the existence of a record before
saving, so there are no duplicate entries (is there another way?). I
have a "groups" table, which has
GroupID
Island
GroupName
My code right now is giving me a "type mismatch" error, and this is what
it looks like:
Dim rsGroups As Recordset
Set rsGroups = CurrentDb.OpenRecordset("tblGroups")
rsGroups.Seek Form_frmGroups.txtGroupName
If rsGroups.EOF Then
MsgBox "Value not found"
'' DoCmd.Save
'' Form_frmGroups.AllowAdditions = False
'' Form_frmGroups.AllowEdits = False
Else
MsgBox "Value already found in database"
End If
Can anyone offer some suggestions or hints?
Kevin
推荐答案
To使用Seek，你必须指定索引名称（必须在
正在寻找的字段上），然后你必须指定运算符（可能是=。）
但这种方法不是很灵活，例如如果你以后拆分
数据库失败，因为Seek只适用于dbOpenTable类型的记录集
（即没有附加表）。一个DLookup（）会更简单更多灵活。
这个例子假设tblGroups有一个名为GroupID的主键，如果值没有改变，那么
就会避免搜索（所以现有的记录确实如此）
找不到自己）：
Dim strWhere as String
Dim varResult As Variant
With Me.txtGroupName
如果IsNull（.Value）或（.Value = .OldValue）那么
''什么都不做
其他
strWhere =" [GroupName] =""" &安培; .Value& """
varResult = DLookup（" GroupID"，" tblGroups"，strWhere）
如果不是IsNull（varResult）那么
MsgBox" Group" &安培; varResult& "具有相同的价值。
结束如果
结束如果
结束
注意：如果GroupName是一个数字字段（不是文本字段），则丢失额外的
引号：
strWhere =" [GroupName] =" &安培; .Value
-
Allen Browne - 微软MVP。西澳大利亚州珀斯。
访问用户提示 - http：// allenbrowne.com/tips.html
回复群组，而不是mvps dot org的allenbrowne。
" Kevin Brammer" < KD ***** @ msn.com>在消息中写道
新闻：MP **************** @ tornado.socal.rr.com ...
To use Seek, you must specify the index name (which must be on the field you
are seeking), and then you must specify the operator (probably "=".)
But that approach is not very flexible, e.g. if fails if you split the
database later, because Seek works only on recordsets of type dbOpenTable
(i.e. not attached tables.) A DLookup() would be simpler and more flexible.
This example assumes tblGroups has a primary key named GroupID, and it
avoids the search if the value has not changed (so an existing record does
not find itself):
Dim strWhere As String
Dim varResult As Variant
With Me.txtGroupName
If IsNull(.Value) Or (.Value = .OldValue) Then
''do nothing
Else
strWhere = "[GroupName] = """ & .Value & """"
varResult = DLookup("GroupID", "tblGroups", strWhere)
If Not IsNull(varResult) Then
MsgBox "Group" & varResult & " has the same value."
End If
End If
End With
Note: If GroupName is a Number field (not a Text field), lose the extra
quotes:
strWhere = "[GroupName] = " & .Value
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Kevin Brammer" <kd*****@msn.com> wrote in message
news:MP****************@tornado.socal.rr.com...
我在保存之前，我试图使用seek检查是否存在记录，因此没有重复的条目（还有其他方法吗？）。我有一个群组表，其中有GroupID
岛
GroupName
我的代码现在正在给我一个类型不匹配的表格。错误，这就是它的样子：
Dim rsGroups作为记录集
设置rsGroups = CurrentDb.OpenRecordset（" tblGroups"）
rsGroups.Seek Form_frmGroups.txtGroupName
如果rsGroups.EOF那么
MsgBox找不到值
''DoCmd.Save
''Form_frmGroups .AllowAdditions = False
''Form_frmGroups.AllowEdits = False
其他
MsgBox已在数据库中找到的值
结束如果
任何人都可以提供一些建议或提示吗？
Kevin
I''m trying to use seek to check for the existence of a record before
saving, so there are no duplicate entries (is there another way?). I have
a "groups" table, which has
GroupID
Island
GroupName
My code right now is giving me a "type mismatch" error, and this is what
it looks like:
Dim rsGroups As Recordset
Set rsGroups = CurrentDb.OpenRecordset("tblGroups")
rsGroups.Seek Form_frmGroups.txtGroupName
If rsGroups.EOF Then
MsgBox "Value not found"
'' DoCmd.Save
'' Form_frmGroups.AllowAdditions = False
'' Form_frmGroups.AllowEdits = False
Else
MsgBox "Value already found in database"
End If
Can anyone offer some suggestions or hints?
Kevin
Per Allen Browne：
Per Allen Browne:
要使用Seek，您必须指定索引名称（必须在您正在寻找的字段上），然后您必须指定运算符（可能是=。）
<但是，这种方法不是很灵活，例如如果以后拆分
数据库失败，因为Seek仅适用于dbOpenTable类型的记录集
（即没有附加表。）DLookup（）会更简单，更灵活。
To use Seek, you must specify the index name (which must be on the field you
are seeking), and then you must specify the operator (probably "=".)
But that approach is not very flexible, e.g. if fails if you split the
database later, because Seek works only on recordsets of type dbOpenTable
(i.e. not attached tables.) A DLookup() would be simpler and more flexible.
实际上，你可以。查看附表。
诀窍是调暗/设置指向表所在数据库的指针。
例如
----------------------------- --------------------------------------
Public Function EmployeeExist（ byVal theEmployeeNumber As Long）As Boolean
将myDB作为DAO.Database调暗
将myRS视为DAO.Recordset
设置myDB = dbEngine（0）.OpenDatabase（" M：\Whatever\Payroll.mdb"）
设置myRS = myDB.OpenRecordset（" tblEmployee"，dbOpenTable ）
with myRS
.Index =" PrimaryKey"
.Seek" =" ;,EmployeeNumber
如果.NoMatch = False那么
EmployeeExist = True
结束如果
（或者，如果你想变得可爱：EmployeeExist = Not .NoMatch
。关闭
结束
设置myRS = Nothing
设置myDB = Nothing
结束功能
----- -------------------------------------------------- ------------
-
PeteCresswell
Actually, you can .Seek an attached table.
The trick is to Dim/Set a pointer to the database that the table is in.
e.g.
-------------------------------------------------------------------
Public Function EmployeeExist(byVal theEmployeeNumber As Long) As Boolean
Dim myDB as DAO.Database
Dim myRS As DAO.Recordset
Set myDB = dbEngine(0).OpenDatabase("M:\Whatever\Payroll.mdb" )
Set myRS = myDB.OpenRecordset("tblEmployee", dbOpenTable)
With myRS
.Index = "PrimaryKey"
.Seek "=", theEmployeeNumber
If .NoMatch = False Then
EmployeeExist = True
End If
(or, if you want to be cute: EmployeeExist = Not .NoMatch
.Close
End With
Set myRS = Nothing
set myDB = Nothing
End Function
-------------------------------------------------------------------
--
PeteCresswell
（PeteCresswell）写道：
(PeteCresswell) wrote:
Per Allen Browne：
Per Allen Browne:
要使用Seek，您必须指定索引名称（必须在您正在寻找的字段上），然后您必须指定运算符（可能是=。）
但这种方法不是很灵活，例如如果以后拆分
数据库失败，因为Seek仅适用于dbOpenTable类型的记录集
（即没有附加表。）DLookup（）会更简单，更灵活。
To use Seek, you must specify the index name (which must be on the field you
are seeking), and then you must specify the operator (probably "=".)
But that approach is not very flexible, e.g. if fails if you split the
database later, because Seek works only on recordsets of type dbOpenTable
(i.e. not attached tables.) A DLookup() would be simpler and more flexible.
实际上，你可以。查看附表。
诀窍是Dim / Set指向表所在数据库的指针。
例如
--------------------------------------------- ----------------------
Public Function EmployeeExist（byVal theEmployeeNumber As Long）as Boolean
将myDB作为DAO调暗。数据库
DIM myRS作为DAO.Recordset
设置myDB = dbEngine（0）.OpenDatabase（" M：\Whatever\Payroll.mdb"）
>设置myRS = myDB.OpenRecordset（" tblEmployee"，dbOpenTable）
使用myRS
.Index =" PrimaryKey"
.Seek" =" ;,EmployeeNumber
如果.NoMatch = False然后
EmployeeExist = True
结束如果
（或者，如果你想变得可爱：EmployeeExist =不.NoMatch
。关闭
结束
设置myRS = Nothing
设置myDB = Nothing
结束功能
- -------------------------------------------------- ----------------
Actually, you can .Seek an attached table.
The trick is to Dim/Set a pointer to the database that the table is in.
e.g.
-------------------------------------------------------------------
Public Function EmployeeExist(byVal theEmployeeNumber As Long) As Boolean
Dim myDB as DAO.Database
Dim myRS As DAO.Recordset
Set myDB = dbEngine(0).OpenDatabase("M:\Whatever\Payroll.mdb" )
Set myRS = myDB.OpenRecordset("tblEmployee", dbOpenTable)
With myRS
.Index = "PrimaryKey"
.Seek "=", theEmployeeNumber
If .NoMatch = False Then
EmployeeExist = True
End If
(or, if you want to be cute: EmployeeExist = Not .NoMatch
.Close
End With
Set myRS = Nothing
set myDB = Nothing
End Function
-------------------------------------------------------------------
当鸭子鸣喇叭时，他们就是鹅。
-
Lyle Fairfield
When ducks honk they are geese.
--
Lyle Fairfield
这篇关于使用seek查找是否存在记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
