using .index and .seek function 
Author Message
 using .index and .seek function

I have taken over the programming of a database for EMS
trip records. Here is a sub in this database, I need help
understanding how he got it to work

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim db As Database
    Dim rstEmployeeSkillsInput As Recordset

    Set db = CurrentDb()
    Set rstEmployeeSkillsInput = db.OpenRecordset
("SkillSummaries")
    ' Determine if there is an entry in the skills
summary table
        rstEmployeeSkillsInput.Index
= "EmployeeTripRecord"
        rstEmployeeSkillsInput.Seek "=", [Driver/Pilot
Name], [MARF Number]
        If rstEmployeeSkillsInput.NoMatch Then
            rstEmployeeSkillsInput.AddNew
            rstEmployeeSkillsInput("Ticket Number") =
[MARF Number]
            rstEmployeeSkillsInput("EmployeeName") =
[Driver/Pilot Name]
            rstEmployeeSkillsInput("Prefix") = [Driver
Prefix]
            rstEmployeeSkillsInput("ID Number") =
[Driver/Pilot License Num]
            rstEmployeeSkillsInput("Driver/Attendant")
= "D"
            rstEmployeeSkillsInput("ALS/BLS Call") =
[ALS/BLS]
            rstEmployeeSkillsInput("Illness Assessment")
= [Illness Assessment]
            rstEmployeeSkillsInput.Update
        End If
      rstEmployeeSkillsInput.Close

    stDocName = "frmEmployeeSkillsInput"

    stLinkCriteria = "[Ticket Number]=" & "'" & Me![MARF
Number] & "'" & _
                     " AND [Prefix]=" & "'" & Me![Driver
Prefix] & "'" & _
                     " AND [ID Number]=" & "'" & Me!
[Driver/Pilot License Num] & "'"

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdRecordDriverSkills_Click:
    Exit Sub

Err_cmdRecordDriverSkills_Click:
    MsgBox Err.Description
    Resume Exit_cmdRecordDriverSkills_Click

The .index EmployeeTripRecord is not a field in the table
nor is it defined as a string anywhere else. Also the two
fileds it searches for matches [Driver/Pilot Name], [MARF
Number] two fields on the form that correspond two
seperate fields in the table, there is also one for
[Attendant1Name], [MARF Number] and [Attendant2Name],
[MARF Number].

My question is how does this function work when the index
is not even a field in the table that is being searched,
the Table name is SkillSummaries, the form name that this
sub is on is frmAmbulanceReporData. I would like to
reproduce this on other forms, but seem unable to,
everytime I try I get EmployeeTripRecord is not an
index.... or what ever name I try. This database was
originally written in access 97 and has been converted to
access 2000 and runs in access 2002 runtime on most
computers.

THank you for any help!



Sat, 15 Oct 2005 14:46:18 GMT  
 using .index and .seek function
Hi,

The "EmployeeTripRecord" name you talk about is an index
in your table. Indexes can be named and referred to.

Hope this helps

Martin

Quote:
>-----Original Message-----
>I have taken over the programming of a database for EMS
>trip records. Here is a sub in this database, I need
help
>understanding how he got it to work

>    Dim stDocName As String
>    Dim stLinkCriteria As String
>    Dim db As Database
>    Dim rstEmployeeSkillsInput As Recordset

>    Set db = CurrentDb()
>    Set rstEmployeeSkillsInput = db.OpenRecordset
>("SkillSummaries")
>    ' Determine if there is an entry in the skills
>summary table
>        rstEmployeeSkillsInput.Index
>= "EmployeeTripRecord"
>        rstEmployeeSkillsInput.Seek "=", [Driver/Pilot
>Name], [MARF Number]
>        If rstEmployeeSkillsInput.NoMatch Then
>            rstEmployeeSkillsInput.AddNew
>            rstEmployeeSkillsInput("Ticket Number") =
>[MARF Number]
>            rstEmployeeSkillsInput("EmployeeName") =
>[Driver/Pilot Name]
>            rstEmployeeSkillsInput("Prefix") = [Driver
>Prefix]
>            rstEmployeeSkillsInput("ID Number") =
>[Driver/Pilot License Num]
>            rstEmployeeSkillsInput("Driver/Attendant")
>= "D"
>            rstEmployeeSkillsInput("ALS/BLS Call") =
>[ALS/BLS]
>            rstEmployeeSkillsInput("Illness Assessment")
>= [Illness Assessment]
>            rstEmployeeSkillsInput.Update
>        End If
>      rstEmployeeSkillsInput.Close

