Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How to create a subform by querying table created at runtime

Posted on 2008-10-08
Medium Priority
Last Modified: 2013-11-29
What I'm trying to accomplish is should be really simple (I think...). Create a subform, in datasheet view, that is populated by certain fields from a temporary table created at runtime. And subsequently, save these fields to a permanent table.

*****This database I am creating is an enterprise multi-user application (currently) with 78 tables, 82 queries, 52 forms, 345 procedures, 50 relationships, 14 reports and countless objects... I am the sole developer, not part of a team or project where there is someone I can consult with, no developer tools and I am not an expert by any stretch of the imagination. I just work really hard...  *****

When the user uploads data from a spreadsheet, I create temporary tables from the tabs and ranges that contain the data needed. It is in this event procedure that I am trying to populate the subform.

My original design was not good, and the requirements have changed for the location of the subform (a different form). The original subform had it's sourceobject as a form, and the recordsource of that form was a temporary table created by a persistent query that was run after the temporary tables were created.... does not work.

I have researched this, and tried so many different methods I can not see straight....

My question is, how should a subform be developed so it can be populated with data from a temporary table and it's fields be accessible through a recordset or recordsetclone to save to a permanent table?

Thanks in advance....
Question by:Destiny947
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 4
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22676180

To be honest, without being intimately familiar with your database, it is going to be difficut for any Expert here to give you any meaningfull advice.

Also, this more of a "Project", than a simple question requiring an answer.

For example:
"My question is, how should a subform be developed so it can be populated with data from a temporary table and it's fields be accessible through a recordset or recordsetclone to save to a permanent table?"
The first question should be, why you even need a system like this?
There is probably a much simpler way.
But again, this would require us to be as familiar with every aspect of the database as you are.

Perhaps a better approach might be for you to state what you are trying to accomplish, and ask how it might be done.



Author Comment

ID: 22677175
Hi Jeff,

Thank you for responding...

I thought I did state what I am trying to accomplish.
" Create a subform, in datasheet view, that is populated by certain fields from a temporary table created at runtime. And subsequently, save these fields to a permanent table"

Please assist me with my ignorance, because you are right "There is probably a much simpler way" and I don't know it, which is why I'm having such a hard time...  

The user finds their excel file, clicks upload and I use a temporary table to pull a range of cells from an excel spreadsheet using the DoCmd.TransferSpreadsheet method.

Most of the data can be put onto the form by their position (e.g., 9,3) in the temporary table. But the building information: 3 fields "MailCode", "Building Desc" and "Action" are located in adjoining cells (e.g., F3,G3,H3), but their position varies depending on how many "actions" are associated with the building.

So the first record would contain the data from cells F3, G3 and H3, but the next record could be, for example, F5, G5 and H5 or F15,G15 and H15, etc. The data is all in the table created by the DoCmd.TransferSpreadsheet method but the only definate position that is constant is the beginning position.

These 3 fields of building information need to be displayed on the form. I've been using a subform that has a form as a recordsource. This form is populated, after the temporary tables have been created, by a query that selects the 3 fields based on the mailcode field not being null. Then makes a table. I have been able to display the data in the subform but have not been able store it in their permanent table because of my design.

This must be a simpler way, as you say, and hence my question on how the subform should be created. Based on a query, temporary table, etc.

Your advice is greatly appreciated.


Author Comment

ID: 22677200
Hi Jeff,
I forgot to mention that the number of buildings is unknown, which is why I used a query.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22678902

Again, I have no working knowledge of your database structure or data, so when you use terms like:
  " Most of the data can be put onto the form by their position (e.g., 9,3)"
  "only definate position that is constant is the beginning position."
... I have absolutely no idea what theses statements mean.

So if we look at this in a "Global" sense, ...one thing becomes clear.

If this will truly be a "enterprise multi-user application".
...Then you need to move the Excel part of this system to Access.

Thus avioding the entire Excel-to-Access subform process.

So, basically you would end up entering the subform records, directly into the subform.

