Next unique record ( Record Navigation )

[ramindya]

I have a table named TableA which consists of following : ( ID is primary key )

ID SSN Lastname PHONENUMBER
1 123456789 ASHOK 5097863736
2 123456789 ASHOK 6572527257
3 897262562 KUMAR 9897867298
4 897262562 KUMAR 6572357373

The subform shows lists data by individual person one person at a time which means

ID SSN Lastname PHONENUMBER
1 123456789 ASHOK 5097863736
2 123456789 ASHOK 6572527257

To navigate from one record to next in the subform I use the record navigation which defualt comes with ms access.



Problem: The mainform also has navigation but it goes through all the records sequentially.
But I want to go next record ( a custom navigation or something like that ) that is..

FROM

1 123456789 ASHOK 5097863736

TO

3 897262562 KUMAR 9897867298

Can somebody let me know how to that ?

Thanks !!

[June7]

Are you saying you don't want the subform to even have the second record for each person in its recordset?

[ramindya]

I just want to add a navigation button in the main form so I can go.

FROM

ID SSN Lastname PHONENUMBER
1 123456789 ASHOK 5097863736

TO

ID SSN Lastname PHONENUMBER
3 897262562 KUMAR 9897867298

Thanks for the spontaneous reply!

[June7]

Might be able to do something with the RecordsetClone. Review this http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
Maybe something like:

Sub GoToNextSSN_Click()
Me.RecordsetClone.FindFirst "ID>" & Me.ID & " AND SSN <>'" & Me.SSN & "'"
If Me.RecordsetClone.NoMatch Then
   MsgBox "Record not found"
Else
   Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

[maximus]

Let me explain what I understand your problem is:

You want to navigate to unique records each time you click a next button. That is move to the next unique SSN Number as per the example provided. This is what I have done:

I have a table with the Following Fields:

ID (PK) (Autonumber)
SSN (Number)
LastName (Text)
PhoneNumber (Number)

Example

ID	SSn	LastName	PhoneNumber
1	123456789	Ashok	5097863736
2	123456789	Ashok	6572527257
3	897262562	Kumar	6572357373
4	897262562	Kumar	9897867298
5	897262569	Majumdar	9007897997

This is an extract from my table. When your form opens you will looking at Entry with ID:1 SSn:12345678 LastName:Ashok PhoneNumber:5097863 and you want to jump to Entry ID:3 SSn:89726562 LastName:Kumar PhoneNumber:6572357373

The Code:

Dim rst As DAO.Recordset
Dim strSQL As String
Dim myRecordSource As String
Dim intMyID As String

strSQL = "Select * From Table1 Where ID>" & Me.ID
Set rst = CurrentDb.OpenRecordset(strSQL)
Do While Not rst.EOF
If rst!SSn <> Me.SSn Then
    intMyID = rst!ID
    Exit Do
End If
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

myRecordSource = "Select * From Table1 Where ID=" & intMyID
Me.RecordSource = myRecordSource
Me.Requery
End Sub

What does the code do:

1) Opens a recordset selects all records with ID number more than the first record displayed in the form.
2) Loops through the records
3) Loop stops when encounters an entry with a SSN number <> equal to the SSN number displayed in the record.
4) Assigns the ID value to the Variable intMyID
5) Use the intMyID to filter the next record.
6) Make myRecordSource the RecordSource of the form.


Note: You will need to change the recordsource of the form to make it show all the records.

[scoobz1234]

i have a similar issue, where i think this may work, i have a database with over 1000 names in it, each name has a unique identifier we call a Division. there are 7 different divisions. in a form i have a combo box with the 7 divisions in it, is there a way to apply a filter once i select a division from the combo box, so when i click next record it only cycles through the records that have that division number?