Finding a record with composite key

[oldblindpew]

My question concerns how to reference and make use of composite keys in my
VBA code.

To choose and retrieve a record, I provide a command button on the
maintenance form. This button opens a dialog form which has an unbound list
box showing records in an appropriate order. When the desired item is
selected from the list box, the dialog form closes, and the selected record
is shown on the maintenance form.

When the record and its table employ a simple key, the critical command is
DoCmd.FindRecord. The key value of the selected record becomes the value of
the list box. The list box value is passed to a string variable, and
FindRecord operates on that string variable to find and display the record.
When the key is a surrogate autonumber key, things get more complicated,
involving recordsets and bookmarks.

But so far, the key has always been a single field. Now I have a table with
a composite key consisting of two fields. How can I get my code to work with
a multi-field key?

Thanks

 

[Allen Browne]

Use a string that looks exactly like the WHERE clause in a query.
Use FindFirst on the RecordsetClone of the form.

This kind of thing:
Dim strWhere As string
Dim rs As DAO.Recrodset

strWhere = "(City = ""New York"") AND (Amount > 1000)"
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If

 

[oldblindpew]

Thanks for replying, but I don't quite follow. I'm using a list box to look
up a specific record. I have no preconception of which record the user
wants; I'm just providing a list. I think the problem lies in how to
capture, from the list box, both fields of the composite key, so my event
procedure can use that information to find the matching record.

Even though Access knows that these two fields have a special relationship
as primary composite key, there doesn't seem to be a handy built-in way to
make use of that relationship.

 

[Allen Browne]

Perhaps it's beyond you at this stage.

The crucial thing is to build a string with both values it in, and use that
expression as your criteria.

 

[oldblindpew]

At this stage, I already know how to make this work for tables with single
key fields, by obtaining the value of the key from the listbox, and putting
it in a string variable. But for a table with a composite key I need to read
two fields from the listbox. How can I build a string with both values in it
if there is no way to read the two values from the listbox?

Do I need to add a dummy key field to my table, consisting of the two
composite key values jammed together into one field, as a workaround?

Should I eliminate and shun composite keys? Rebuild my table and everything
affected by it, just because it is "beyone me at this stage" to be able to
get Access to simply retrieve a record having a composite key?

Or, could I somehow make use of setting the listbox bound column to zero, so
that the value of the control is the index number of the selected row in the
listbox? From what I can tell, this approach would allow me to read fields
from the listbox, but it has more to do with, and is dependent upon, the
listbox being a multiselect listbox, which would not be at all appropriate
here.

 

[Allen Browne]

So you are trying to use a multi-column list box to provide *both* the
values of the key? Sorry: I assumed you were using 2 controls to supply to 2
values.

Only one of the columns in a list box (or combo) can be its Bound Column,
i.e. the column that gives it its Value. Although it is possible to have a
RowSource that returns multiple rows with the same value in the Bound
Column, my experience is that this is not reliable. So, you need to craft
the Bound Column so that the values are distinct, or else use 2 controls to
supply the 2 values.

For example, say Table1's key is a combination of F1 and F2.
You could set the RowSource like this:
SELECT [F1] & ";" & [F2] AS TheValue, F1, F2
FROM Table1
ORDER BY F1, F2;
List box properties:
Bound Column: 1
Column Count 3
Column Widths 0";1";1"
This displays 2 columns in the list box, but but bound column (and so the
Value) is unique.

Now your code can use Split() to parse the 2 values in the bound column into
an array, and so build the WHERE string from the 2 values.

The alternative is to use 2 controls to get the 2 values from the user.

Does that help?

 

[oldblindpew]

Exactly. I need the whole value of the composite key; having part of the key
is no good, and having part now and part later isn't much better. I need it
all at once.

Your suggestion sounds reasonable, and I was anticipating a solution of this
sort, somehow temporarily turning the composite key into a single value so
you could jam it into one column in the list box. I was just reluctant to
believe that such a makeshift, workaround approach would be necessary in such
a mature product. I thought surely Access would be prepared to manage a
compound key.

Before I left off Friday, I found opinion on the web from at least one
expert that composite keys in Access should be avoided like the plague.
Based on this and your reply, I'm inclined toward getting rid of the
composite key. Accomplishing this will be a chore, I'm sure, but may be best
in the long run.

Thanks for taking a closer look at my problem, and I'm sorry I couldn't do a
better job of explaining what I'm trying to do.

So you are trying to use a multi-column list box to provide *both* the
values of the key? Sorry: I assumed you were using 2 controls to supply to 2
values.

Only one of the columns in a list box (or combo) can be its Bound Column,
i.e. the column that gives it its Value. Although it is possible to have a
RowSource that returns multiple rows with the same value in the Bound
Column, my experience is that this is not reliable. So, you need to craft
the Bound Column so that the values are distinct, or else use 2 controls to
supply the 2 values.

For example, say Table1's key is a combination of F1 and F2.
You could set the RowSource like this:
SELECT [F1] & ";" & [F2] AS TheValue, F1, F2
FROM Table1
ORDER BY F1, F2;
List box properties:
Bound Column: 1
Column Count 3
Column Widths 0";1";1"
This displays 2 columns in the list box, but but bound column (and so the
Value) is unique.

Now your code can use Split() to parse the 2 values in the bound column into
an array, and so build the WHERE string from the 2 values.

The alternative is to use 2 controls to get the 2 values from the user.

Does that help?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

At this stage, I already know how to make this work for tables with single
key fields, by obtaining the value of the key from the listbox, and
putting
it in a string variable. But for a table with a composite key I need to
read
two fields from the listbox. How can I build a string with both values in
it
if there is no way to read the two values from the listbox?

Do I need to add a dummy key field to my table, consisting of the two
composite key values jammed together into one field, as a workaround?

Should I eliminate and shun composite keys? Rebuild my table and
everything
affected by it, just because it is "beyone me at this stage" to be able to
get Access to simply retrieve a record having a composite key?

Or, could I somehow make use of setting the listbox bound column to zero,
so
that the value of the control is the index number of the selected row in
the
listbox? From what I can tell, this approach would allow me to read
fields
from the listbox, but it has more to do with, and is dependent upon, the
listbox being a multiselect listbox, which would not be at all appropriate
here.