Solved

How to create a subform by querying table created at runtime

Posted on 2008-10-08
15
330 Views
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....
0
Comment
Question by:Destiny947
  • 11
  • 4
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22676180
Destiny947,

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.
;-)

JeffCoachman


0
 

Author Comment

by:Destiny947
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.










0
 

Author Comment

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

Author Comment

by:Destiny947
ID: 22677488
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22678902
Destiny947,

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)"
or...
  "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.

JeffCoachman


0
 

Author Comment

by:Destiny947
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?

Thanks...
stripped-Copy-Excel-file.xls
0
 

Author Comment

by:Destiny947
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".
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22681136
Destiny947,

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.
;-)

JeffCoachman
0
 

Author Comment

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

Author Comment

by:Destiny947
ID: 22681448
Jeff,

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...
0
 

Author Comment

by:Destiny947
ID: 22681851
Jeff,

Here is one link I found:

>abinboston:

>Try this:
>https://filedb.experts-exchange.com/incoming/ee-stuff/5009-AccessEERightClickRequery.zip

>Basics:
>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.
0
 

Author Comment

by:Destiny947
ID: 22681955
Got the filter and presentation!
0
 

Accepted Solution

by:
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
       
         rst1.Requery
         rst1.MoveFirst
         Do Until rst1.Fields("F1").Value = EndRec
            rst2.AddNew
            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
            rst2.Update
            rst1.MoveNext
        Loop
rst1.Close
rst2.Close
   
ExitHere:
    Set rst = Nothing
    Set rst1 = Nothing
    Set rst2 = Nothing
    Exit Sub

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

Expert Comment

by:Jeffrey Coachman
ID: 22683988
Congratulations!

See, you didn't need me at all.
;-)

JeffCoachman
0
 

Author Comment

by:Destiny947
ID: 22686340
Hi Jeff,

Thank you... :)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

705 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