Insert data from a datasheet on a subform into a table

Posted on 2004-11-15
Last Modified: 2008-02-01
I've seen other questions similar to this asked on EE, but none seem to fit the bill.

I have a form with a unbound subform. The subform is in datasheet view.  I would like to be able to loop through all the data on the subform after the users enters it and insert that into a table.

Not sure how to loop through the fields on a subform in datasheet view.


Question by:silsuba
    1 Comment
    LVL 8

    Accepted Solution

    A single record?

    Use expression builder to get your syntax right for the subform controls (drop a new control, goto controlsource, click on the 3 dots, poke around through the forms and double click on the control name)

    Then in code you can build an insert statement. this will be long, i have examples of number, text and Date fields.

    dim s as string

    s = "Insert into table (col1, Col2, Col3) values (" & me.[SubformName].Form![ANumberField] & ", ' " & me.[SubformName].Form![ATextField] & " ' , # " & me.[SubformName].Form![ADateField] & "#)"

    docmd.runsql s

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now