All,

Have a form with over 189 controls on it and trying to automate the save.

I currently use the following script to init the fields:

On Error GoTo Err_Msg
    For Each ctl In Targetform.Controls
        ' 100 = Label, 106 = CheckBox, 109 = TextBox, 111 = ComboBox
        Select Case ctl.ControlType
            Case 106
                If ctl.Visible = True Then ctl = Null
            Case 111
                If ctl.Visible = True Then ctl = Null
            Case 109
                If ctl.Visible = True Then ctl = ""
        End Select
    Next
    Exit Sub
Err_Msg:
    MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.Description

So wanting to write something based on temp table containing 2 column Frm_Fld_Name, and Tbl_Fld_Name for assignment of form field to table field; maybe looking like this:

Dim dbs As DAO.Database, WsP As DAO.Workspace, RsS As DAO.Recordset
    SrchNo = Targetform![cboxPSH]
    RECnum = DLookup("cad_rno", "tblCADdetail", "[cad_pjx]=" & SrchNo)
    WhrStr = "WHERE (([cad_pjx]=" & SrchNo & ") AND ([cad_rno]='" & RECnum & "'))"
    CSTstr = "SELECT * FROM tblCADdetail " & WhrStr & " ORDER BY cad_cds;"
    Set Wspace = DBEngine.Workspaces(0)
    Set dbs = CurrentDb
    Set RsS = dbs.OpenRecordset(CSTstr, dbReadOnly)
    With RsS
        If .RecordCount > 0 Then
            .MoveFirst
            .Edit
        Else
            .AddNew
        End If
   On Error GoTo Err_Msg
    For Each ctl In Targetform.Controls
        ' 100 = Label, 106 = CheckBox, 109 = TextBox, 111 = ComboBox
        Select Case ctl.ControlType
            Case 106, 109, 111
                TF_Name = DLookup("Tbl_Fld_Name","TF_Var_Def","[Frm_Fld_Name]=" & ctl.Name)
                Eval (![TF_Name] = ctl)
        End Select
    Next
        .Update
        .Close
    End With
    Exit Sub
Err_Msg:
    MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.Description

So I do not have to run documenter and cut/paste all vars into the save subroutine.

Is my idea solid and what are the pit falls?

OMR

All,

Got to thinking about this for total code re-usability and decided to create the table as:

tblF2Tmatch:
tfl_id autoincrement
tfl_frm text (Form Name)
tfl_ffd text (Form Field Name)
tfl_tbl text (Table Name)
tfl_tfd text (Table Field Name)
tfl_spc y/n (Special Processing)
tfl_fnc text (SP Function Name)
tfl_prm text (SPF Parms)

where I can map any field on any form to any field in any table and then create var assignments for fields needing special processing and/or something other than direct var to var connection to table fields.

Thinking on function call something like var = func_nam(form_fld,parmlist)

so var get assigned directly in my process to the table field, like I was orgininally thinking

Oh, those of you who have not done complete multi-user mode and are stuck in the "relationship" mode, sorry VBA like this solves those problems.

OMR

All,

My main issue is with the evaluation or interpretation of the names from the table I created. If I am remembering correctly, VBA has four different way to interpret and execute a command string and it varies depending on which var type you have.

What I usually struggle with is getting the second of the following 2 lines right:

TF_Name = DLookup("tfl_nam", "tblTFmatch", "[tfl_fnm]=" & ctl.Name)
          Eval (![TF_Name] = ctl)

On the note about DLookup, I could open this table in a recordset, write it to an array and then process out of an array, if speed is an issue.

OMR

All,

Think I have my code right:

Sub Sav_Rec()
    Dim dbs As DAO.Database, WsP As DAO.Workspace, RsS As DAO.Recordset, Ffld As Field
    Dim Fnc_Nam, Fnc_Pms, RECnum, SrchNo, SQLstr, TF_Name, WhrStr, Tfld As Field
    SrchNo = Targetform![cboxPSH]
    Set Wspace = DBEngine.Workspaces(0)
    Set dbs = CurrentDb
    DoCmd.Hourglass True
    On Error GoTo Err_Msg
    For Each Ffld In Targetform.Fields
        TF_Name = DLookup("tfl_nam", "tblTFmatch", "[tfl_fnm]=" & Ffld.Name)
        If (Ffld.Name <> Ffld.Value) Or Not (IsNull(Ffld.Name) = IsNull(Ffld.Value)) Then
            RECnum = DLookup("cad_rno", "tblCADdetail", "[cad_pjx]=" & SrchNo)
            WhrStr = "WHERE (([cad_pjx]=" & SrchNo & ") AND ([cad_rno]='" & RECnum & "'))"
            SQLstr = "SELECT * FROM tblCADdetail " & WhrStr & " ORDER BY cad_cds;"
            Set RsS = dbs.OpenRecordset(SQLstr, dbOpenDynaset)
            With RsS
                If .RecordCount > 0 Then
                    .MoveFirst
                    .Edit
                Else
                    .AddNew
                End If
                On Error Resume Next
                TF_Name = DLookup("tfl_tfd", "tblTFmatch", "[tfl_ffd]=" & Ffld.Name)
                Fnc_Nam = DLookup("tfl_fnc", "tblTFmatch", "[tfl_ffd]=" & Ffld.Name)
                Fnc_Pms = DLookup("tfl_prm", "tblTFmatch", "[tfl_ffd]=" & Ffld.Name)
                On Error GoTo Err_Msg
                For Each Tfld In .Fields
                    If TF_Name = Tfld.Name Then
                        If IsNull(Fnc_Nam) Or Fnc_Nam = "" Then
                            Tfld = Ffld.Value
                        Else
                            Tfld = Fnc_Nam(Ffld.Name, Ffld.Value, Fnc_Pms)
                        End If
                    End If
                Next
                .Update
                .Close
            End With
        End If
    Next
    DoEvents
    DoCmd.Hourglass False
    Exit Sub
Err_Msg:
    MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.Description
    Resume Next
End Sub

Now testing

OMR

All,

Think maybe I'm not explaining the problem right.

The line in hard code would be:

Call RoutineName(1stParm, 2ndParm, ...)
or
Someval = FunctionName(1stParm, 2ndParm, ...)

When I'm using the term "interpret" that means I'm constructing a string, assuming the parms are also vars, as coming from query, with:

I_line = RoutineName & "(" & 1stParm & ", " & 2ndParm & "," & ...)

then the line is to execute with:

Call Eval(I_line)
or
Someval = Eval(I_line)

So the substitution for the vars happens and the actual funciton/routine is executed, but never a HARD CODE CALL to the function/routine.

I can do this easily in most languages I program in but really struggling here as VB/VBA does not like to both interpret and then execute the interpreted string.

Some help would be appreciated.

Thanks!

TBNK