Using seek to find if a record exists

[Kevin Brammer]

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.OpenR ecordset("tblGr oups")

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

[Allen Browne]

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("GroupI D", "tblGroups" , strWhere)
If Not IsNull(varResul t) 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.co m> wrote in message
news:MP******** ********@tornad o.socal.rr.com. ..

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.OpenR ecordset("tblGr oups")

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

[(PeteCresswell)]

Per Allen Browne:

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.

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(b yVal theEmployeeNumb er As Long) As Boolean

Dim myDB as DAO.Database
Dim myRS As DAO.Recordset

Set myDB = dbEngine(0).Ope nDatabase("M:\W hatever\Payroll .mdb")

Set myRS = myDB.OpenRecord set("tblEmploye e", dbOpenTable)
With myRS
.Index = "PrimaryKey "
.Seek "=", theEmployeeNumb er
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

[Lyle Fairfield]

(PeteCresswell) wrote:

Per Allen Browne:

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.

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(b yVal theEmployeeNumb er As Long) As Boolean

Dim myDB as DAO.Database
Dim myRS As DAO.Recordset

Set myDB = dbEngine(0).Ope nDatabase("M:\W hatever\Payroll .mdb")

Set myRS = myDB.OpenRecord set("tblEmploye e", dbOpenTable)
With myRS
.Index = "PrimaryKey "
.Seek "=", theEmployeeNumb er
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

-------------------------------------------------------------------

When ducks honk they are geese.

--
Lyle Fairfield

[summerwind]

Hi Kevin

I wouldn't use Seek at all.

Just open your recordset based on "WHERE GroupName = " & QUOTE &
Me.txtGroupName & QUOTE

The QUOTE is a global that I use which is simply a double set of
quotation marks. When using a string as a parameter for an SQL
statement it needs to be enclosed in these double quotes. That is why
you are getting your mismatch error.

After opening the recordset, your next line is:

If rsGroups.BOF and rsGroups.EOF then
'You've got an empty recordset and therefore the group isn't in
the table
' Do what you wanndo
end if

[Steve]

Kevin, I'm going to join this mishmash of suggestions, although you
have probably already implemented one. What I would do is simply make
the GroupName a primary key (No Duplicates) in your underlying table.
You would just have an extra primary key (as opposed to making
GroupName your ONLY primary key). In this approach, any effort to add a
record with the same GroupName as an existing one would result in an
error. On Error Resume next works well, but you could trap that
specific error if you wanted to.

[David W. Fenton]

Kevin Brammer <kd*****@msn.co m> wrote in
news:MP******** ********@tornad o.socal.rr.com:

I'm trying to use seek to check for the existence of a record
before saving

Why not just do a SELECT on the values that you're trying to match,
and if the result has a non-zero number of records, you know it's a
duplicate?

I never use SEEK. Ever.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Steve" <th*********@gm ail.com> wrote in
news:11******** **************@ o13g2000cwo.goo glegroups.com:

Kevin, I'm going to join this mishmash of suggestions, although
you have probably already implemented one. What I would do is
simply make the GroupName a primary key (No Duplicates) in your
underlying table. You would just have an extra primary key (as
opposed to making GroupName your ONLY primary key). In this
approach, any effort to add a record with the same GroupName as an
existing one would result in an error. On Error Resume next works
well, but you could trap that specific error if you wanted to.

This response confuses me.

A primary key, by definition, can only be "primary" if there's only
one of them in a table. Suggesting adding another primary key would
be like saying "this is the first, and this is the other first."

I believe what you mean is adding a unique index on the other field.
The downside of this is that for that to work, it has to prohibit
Nulls.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Br]

David W. Fenton wrote:

"Steve" <th*********@gm ail.com> wrote in
news:11******** **************@ o13g2000cwo.goo glegroups.com:

Kevin, I'm going to join this mishmash of suggestions, although
you have probably already implemented one. What I would do is
simply make the GroupName a primary key (No Duplicates) in your
underlying table. You would just have an extra primary key (as
opposed to making GroupName your ONLY primary key). In this
approach, any effort to add a record with the same GroupName as an
existing one would result in an error. On Error Resume next works
well, but you could trap that specific error if you wanted to.

This response confuses me.

A primary key, by definition, can only be "primary" if there's only
one of them in a table. Suggesting adding another primary key would
be like saying "this is the first, and this is the other first."

I believe what you mean is adding a unique index on the other field.
The downside of this is that for that to work, it has to prohibit
Nulls.

Hehe, yes you can only have one primary key :)
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Kevin]

On Thu, 22 Dec 2005 15:16:35 -0600, David W. Fenton wrote:

Kevin Brammer <kd*****@msn.co m> wrote in
news:MP******** ********@tornad o.socal.rr.com:

I'm trying to use seek to check for the existence of a record before
saving

Why not just do a SELECT on the values that you're trying to match, and if
the result has a non-zero number of records, you know it's a duplicate?

I never use SEEK. Ever.

I saw that in one of my searches as well. I may have to give it a try,
thanks!

Kevin