Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: Automating Table Save

    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:
    Code:
        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:
    Code:
         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?

    DBS4M

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is that 189+1 calls to DLookup() ?
    not something that is top of my list of favorite things to do.

    you are also opening a read-only recordset and issuing .Edit and .Addnew.

    Code Bank post #81 is pretty close to what you are doing

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Quick Table Redesign

    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.

    DBS4M

  4. #4
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379
    Quote Originally Posted by izyrider
    is that 189+1 calls to DLookup() ?
    not something that is top of my list of favorite things to do.

    you are also opening a read-only recordset and issuing .Edit and .Addnew.

    Code Bank post #81 is pretty close to what you are doing

    izy
    Downloaded and looking at it. Oh corrected the code to dbOpenDynaset.

    DBS4M
    Last edited by dbsupport4me; 03-10-09 at 00:57.

  5. #5
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Main Issue

    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:
    Code:
              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.

    DBS4M

  6. #6
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Testing

    All,

    Think I have my code right:
    Code:
    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

    DBS4M
    Last edited by dbsupport4me; 03-10-09 at 11:26.

  7. #7
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Still Stumped

    All,

    Still stuck trying to get the eval/interpret on the call of a function name to work.

    DBS4M

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why are you using Eval and what do you try to do with it?

  9. #9
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379
    Quote Originally Posted by Sinndho
    Why are you using Eval and what do you try to do with it?
    Original code was:

    Tfld = Fnc_Nam(Ffld.Name, Ffld.Value, Fnc_Pms)

    but did not work so tried:

    Tfld = eval(Fnc_Nam(Ffld.Name, Ffld.Value, Fnc_Pms))

    which also did not work so then tried:

    Tfld = eval(Fnc_Nam & "(" & Ffld.Name & ", " & Ffld.Value & ", " & Fnc_Pms & ")")

    which also did not work.

    Nothing I'm doing here actaully calls the function with the passing or the parms.

    Any ideas?

    DBS4M
    Last edited by dbsupport4me; 03-21-09 at 03:01.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im just curious whose time you are trying to save, and where the cost benefit is here

    as I read it you are trying to develop a generalised form handler/designer. It seems to me that too much time is being spent on things that are interesting to the developer and possibly not enough time on delivering the solution to he customer who in effect is paying for this.

    I fear your generalised design may be a serious problme to maintain over time, especially if you don't effectively comment what you are doing. although I'd agree well written code often self comments often you ahve to make it clear what you are doing.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If what you want to do is saving the controls properties of a form into a table, and eventually create a new form with almost identical controls, have a look at the Enumformcontrols and BuildNewForm functions of the attached file.

    I cannot totally agree with healdem (sorry!). Sure it's a lot of work and sure it's not the kind of thing you'll try to do the day (or worse: the night!) before a project is due. However, if it's prepared far in advance it's worth the effort and can be easily reused from one project to another. It's also a great tool for rationalization and for making bulk changes into the structure of a form:
    Fill the table with the contols properties, change what you want (you can use SQL statements for making bulk changes into the table), then rebuild the form or change the properties of the controls in the existing one. You can also build templates for frequently used forms and use them for building new forms: that's how the forms wizards work in Access.

    Have a nice day!
    Attached Files Attached Files

  12. #12
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Wink Reason

    Sinndho/Healdem,

    First let me enlighten you on the reason. You are talking to the originator of modular design approach/concept/methodology, so I do nothing without this in mind. Therefore all code must pass this design criteria, and must be reusable if you ever do the process more than once.

    Obviously in Access you save forms all the time, so an automated process that does not require coding to make it happen is the only solution, anything else is simply not an option.

    Sometimes, yes the effort to develop a modular, reusable, generic piece of code consumes more time than a point solution. But when you design the modular piece right, the time you save will pay you over and over in your development time going forward.

    Also this is the differentiator between novice and expert programmers, as the expert write code that writes code, so they do not have to write it again. In the 2GL days when 20K lines of code a year was the standard for a good programmer, yours truly was producing 80K lines per six month, because my code wrote code and I had the function keys programmed to drop in the code when I clicked them, but that was in another program language, so not applicable here.

    I have written many modules that I now call in a global module, so never have to re-write, just call, now. This is another one of those building blocks, so correct implementation is really important.

    H,

    Your idea on maintenance is reversed, just the opposite happens, when you build a library of re-usable code, you work less and less from project to project and maintenance is not an issue, especially since using modular design allows you update/upgrade a single function/module with very specific localized impact by actual function executed. If you do not know or understand modular design, do some reading up on it. You will become a better programmer, once you do.

    S,

    Looking at the example you put up to see if the evaluation/interpretation function I need is included in the code somewhere.

    After reviewing it, though I like the concept of what it does and the automation is good, the actual problem I'm stuck on is getting a "string" line built, that will interpret to either call a function or routine, with all the arguments/parameters properly intact and passed to the function/routine. See the function name is stored my table so the query grabs that and then the function must be executed. Right now all attempts to get an interpretable/executable command line has not worked.

    Other languages I program in (and I program in 25 other languages) automatically interpret strings as command/routine calls, based on format, but not so in VBA, so trying to find the magic to get it right.

    Examples of my line build from the DB are:
    Code:
      codline = ![cfl_func] & "(" & ctl.Name & ", " & ctl.Value & ", " & ![cfl_parms] & ")"
      tblTIMEsht![tim_ahr] = eval(codeline)
    Where:
    ![cfl_func] = RowFind (function name)
    ctl.Name (Input Field Name from the Form)
    ctl.Value (form input field value)
    ![cfl_parms] = "Add_Tot, cfl_tah" (parms to finish processing ==> add the total and add to total field)

    So interpreted the line would be:

    Code:
      tblTIMEsht![tim_ahr] = RowFind("Row1Col3", "The value from the input field","Add_Tot, cfl_tah")
    DBS4M
    Last edited by dbsupport4me; 03-21-09 at 03:00.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You far too intelligent for me, I give up.

    Too bad you're not able to apply your wonderful theory to a language as trivial (or should I say vulgar) as VBA. Go back and play with the 24 other languages you program with. If they are so much superior to VBA why the heck to you bother to spend a little of your so precious time with it?

    Have a nice day!

  14. #14
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Explaination

    All,

    Think maybe I'm not explaining the problem right.

    The line in hard code would be:
    Code:
    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:
    Code:
    I_line = RoutineName & "(" & 1stParm & ", " & 2ndParm & "," & ...)
    then the line is to execute with:
    Code:
    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!

    S,

    I did not knock this language, just said it has it challenges, so keep it clean. Also "Eval" actually does do the interpret and execute in other languages, just not as familiar here with what is needed. Some other languages use the command "Interpret", but that's not the issue, getting the right syntax and all here is.

    Thanks Again!

    DBS4M

  15. #15
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    No Time

    All,

    I am still working on this, just no time to send in updates.

    DBS4M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •