Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to retrieve all records using partial primary key?

Discussion in 'Information Technology' started by strazz@my-Deja.com, Jul 28, 2006.

  1. strazz@my-Deja.com

    strazz@my-Deja.com
    Expand Collapse
    Guest

    I have a table which has a primary key made up of to fields from the
    table;
    The fields are opType and opLocation. Although each of the two fields
    can have duplicated, when used together as the primary key, the key
    must be unique.

    I would like to return a recordset of all records with the same opType.
    Could someone give me an example of the VB/VBA code to do this and
    also one to retrieve the single record which matches a specified
    primary key.

    I have been doing this sort of thing by using an SQL SELECT string, but
    am wondering if there is a simpler or better way. My background is
    (traditional 3GL) programming from which I understand the use of keys
    and partial keys but can't quite get my head around how this applies to
    the jet mdb structure.

    Cheers
    TonyS.
     
  2. Loading...

    Similar Threads Forum Date
    USA Obama: U.S. Will Help New Governments Retrieve Assets Breaking News May 19, 2011
    Sikh News New Orleans: Sikh holy book retrieved (Rediff.com) Breaking News Sep 11, 2005
    Sikh News Sikh holy book retrieved from under water (Hindustan Times) Breaking News Sep 10, 2005
    Sikh News UK Sikh Harnaam Kaur Enters Guinness Records As Youngest Female With Beard - The Indian Express Breaking News Sep 9, 2016
    USA Robbery suspect seeks his NSA records Breaking News Jun 13, 2013

  3. Alex Dybenko

    Alex Dybenko
    Expand Collapse
    Guest

    Hi,
    best - is to use SQL SELECT (queries). To find duplicated records - you can
    use "Find Duplicates query wizard".

    another approach - is to use .Seek method - you have to open recordset in
    dbOpenTable mode in order to use it. See access help for more info

    --
    Best regards,
    ___________
    Alex Dybenko (MVP)
    http://alexdyb.blogspot.com
    http://www.PointLtd.com


    <strazz@my-Deja.com> wrote in message
    news:1150869252.551645.133870@b68g2000cwa.googlegroups.com...
    >I have a table which has a primary key made up of to fields from the
    > table;
    > The fields are opType and opLocation. Although each of the two fields
    > can have duplicated, when used together as the primary key, the key
    > must be unique.
    >
    > I would like to return a recordset of all records with the same opType.
    > Could someone give me an example of the VB/VBA code to do this and
    > also one to retrieve the single record which matches a specified
    > primary key.
    >
    > I have been doing this sort of thing by using an SQL SELECT string, but
    > am wondering if there is a simpler or better way. My background is
    > (traditional 3GL) programming from which I understand the use of keys
    > and partial keys but can't quite get my head around how this applies to
    > the jet mdb structure.
    >
    > Cheers
    > TonyS.
    >
     
  4. Klatuu

    Klatuu
    Expand Collapse
    Guest

    Assuming you are working in a form, the technique for locating a specific
    record is something like this:

    Set rst = Me.RecordsetClone
    rst.FindFirst "[opType] = '" & Me.txtOpType & "' And [opLocation] = '" & _
    Me.txtOpLocation & "'"
    If rst.NoMatch Then
    MsgBox "No Matching Record Found"
    Else
    Me.Bookmark = rst.Bookmark
    End If

    As to limiting your recordset based on the values in one or more fields,
    there are a number of ways to do that. How you do it depends on the current
    situation and personal preference.
    To limit the records based on opType could be by using a parameter query as
    your recordset or you could do it with the form's Filter and Filter on
    properties. In either case, you will have to give the app the information to
    determine what value to use.

    A common approach is the use of a Combo Box. You can provide a rowsource to
    the combo that would provide a list of all available opType values. Once the
    value is selected, you can requery the form using the value in the Combo for
    the query's parameter.

    In the query builder, you would put the following code in the Criteria row
    of the opType column:
    Forms!MyFormName!MyCombName
    This will cause the query to include only opTypes that match the value in
    the combo. To actually do the filtering, you would use the After Update
    event of the combo box.
    Me.Requery


    "strazz@my-Deja.com" wrote:

    > I have a table which has a primary key made up of to fields from the
    > table;
    > The fields are opType and opLocation. Although each of the two fields
    > can have duplicated, when used together as the primary key, the key
    > must be unique.
    >
    > I would like to return a recordset of all records with the same opType.
    > Could someone give me an example of the VB/VBA code to do this and
    > also one to retrieve the single record which matches a specified
    > primary key.
    >
    > I have been doing this sort of thing by using an SQL SELECT string, but
    > am wondering if there is a simpler or better way. My background is
    > (traditional 3GL) programming from which I understand the use of keys
    > and partial keys but can't quite get my head around how this applies to
    > the jet mdb structure.
    >
    > Cheers
    > TonyS.
    >
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 20 Jun 2006 22:54:12 -0700, strazz@my-Deja.com wrote:

    >I have a table which has a primary key made up of to fields from the
    >table;
    >The fields are opType and opLocation. Although each of the two fields
    >can have duplicated, when used together as the primary key, the key
    >must be unique.
    >
    >I would like to return a recordset of all records with the same opType.
    > Could someone give me an example of the VB/VBA code to do this and
    >also one to retrieve the single record which matches a specified
    >primary key.
    >
    >I have been doing this sort of thing by using an SQL SELECT string, but
    >am wondering if there is a simpler or better way. My background is
    >(traditional 3GL) programming from which I understand the use of keys
    >and partial keys but can't quite get my head around how this applies to
    >the jet mdb structure.


    A SQL SELECT is your best bet: doing it in VBA is certain to be less
    efficient.

    It really makes no difference whether the field in question is part of
    the primary key or not. A Query can search on any field, using a query
    criterion. If that field is Indexed (in this case the first field in
    the primary key will be, using the PK index) the search will be faster
    and more efficient, but even a non-indexed field can be searched.

    Just

    SELECT * FROM yourtable WHERE [OpType] = [Enter op type:];

    will do exactly this.

    If you include multiple fields, just add them to the query using AND
    logic:

    SELECT * FROM yourtable WHERE [OpType] = [Enter op type:] AND
    [OpLocation] = [Enter op location:]

    will find only the one record requested.

    John W. Vinson[MVP]
     
  6. strazz@my-Deja.com

    strazz@my-Deja.com
    Expand Collapse
    Guest

    Thanks All for your help.

    I have got it working as I wanted using the WHERE keyword.

    Cheers
    TonyS.
     

Share This Page