Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access subform datasheet table query

Posted on 2009-12-19
Medium Priority
Last Modified: 2013-11-28
I have a subform which displays records in the datasheet view.  Two columns, Product and Amount, and roughly 20 rows.  It is populated using sql when I select Department from a listbox on the same form.  Data that is displayed in the subform is reviewed and if needed changed.  

Because the subform isn't bound to a table, how can I grab all of the records in the subform and write them to a table I have specified?  I'm trying to think of this in terms of "Select Product, Amount from subform" but cant get think of how to make this work.
Question by:donisanp
  • 6
  • 3
LVL 39

Expert Comment

ID: 26089061
You can use an update query.
CurrentDB.Execute TheQueryHere

Author Comment

ID: 26089069
Thanks, I get that much.  How do I reference the form as a table?
LVL 39

Expert Comment

ID: 26089077
How do I reference the form as a table?
I don't understand.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 26089104
The subform displays a datasheet view which is populated with data.  I'm trying to figure out how to reference the datasheet in a query as the datasheet is unbound.  It is unbound because the table from which the datasheet is populated is not the same table that I'm trying to write back to.

Author Comment

ID: 26089113
This is what I'm trying to get work:
Private Sub Command51_Click()

Dim db As Database
Dim temptbl As TableDef

'subfrmProdSel is the subform object containing the datasheet

temptbl = subfrmProdSel

Set db = CurrentDb()

db.Execute ("insert into tblTempProdAlloc (Product_code, Product_name, alloc_amount) " & _
    "Select product_code, product_name, alloc_amount from temptbl ")

End Sub

Open in new window

LVL 39

Expert Comment

ID: 26089118
How are you populating the datasheet subform?

Author Comment

ID: 26089124
Another table
Forms!frmProductAllocation!subfrmProdSel.Form.RecordSource = "SELECT PRODUCT_NAME, ALLOC_AMOUNT FROM tblTempProd"


Open in new window

LVL 77

Assisted Solution

peter57r earned 2000 total points
ID: 26089887
I don't see how someone asking for more detail can be interpreted as ' no feasible solution' .

Clearly the subform IS bound; it is bound to the tblTempProd table via the query you posted.
So any additions or edits are in that table.

So you use a standard Append query to add to the the second table (table2)- change the sql to use your own names.

INSERT INTO table2 ( product_name, alloc_amount)
SELECT product_name, alloc_amount
FROM tblTempProd;
Save this as say qryAppend

You need to be sure you have saved the last new/changed record in the subform before you run the query.
If the code to run the append query is in the subform then you should do..
me.dirty = false
currentdb.execute "qryappend", dbfailonerror

But if you running the append query from the main form, say from a button,  then you can just set the focus to field in the main form first

currentdb.execute "qryappend", dbfailonerror


Accepted Solution

donisanp earned 0 total points
ID: 26091339
No feasible solution for referencing the datasheet directly in my sql statement.  That's all that I was getting at.  What I didn't realize was that when I set the recordsource via vba code (as opposed to setting the object properties in the dialog box) for the subform that it established a read/write relationship.  At first I had assumed a read-only relationship.  While "experimenting" with the datasheet I realized that changes in the datasheet modified the recordsource table.  I used this relationship to get the job done.  While I couldn't query the datasheet object directly, I could use the underlying recordsource relationship to assign a holding table and query the holding table.  

I'd consider myself a novice VBA user, but this is my first project where I've had to use a subform and have had no experience with this object and its methods.  I just needed to understand how the object worked.  Figured that out on my own.  Thanks for following up.  

Author Comment

ID: 26113724
Yes, peter57r's explanation is helpful indeed.  To assist others reading this post I think it should be made clear that one cannot select data from a subform like you can with a list, combo or text box.  You must select the data from the bound source using either a query or an embedded sql statement.  

Peter - Thanks for your help.  Wish you would have come across my post 8 hours sooner.  Would have saved me a great deal of headache.  But that's how you learn I guess, by doing.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

571 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