>    stDocName = "frmEmployeeSkillsInput"

>    stLinkCriteria = "[Ticket Number]=" & "'" & Me![MARF
>Number] & "'" & _
>                     " AND [Prefix]=" & "'" & Me![Driver
>Prefix] & "'" & _
>                     " AND [ID Number]=" & "'" & Me!
>[Driver/Pilot License Num] & "'"

>    DoCmd.OpenForm stDocName, , , stLinkCriteria

>Exit_cmdRecordDriverSkills_Click:
>    Exit Sub

>Err_cmdRecordDriverSkills_Click:
>    MsgBox Err.Description
>    Resume Exit_cmdRecordDriverSkills_Click

>The .index EmployeeTripRecord is not a field in the
table
>nor is it defined as a string anywhere else. Also the
two
>fileds it searches for matches [Driver/Pilot Name],
[MARF
>Number] two fields on the form that correspond two
>seperate fields in the table, there is also one for
>[Attendant1Name], [MARF Number] and [Attendant2Name],
>[MARF Number].

>My question is how does this function work when the
index
>is not even a field in the table that is being searched,
>the Table name is SkillSummaries, the form name that
this
>sub is on is frmAmbulanceReporData. I would like to
>reproduce this on other forms, but seem unable to,
>everytime I try I get EmployeeTripRecord is not an
>index.... or what ever name I try. This database was
>originally written in access 97 and has been converted
to
>access 2000 and runs in access 2002 runtime on most
>computers.

>THank you for any help!

>.



Sat, 15 Oct 2005 17:38:13 GMT  
 using .index and .seek function
When I view the table in design view there is no field
named this, I do not know how to setup an index that is
not an actual field in the Table.
The Table Name is SkillsSummaries
Here are the table fields
Ticket Number     Text
Employee Name     Text
Prefix            Text
ID Number         Text

Following these four field are fif{*filter*} more that deal
with skills performed, none of which are named
EmployeeTripRecord

In fact not a single field is listed as Indexed, under
Index it says No

Quote:
>-----Original Message-----
>Hi,

>The "EmployeeTripRecord" name you talk about is an index
>in your table. Indexes can be named and referred to.

>Hope this helps

>Martin

>>-----Original Message-----
>>I have taken over the programming of a database for EMS
>>trip records. Here is a sub in this database, I need
>help
>>understanding how he got it to work

>>    Dim stDocName As String
>>    Dim stLinkCriteria As String
>>    Dim db As Database
>>    Dim rstEmployeeSkillsInput As Recordset

