Insert data from a datasheet on a subform into a table

Posted on 2004-11-15
Medium Priority
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

Accepted Solution

SimonLarsen earned 1000 total points
ID: 12589235
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 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