Toggle between records on a form

Question

Hi,

Given the following form:

This is a member database, Naam is showing the name of the member and Echtegeno(o)e is showing the name of the husband/spouse.

The name of the spouse/husband is called with:

Private Sub Form_Current()

   Dim db As Database

   Dim rs As DAO.Recordset

   Dim strSQL As String

   Dim strResult  As String

   Dim strS As String

   Dim strAdres As String

   Dim lngStad As Long

   On Error Resume Next

   strS = Me.S

   strAdres = Me.Adres

   lngStad = Me.Stad

   If strS = "M" Then

      strSQL = "SELECT Gehuwden.[Naam Lid], Gehuwden.S, Gehuwden.StadIndex, Gehuwden.Adres FROM Gehuwden WHERE (((Gehuwden.S)=""V"") AND ((Gehuwden.StadIndex)=" & lngStad & ") AND ((Gehuwden.Adres)=""" & strAdres & """));"

   Else

      strSQL = "SELECT Gehuwden.[Naam Lid], Gehuwden.S, Gehuwden.StadIndex, Gehuwden.Adres FROM Gehuwden WHERE (((Gehuwden.S)=""M"") AND ((Gehuwden.StadIndex)=" & lngStad & ") AND ((Gehuwden.Adres)=""" & strAdres & """));"

   End If

   Set db = CurrentDb

   Set rs = db.OpenRecordset(strSQL)

   If rs.RecordCount > 0 Then

      Me.Echtgenoot = rs.Fields(0)

   Else

      Me.Echtgenoot = ""

   End If

   On Error GoTo 0

End Sub

What I would like to obtain is double clicking on Echtegeno(o)e and display that record.

I tried with no avail:

Private Sub Echtgenoot_DblClick(Cancel As Integer)

   Dim strEchtgenoot As String

   strEchtgenoot = Me.Echtgenoot.Text

   DoCmd.ApplyFilter , "[Naam Lid] = Forms!Echtgenoot"

   Me.Refresh

End Sub

Is that possible? TIA

Regards,

JP

Answer 1

In een vergelijkbare toepassing maakte ik ongeveer zo'n gebeurtenis:

Private Sub Echtgenoot_DblClick(Cancel As Integer)

On Error GoTo Fout

DoCmd.SearchForRecord , , , "[Naam Lid]='" & Me.Echtgenoot & "'"

Einde:

    Exit Sub

Fout:

    MsgBox Err.Description

    Resume Einde

End Sub

Overigens begrijp ik, net als andere regeerders, niet wat je voor hebt met de "Current" gebeurtenis. Als het goed is, worden alle gegevens van het formulier automatisch gevuld vanuit de Recordbron van het formulier.

Wat me ook opvalt is dat je in die (overbodige?) gebeurtenis de 'partner' op wil halen op basis van het geslacht en het adres. Ik zou verwachten dat je bij het lid alleen de uniek identificatie van de partner opslaat en dus alleen die nodig heb om de partner te vinden. Los daarvan ga je er kennelijk van uit dat partners altijd van verschillend geslacht zijn en op hetzelfde adres wonen. Beide aannames hoeven in het huidige tijdsgewricht niet per definitie juist te zijn.

Answer 2

Hi JP, I'm an independent adviser and will try to help.

What I suspect here is that your control that shows the spouse name is not set up correctly. In a properly normalized database the Spouse control should hold the primary key of the Spouse record. It can be setup to show the spouse name, but it should store the PK.

So your code should be:

DoCmd.ApplyFilter , "[Naam Lid] = " & Me.Echtgenoot

I would use DoCmd.RunCommand acCmdApplyFilterSort instead of ApplyFilter.

Answer 3

Try this version:

Private Sub Echtgenoot_DblClick(Cancel As Integer)

    With Me.RecordsetClone

        .FindFirst "[Naam Lid] = '" & Me.Echtgenoot.Text & "'"

        If .NoMatch Then

            Beep

        Else

            Me.Bookmark = .Bookmark

        End If

    End With

End Sub

Answer 4

Hi Hans,

Works as a charm. Thank you.

Answer 5

As it happens I've recently uploaded a file to my OneDrive Public Databases folder which includes something similar.  You'll find the file as Fees.zip at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the members form includes a combo box bound to the FamilyOf column.  Next to the combo box is a 'Go To' button which opens the same form at the member selected as 'family of', using the following code:

    With Me.RecordsetClone

        .FindFirst "MemberID = " & Nz(Me.FamilyOf, 0)

        If Not .NoMatch Then

            Me.Bookmark = .Bookmark

        End If

    End With

The FamilyOf column in the Members table is of long integer data type and references the MemberID column of the same table in an enforced relationship.  The combo box bound to the column is set up to hide the MemberID bound column, and show the concatenated FirstName & LastName values in the control.  This is done by setting the control's ColumnWidths property to zero.