Automating Table Save

[TBotNik]

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

[TBotNik]

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

[TBotNik]

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

[TBotNik]

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

[TBotNik]

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