How to retrieve all records using partial primary key?

[strazz]

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.

 

[Alex Dybenko]

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

 

[Klatuu]

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

 

[John Vinson]

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]

 

[strazz]

Thanks All for your help.

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

Cheers
TonyS.