Again, in a Global sense, this would involve you taking the Excel file and importing it to an Access table.
The table would be the source for the subform.

Now, if your situation is more complex than this, then that is where you should start to think about re-designing or simplifying it.



Author Comment

ID: 22680047
Hi Jeff,

Putting the Excel data into an Access table is what I do with this statement.

 'Import TAB 2 Worksheet into temporary table tblTemp2
         DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
              "tblTemp2", A, False, "Tab 2!F61:H200"

A temporary Access table "tblTemp2" is created. But as I mentioned, the number of buildings and actions vary, so I have been using a query to pull only those rows where the building desc is not null.

I've attached a stripped down version of one Excel file to show how the cells can be filled.

There can be any number of rows between buildings, and all the user needs to see is the mailcode, building desc and action for each building.

Do you suggest I pull all the fields into the subform and then manipulate how it is viewed?


Author Comment

ID: 22680942
The way this form is used, is the user uploads a specific Excel file that contains data that needs to be stored in the central database tables (BE). The file content will be different for every user (which explains why the number of buildings and actions vary), and also the content will change over the course of their work.

So wherever the user is located, they will be working with one, or many, files. There is no central location for the files. In real-time, the user will select the appropriate file from their location and upload it into the database.

There are 2 permanent tables involved in the form : tblPlan, tblBldgs

The tables have the following fields.
tblPlan: PlanAutoID(PK), PlanNo, PlanDescription, ApprovalDate,AuthDate

tblBldgs: SPBldgID (PK), PlanID (=tblPlan.PlanAutoID), BldgNo, MailCode, BldgDesc, Action.

The table "tblTemp2" contains the data that needs to be stored in "tblBlgs".
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22681136

Again, This is more of a "Design Project" than a simple question needing a direct answer.

Any solution would require exstensive coding in both the Access and Excel object models.

As volunteers, many experts here cannot afford to take on questions of this scope.
On the other hand, some experts will.

Please click the "Request Attention" button in your original post and ask for assistance.


Author Comment

ID: 22681221
I have coding in place but it doesn't work with the way I have designed the subform.

Author Comment

ID: 22681448

You mention any solution would require extensive coding ...

Could you suggest some solutions and let me work on the coding?

That's really all I'm asking...

Author Comment

ID: 22681851

Here is one link I found:


>Try this:

>Create functions to Filter and Unfilter the subform
>Create Macros that call these functions
>Create a Custom Pop-up (Short Cut) menu containing these two macros
>In the startup dialog box, select this custom popup as the Shortcut menubar.

>Is this what you wanted?

>Jeff Coachman

Maybe I can pull the entire temp table into a new form (which I just did), and then filter it.

Author Comment

ID: 22681955
Got the filter and presentation!

Accepted Solution

Destiny947 earned 0 total points
ID: 22683343
Got it!!!!!! Oh... my... goodness... it was so simple!

rst1.Open "SELECT tblAPATemp2.F1, tblAPATemp2.F2, tblAPATemp2.F3 FROM tblAPATemp2 " & _
 "WHERE (((tblAPATemp2.F1) Is Not Null))", _
 CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set rst2 = New ADODB.Recordset
rst2.Open "tblStrPlanBldgs", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
         Do Until rst1.Fields("F1").Value = EndRec
            rst2.Fields("PlanNo").Value = Plan
'                For Each fld In rst1.Fields
            rst2.Fields("MailCode").Value = rst1.Fields("F1").Value
            rst2.Fields("BldgDesc").Value = rst1.Fields("F2").Value
            rst2.Fields("AuthorizationAction").Value = rst1.Fields("F3").Value
            rst2.Fields("PlanID").Value = PlanID
    Set rst = Nothing
    Set rst1 = Nothing
    Set rst2 = Nothing
    Exit Sub

    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere
End Sub
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22683988

See, you didn't need me at all.


Author Comment

ID: 22686340
Hi Jeff,

Thank you... :)

Featured Post

Technology Partners: 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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

618 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