Bookmark question

[WindSailor]

This should be a simple one, but right now I just can't get it.
How do I get a Bookmark to pick up on the entry that I chose from in a combo box when there are multiple entries for the same item I want to chose from (ID - primary key is unique, just not the name of the item - 'FullP', and that is what I am using)?

For instance I want to be able to choose the correct record where 'FullP' will have multiple entries, but unique ID's.

Originally I had this bookmark off of a set of cascading combo boxes on a form, but unfortunately when using this set-up the ID (primary key) is not transfered foward, the rest of it works fine and it takes me to the first record for each 'FullP' (just not the correct record when multiples are entered). The record source for the combo is similar to:

“SELECT  [LDetails].MDay, [LDetails].Session, [LDetails].DL, [LDetails].FullP “ & _
“FROM [LDetails] WHERE (((([LDetails].MDay)=Forms![DEntries]!EDay) “ & _
“And ([LDetails].Session)=Forms![DEntries]!Session) “ & _
“And ([LDetails].DL)=Forms![DEntries]!DL) “ & _
“ORDER BY [LDetails].FullP, [LDetails].ID;”

AND

Dim Rs As Object
Set Rs = Me.Recordset.Clone
Rs.FindFirst "[FullP] = '" & Me![FullP] & "'"
If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
Rs.Close

When changing the final combo box and binding it directly to the query, the primary key is transfered foward correctly, but I am still having problems getting it to go to the correct record.
I have added:

Dim Rs As Object
Set Rs = Me.Recordset.Clone
Me.RecordsetClone.FindFirst "[FullP] = '" & Me.FullP.Column(0) & "'"
If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
Rs.Close

This just takes me to the very first record...

I would really like to keep the cascade combo boxes working.
Any ideas?

 

[WindSailor]

Ok...

With the combo box tied to the Query... and going by the Access default set-up for creating a look-up combo box... <g>

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![CboFullP], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
rs.Close

Row Source for 'CboFullP':
SELECT [YourQueryName].[ID], [YourQueryName].[FullP] FROM [YourQueryName] ORDER BY FullP;

You will find your exact record for duplicate values of 'FullP'.

You can adjust your Recordsource SQL statement for a specific query and ORDER BY clause if you so desire.
This is with Access 2003.

 

[WindSailor]

Well I found my problem using cascading combo boxes...

I simply forgot to include the primary key or 'ID' in the SELECT statement for the final combo box. Once that was entered correctly and changed the 'bound column' to 1 to hold the value of the primary key instead of holding the column value for the name of 'FullP'; everything went great...