>>    Set db = CurrentDb()
>>    Set rstEmployeeSkillsInput = db.OpenRecordset
>>("SkillSummaries")
>>    ' Determine if there is an entry in the skills
>>summary table
>>        rstEmployeeSkillsInput.Index
>>= "EmployeeTripRecord"
>>        rstEmployeeSkillsInput.Seek "=", [Driver/Pilot
>>Name], [MARF Number]
>>        If rstEmployeeSkillsInput.NoMatch Then
>>            rstEmployeeSkillsInput.AddNew
>>            rstEmployeeSkillsInput("Ticket Number") =
>>[MARF Number]
>>            rstEmployeeSkillsInput("EmployeeName") =
>>[Driver/Pilot Name]
>>            rstEmployeeSkillsInput("Prefix") = [Driver
>>Prefix]
>>            rstEmployeeSkillsInput("ID Number") =
>>[Driver/Pilot License Num]
>>            rstEmployeeSkillsInput("Driver/Attendant")
>>= "D"
>>            rstEmployeeSkillsInput("ALS/BLS Call") =
>>[ALS/BLS]
>>            rstEmployeeSkillsInput("Illness
Assessment")
>>= [Illness Assessment]
>>            rstEmployeeSkillsInput.Update
>>        End If
>>      rstEmployeeSkillsInput.Close

>>    stDocName = "frmEmployeeSkillsInput"

>>    stLinkCriteria = "[Ticket Number]=" & "'" & Me!
[MARF
>>Number] & "'" & _
>>                     " AND [Prefix]=" & "'" & Me!
[Driver
>>Prefix] & "'" & _
>>                     " AND [ID Number]=" & "'" & Me!
>>[Driver/Pilot License Num] & "'"

>>    DoCmd.OpenForm stDocName, , , stLinkCriteria

>>Exit_cmdRecordDriverSkills_Click:
>>    Exit Sub

>>Err_cmdRecordDriverSkills_Click:
>>    MsgBox Err.Description
>>    Resume Exit_cmdRecordDriverSkills_Click

>>The .index EmployeeTripRecord is not a field in the
>table
>>nor is it defined as a string anywhere else. Also the
>two
>>fileds it searches for matches [Driver/Pilot Name],
>[MARF
>>Number] two fields on the form that correspond two
>>seperate fields in the table, there is also one for
>>[Attendant1Name], [MARF Number] and [Attendant2Name],
>>[MARF Number].

>>My question is how does this function work when the
>index
>>is not even a field in the table that is being
searched,
>>the Table name is SkillSummaries, the form name that
>this
>>sub is on is frmAmbulanceReporData. I would like to
>>reproduce this on other forms, but seem unable to,
>>everytime I try I get EmployeeTripRecord is not an
>>index.... or what ever name I try. This database was
>>originally written in access 97 and has been converted
>to
>>access 2000 and runs in access 2002 runtime on most
>>computers.

>>THank you for any help!

>>.

>.



Sun, 16 Oct 2005 14:34:41 GMT  
 using .index and .seek function
In design view for the table, right click on the title bar of the
table window and choose Indexes from the dropdown menu. You should
find an Index with the appropriate name listed.

Quote:

>When I view the table in design view there is no field
>named this, I do not know how to setup an index that is
>not an actual field in the Table.
>The Table Name is SkillsSummaries
>Here are the table fields
>Ticket Number     Text
>Employee Name     Text
>Prefix            Text
>ID Number         Text

>Following these four field are fif{*filter*} more that deal
>with skills performed, none of which are named
>EmployeeTripRecord

>In fact not a single field is listed as Indexed, under
>Index it says No
>>-----Original Message-----
>>Hi,

>>The "EmployeeTripRecord" name you talk about is an index
>>in your table. Indexes can be named and referred to.

>>Hope this helps

>>Martin

>>>-----Original Message-----
>>>I have taken over the programming of a database for EMS
>>>trip records. Here is a sub in this database, I need
>>help
>>>understanding how he got it to work

>>>    Dim stDocName As String
>>>    Dim stLinkCriteria As String
>>>    Dim db As Database
>>>    Dim rstEmployeeSkillsInput As Recordset

>>>    Set db = CurrentDb()
>>>    Set rstEmployeeSkillsInput = db.OpenRecordset
>>>("SkillSummaries")
>>>    ' Determine if there is an entry in the skills
>>>summary table
>>>        rstEmployeeSkillsInput.Index
>>>= "EmployeeTripRecord"
>>>        rstEmployeeSkillsInput.Seek "=", [Driver/Pilot
>>>Name], [MARF Number]
>>>        If rstEmployeeSkillsInput.NoMatch Then
>>>            rstEmployeeSkillsInput.AddNew
>>>            rstEmployeeSkillsInput("Ticket Number") =
>>>[MARF Number]
>>>            rstEmployeeSkillsInput("EmployeeName") =
>>>[Driver/Pilot Name]
>>>            rstEmployeeSkillsInput("Prefix") = [Driver
>>>Prefix]
>>>            rstEmployeeSkillsInput("ID Number") =
>>>[Driver/Pilot License Num]
>>>            rstEmployeeSkillsInput("Driver/Attendant")
>>>= "D"
>>>            rstEmployeeSkillsInput("ALS/BLS Call") =
>>>[ALS/BLS]
>>>            rstEmployeeSkillsInput("Illness
>Assessment")
>>>= [Illness Assessment]
>>>            rstEmployeeSkillsInput.Update
>>>        End If
>>>      rstEmployeeSkillsInput.Close

>>>    stDocName = "frmEmployeeSkillsInput"

>>>    stLinkCriteria = "[Ticket Number]=" & "'" & Me!
>[MARF
>>>Number] & "'" & _
>>>                     " AND [Prefix]=" & "'" & Me!
>[Driver
>>>Prefix] & "'" & _
>>>                     " AND [ID Number]=" & "'" & Me!
>>>[Driver/Pilot License Num] & "'"

>>>    DoCmd.OpenForm stDocName, , , stLinkCriteria

>>>Exit_cmdRecordDriverSkills_Click:
>>>    Exit Sub

>>>Err_cmdRecordDriverSkills_Click:
>>>    MsgBox Err.Description
>>>    Resume Exit_cmdRecordDriverSkills_Click

>>>The .index EmployeeTripRecord is not a field in the
>>table
>>>nor is it defined as a string anywhere else. Also the
>>two
>>>fileds it searches for matches [Driver/Pilot Name],
>>[MARF
>>>Number] two fields on the form that correspond two
>>>seperate fields in the table, there is also one for
>>>[Attendant1Name], [MARF Number] and [Attendant2Name],
>>>[MARF Number].

>>>My question is how does this function work when the
>>index
>>>is not even a field in the table that is being
>searched,
>>>the Table name is SkillSummaries, the form name that
>>this
>>>sub is on is frmAmbulanceReporData. I would like to
>>>reproduce this on other forms, but seem unable to,
>>>everytime I try I get EmployeeTripRecord is not an
>>>index.... or what ever name I try. This database was
>>>originally written in access 97 and has been converted
>>to
>>>access 2000 and runs in access 2002 runtime on most
>>>computers.

>>>THank you for any help!

>>>.

>>.

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.*-*-*.com/ The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---


Sun, 16 Oct 2005 15:29:13 GMT  
 using .index and .seek function

Quote:

>When I view the table in design view there is no field
>named this, I do not know how to setup an index that is
>not an actual field in the Table.

Don't confuse a Field with an Index. They are quite different kinds of
entities!

If you open your table in Design view, you will see an icon on the
toolbar that looks like a lightning bolt hitting a datasheet -
clicking this will open the table's Indexes collection. Typically
there will be an index named PrimaryKey which indexes the field that
you have chosen as the primary key; other fields will have other
indexes. It's even possible (and often a very good idea!) to have one
index which indexes multiple fields.

The .Index and .Seek methods are the only commonly used Access
features which require that you know the name of the Index... but for
them, you *do* need that name!

                  John W. Vinson[MVP]    
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public



Sun, 16 Oct 2005 22:08:52 GMT  
 using .index and .seek function
Thank you vey much, I did not know you could create
Indexes seperate from the index propery of a single
field, and include several fields as an index under one
name.
Quote:
>-----Original Message-----
>On Tue, 29 Apr 2003 23:34:41 -0700, "Doug"

>>When I view the table in design view there is no field
>>named this, I do not know how to setup an index that is
>>not an actual field in the Table.

>Don't confuse a Field with an Index. They are quite
different kinds of
>entities!

>If you open your table in Design view, you will see an
icon on the
>toolbar that looks like a lightning bolt hitting a
datasheet -
>clicking this will open the table's Indexes collection.
Typically
>there will be an index named PrimaryKey which indexes
the field that
>you have chosen as the primary key; other fields will
have other
>indexes. It's even possible (and often a very good
idea!) to have one
>index which indexes multiple fields.

>The .Index and .Seek methods are the only commonly used
Access
>features which require that you know the name of the
Index... but for
>them, you *do* need that name!

>                  John W. Vinson[MVP]    
>    Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.



Mon, 17 Oct 2005 12:44:50 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Using SEEK wih multiple-field indexes

2. Using seek and index with text database

3. Help : Seeking record using Index

4. Using SEEK on DBF files and indexes

5. ADO USING SEEK AND INDEXES

6. Problems with the methods Index and Seek

7. 2 questions: Code doesn't work when I split database and Seek/Index question

8. How to set controlling index before doing SEEK in Access Basic

9. Multiple Index Seek (Acc97)

10. sequential file, line indexing, seeking and readline

11. ADO Index property and Seek method

12. Index Property/SEEK method Problem with ADO

 

 
Powered by phpBB® Forum Software