Form OK in Excel, but not in Access.

[kirkm]

What would be a better choice than Current event?
Here's what I have... hope it makes some sense being a subset..

  Dim sql As String, sArtist As String, sTitle As String, pArtist As String
    Dim rDisk As DAO.Recordset
    Dim frm As Form
    
    sTitle = Nz(Me.subCDTracks!cboTTitle)
    sArtist = Nz(Me.subCDTracks!cboTPerformer)
    If Len(sArtist) > 5 Then pArtist = Mid(sArtist, 5) 'Remove any leading "The "

    
        'Absolute Match Artist AND Title
    Set rDisk = GetAbsoluteMatch(pArtist, sArtist, sTitle)
    If rDisk.RecordCount <> 0 Then rDisk.MoveLast
    If rDisk.RecordCount = 1 Then
            ApplyIt rDisk
            Set rDisk = Nothing
            Exit Sub
        ElseIf rDisk.RecordCount > 1 Then
            Set frm = Form_frmExtSingles
            With frm
                .RS = rDisk
                .TheCaption = MyCompany() & " Artist & Title = " & Replace(sArtist, "The ", "(The) ") & " - " & sTitle
                .Visible = True
            End With
            Set frm = Nothing
            Set rDisk = Nothing
            Exit Sub
    End If



Function GetAbsoluteMatch(pArtist, sArtist, sTitle) As DAO.Recordset
    Dim sql As String
    sql = "SELECT " & MyCompany & ".Artist, " & MyCompany & ".Title, " & MyCompany & ".Label, " & MyCompany & ".Year "
    sql = sql & "FROM [" & MyCompany & "] "
    sql = sql & "WHERE " & MyCompany & ".Artist = p0 "
    sql = sql & "AND " & MyCompany & ".Title = p2 "
    sql = sql & "OR " & MyCompany & ".Artist = p1 "
    sql = sql & "AND " & MyCompany & ".Title = p2 ;"

    With CurrentDb.CreateQueryDef("", sql)
        .Parameters("p0") = sArtist
        .Parameters("p1") = pArtist
        .Parameters("p2") = sTitle
        Set GetAbsoluteMatch = .OpenRecordset
        .Close
    End With
End Function





Option Compare Database
Option Explicit

Private Sub Form_Current()
    DoCmd.MoveSize 7500, 5000, 16000, 8000
End Sub

Public Property Let RS(x As DAO.Recordset)
    Set Me.Recordset = x
End Property

Public Property Let TheCaption(z As String)
    Me.Caption = z
End Property

 

[theDBguy]

What would be a better choice than Current event?

It depends. If you want the code to fire each time the user navigates to a different record, then the Current event is perfect. Otherwise, what happened with the Load event?

 

[kirkm]

Neither Load or Activate events fire the way the Form is opened (unless I've missed something).
Maybe I could move it to a Sub and call it before .Visible? Will try... no need to run it every time a new record is clicked.

 

[theDBguy]

Neither Load or Activate events fire the way the Form is opened (unless I've missed something).
Maybe I could move it to a Sub and call it before .Visible? Will try... no need to run it every time a new record is clicked.

Okay. Let us know how it goes...