changing a forms record source and control sources in code

[markjkubicki]

I have a simple single record form that can be used to display data from one of several different tables, where each table has different field names for the same info


'its inherited, and one day, I'll normalize(?) this, but UGH, not this go around...
---> the question is simple: IS THERE A MORE ELEGANT WAY TO DO THIS?

Public Sub ANotes_SourceUpdate(vNoteRef As Long, vRecSource As String)
Dim strFormName As String
strFormName = "frmANotes_Edit"
Dim frm As Access.Form


    Select Case vRecSource
    Case Is = "GNote"
        strTbl = "tbeGenrlNotes"
        strNoteRef = "OptNumber"
        strTitle = "AFill_Title"
        strText = "AFill_Text"
    Case Is = "CNote"
        strTbl = "tbeCoordNotes_EOS"
        strNoteRef = "CNote_ID"
        strTitle = "CNote_Title"
        strText = "CNote_Text"
    Case Is = "Installation"
        strTbl = "tbeInstallNotes_EOS"
        strNoteRef = "InstallNote_ID"
        strTitle = "InstallNoteTitle"
        strText = "InstallNoteText"
    End Select

' if i understand correctly, the form has to be open to alter the record source.... seems suspect.... IS THIS CORRECT?
    DoCmd.OpenForm strFormName, acNormal

  
' Set form properties.
    Set frm = Forms!frmANotes_Edit
    With frm
        vSelect = strTbl & " where " & strNoteRef & " = " & vNoteRef
        .RecordSource = "SELECT * FROM " & vSelect
        .Form.txtNoteTitle.ControlSource = CNoteTitle
        .Form.txtNoteText.ControlSource = CNoteText
    End With

    frm.Refresh 
' this didn't work, nor did: requery, repaint, ...

' the only thing I could get to work is to close the form, and then re-open it, which seems CLUNKY
' ***  this is really the question: IS THERE A MORE ELEGANT WAY TO DO THIS? ***

    DoCmd.Close acForm, strFormName
    DoCmd.OpenForm strFormName, acNormal


End Sub

[June7]

Probably doesn't work because code is not referencing variables to set ControlSource property.

' Set form properties.
    Set frm = Forms!frmANotes_Edit
    With frm
        vSelect = strTbl & " where " & strNoteRef & " = " & vNoteRef
        .RecordSource = "SELECT * FROM " & vSelect
        .Form.txtNoteTitle.ControlSource = strTitle
        .Form.txtNoteText.ControlSource = strText
    End With

I have done something like this. However, I put code to set form properties in that form Open event.