Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: Quick Source Link

    All,

    Had a bookmark/favorite to a link on HOWTO capture the list of fields in a form to a table, but lost it.

    Does anyone have a URL to a good example of this?

    DBS4M

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

    Help

    All,

    When no one replies, one wonders if anyone is reading the posts?

    DBS4M

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nah, it's more likely that it's been read but nobody can answer the topic -- I certainly have no URL for you... but surely you don't want 38949873 replies that say "I can't help" ?!

    It would be helpful if this site revealed the number of "reads", just like almost every other forum.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Isn't that what the "Views" number is when you click on the link to read the post?!?

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Was this it?

    Here's some code you may find useful...

    * Loop through and populate unbound fields on a form from a table...

    Public Sub FillUnboundForm()
    On error resume next
    Dim db As Database
    Dim rst As Recordset
    Dim rfld As DAO.Field
    On Error Resume Next
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from mytable", dbOpenDynaset, dbSeeChanges)

    With rst
    If .EOF Then
    Else
    For Each rfld In rst.Fields
    Me(rfld.Name) = rfld
    Next
    End If
    End With

    End Sub

    * Loop through and save unbound fields on a form to a table...

    Public Sub saveUnboundForm()
    On error resume next
    Dim db As Database
    Dim rst As Recordset
    Dim rfld As DAO.Field, myfield
    On Error Resume Next
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from mytable",dbOpenDynaset, dbSeeChanges)

    With rst
    If .EOF Then
    Else
    For Each rfld In rst.Fields
    rst.Edit
    rfld = Me(rfld.Name)
    rst(Me(rfld.Name)) = Me(rfld.Name).Value
    rst.update
    Next
    End If
    End With

    End Sub

    * Looping through controls on a form and construct an SQL statement...

    private sub butgo_click()
    dim ctl as control
    dim strSQL as string
    for each ctl in me.controls
    if typeof ctl is checkbox then
    if ctl.value = true then
    strSQL = strSQL & ctl.name & ", "
    end if
    end if
    next
    strsql = "SELECT " & left$(strSQL, len(strSQL) - 2) & " FROM mytable"
    end sub

    I believe the credit goes to Ashfaque on creating this code. It's also now in the MSAccess code bank as well.

    http://www.dbforums.com/6402258-post87.html
    Last edited by pkstormy; 06-01-09 at 21:55.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks canupus, I have no idea why I never noticed that before O.O
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

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

    Similar

    All,

    Before all the responses I wrote the following:
    Code:
    Public Sub Tbl_Add(MyFname, MyWTable, MyTTable)
        ' This subroutine loads the WRITE table for automated
        ' processing with the form name and form field names when
        ' invoked within a form init sequence.
        '
        ' Format Call Tbl_Add Form, WriteTable, TargetTable
        '
        Dim dbs As DAO.Database, Wsp As DAO.Workspace, RsS As DAO.Recordset, Ctl As Control
        Dim CtlNam, FrmNam, MsgStr, SQLstr, WHRstr, RowCnt
        On Error Resume Next
        For Each Ctl In MyFname.Controls
            ' 100 = Label, 106 = CheckBox, 109 = TextBox, 111 = ComboBox
            Select Case Ctl.ControlType
                Case 106, 111, 109
                    CtlNam = Ctl.Name
                    FrmNam = MyFname.Name
                    WHRstr = "WHERE (([tfl_ffd]='" & MyWTable & "') AND ([tfl_frm]='" & FrmNam & "'));"
                    SQLstr = "SELECT * FROM tblF2Tmatch " & WHRstr
                    Set Wspace = DBEngine.Workspaces(0)
                    Set dbs = CurrentDb
                    DoCmd.Hourglass True
                    'On Error GoTo Err_Msg
                    Set RsS = dbs.OpenRecordset(SQLstr, dbOpenDynaset)
                    With RsS
                        If .RecordCount > 0 Then
                            .MoveFirst
                            .Edit
                        Else
                            .AddNew
                        End If
                        ![tfl_frm] = FrmNam
                        ![tfl_ffd] = CtlNam
                        .Update
                        .Close
                    End With
            End Select
        Next
        DoEvents
        Set RsS = Nothing
        Set dbs = Nothing
        Set Wsp = Nothing
        DoCmd.Hourglass False
        Exit Sub
    Err_Msg:
        MsgStr = "Ctl => " & Ctl.Name & " Func => " & Fnc_Nam
        MsgBox MsgStr & " Error => " & Err.Number & " => " & Err.Description
    End Sub
    Think this is very similar to what you supplied in feedback.

    For full automation this should be a seperate tool where .mdb file can be selected, forms viewed and selected, then the fields added, by process button, to my table "tblF2Tmatch", which is written into the .mdb file being interrogated.

    I'm using this with the save automation tool, I have documented at:

    http://www.dbforums.com/microsoft-ac...ml#post6404348

    Thanks!

    DBS4M
    Last edited by dbsupport4me; 06-10-09 at 09:17.

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

    Quick Q

    All,

    What is the control type for a list box?

    DBS4M

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

    Forgotten

    All,

    Did not get answer to last post.

    Did everyone forget me?

    DBS4M

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, you just ask questions that either can't be answered or not many have experience in.

    What is the control type for a list box? Well,... it's a list box.

    Perhaps you could restate the question differently?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The code examples you've received are different ways you can loop through the controls on a form and grab/store their name in a list (pursuant to your first question.)

    This function will do that (have you tried it?) - put it into the bottom of the form and then run it:

    Function GetFields()
    dim ctl as control
    dim strSQL as string
    for each ctl in me.controls
    if typeof ctl is checkbox then
    if ctl.value = true then
    strSQL = strSQL & ctl.name & ", "
    end if
    end if
    next
    strsql = "SELECT " & left$(strSQL, len(strSQL) - 2) & " FROM mytable"
    msgbox strsql
    end Function

    It contructs a SQL statement which you change if you need just the field names.

    Or try one of the other examples. Did you have something different in mind or has your question changed?

    .
    Last edited by pkstormy; 06-28-09 at 23:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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