How do i export a complicated Access subform to Excel

[TinyTom]

I really need help with this code. I have made a pretty complicated subform that automatically updates filter criteria based off of inputs in various fields within the search form.

What I need to do is design a command button that will kick out the information that is stored in the subform to an excel document. Ideally I would like to use the DoCmd.OutputTo function so that the user could choose their output type, but right now I'll take whatever I can get.

The code for my form is as follows. I use setlink to set the child and master relationships between the form and subform. The control source for my subform is a table named Master Table subform4. And the Record Source for the main search form is a form named SearchFormTable .

Private Sub setlink()
Dim link As String
Dim found As Boolean
 
 
If Len(Form.Controls("Location").Value) <> 0 Then
If found Then link = link + ";"
link = link + "Location"
found = True
End If
If Len(Model.Value) <> 0 Then
If found Then link = link + ";"
link = link + "Model"
found = True
End If

............I repeat the above code over and over for each of the over 60 different search controls within the form......

If Form.Controls("PARTS BIN").Value Then
If found Then link = link + ";"
link = link + "PARTS BIN"
found = True
End If
 
If Form.Controls("JJ KELLER").Value Then
If found Then link = link + ";"
link = link + "JJ KELLER"
found = True
End If
 
searchsub.LinkMasterFields = ""
searchsub.LinkChildFields = ""
 
searchsub.LinkChildFields = link
searchsub.LinkMasterFields = link
End Sub

Here is the code that activate the filtering. Again, it is repeated for each of the items.

[code]Private Sub ADOBE_ELEMENTS_ AfterUpdate()
setlink
End Sub

Private Sub BARTENDER_After Update()
setlink
End Sub

Private Sub BUILD_A_BADGE_A fterUpdate()
setlink
End Sub............ ...[code]

Here is my current code for exporting. This is probably the worst written of my attempts, but I got desperate.

Private Sub Export_Click()
 
Dim stdocname As String
stdocname = "searchsub"
DoCmd.OutputTo acForm, stdocname
 
End Sub

I'm not sure the best route to tackle this. OutputTo will only work with a Form, Table, or Query. Maybe if there is a way to store my subform into a temporary table and OutputTO that. I'm just spit balling here. If anyone can help with this I would really use it. Again, like I said, I know its a challenge, but I like the way the form autocorrects after each change without using a command button to filter. Please help

[Stewart Ross]

Hi. I have added code tags to help delineate your code more clearly above.

I would suggest a fundamental rethink in this case. Mainform/subform structures usually reflect 1 to many relationships, but from the wide ranging nature of what is in your code there does not appear to be such a simple relationship here. Could you tell us what this is about? It looks like some kind of all-encompassing filter by form on everything that possibly might want to be filtered - and 60 possible search controls is complexity beyond any possible user I know of...

if you want to export data to Excel (or any other application) the norm is to build a query first. Not in code, just using SQL or the Access query editor. After that, use filter by form to restrict the data in that query for export. This can be done in code, responding to user selections and so on from the After Update events of the textboxes etc concerned.

I am very unclear indeed why you are linking the subform in code.

-Stewart

[TinyTom]

Believe me, my life would be alot easier if there the data I am searching in this form had relationships. Unfortunately, primary keys are next to impossible aside from a serial number. Computer names can apply to multiple systems as can all other criteria.

If I'm understanding your suggestion correctly, you advise me to abandon the auto filtering and work on a filter query that is conditional upon the items in the form. Unfortunately, the setlink function that I wrote is what is adding the extra functionality to my form. With the code any user can switch objects and mess around with conditions easily without having to select everything and then try and push the "filter" button. Please advise if there is any way to do it this way.

If not, I guess I can try and make a link to a second form that will use the values in my original form to make an export. Please instruct on how to do it your way. Keep in mind I have around 10 combo boxes, 6 text fields, and over 50 check boxes that limit the search criteria (it is a very large database).

[Stewart Ross]

Hi TinyTom. Sorry to say I am still none the wiser what it is you are doing; you mention serial numbers and computers - some kind of help desk system or tracking system for PCs and their components?

The next thing that springs to mind is what is the table structure? Where is the hierarchy of objects related to other objects one would expect when modelling PCs, for instance?

If there are no relations defined, and everything is in one big non-relational table, there is no chance at all of resolving such complexity into manageable chunks.

I would be happy to advise you how to move this one forward, but I simply don't have an understanding of what it is you are trying to achieve. My gut feel is that if the design does not adequately model the reality the design has to be changed until it does, which will save an awful lot of rescue effort in the longer run.

-Stewart

[TinyTom]

I'm taking this table over from the previous developer. The database is a PC inventory database. The old design was, for the most part, one gigantic master table that stored: location (by city), computer name, user name, serial number, job function, status (online, offline, retired), cpu, dual core yes/no column, model, make, software1, software2, software3,softw are4 and a made up primary key that has no relavence besides assigning an indepent identifier.

This table had no relationships to other tables and no primary key assigned. I assigned the serial as the primary key seeing as it was a unique value. From there, I did my best to transfer the software into a new Software table that I related back to the master table with the serial number. In this table I have made around 50 possible checkboxes for software.

Converting the preexisting data into that software table is next to impossible seeing as there is no logic to the previous table. Software names can be located in any of the 4 columns, plus only allowing 4 columns limits the amount of info that can be put into the database.

So I am trying to do my best to work with what I have. Please let me know what you recommend I do to ammend this problem.

The current form with the subform is a search form which allows the user to navigate through the master/software tables to narrow down a search. If the computer they aren't looking for isn't found within the search, there is an "add record" command button that takes them to a data input form that pulls all the info that they already entered into the new form. These temporary values are stored in a table called SearchFormTable . SearchFormTable is the record source for the Search form. Master Table subform4 is the source object for the subform within Search.

The search controls are in 4 tabs within the form header section of Search. The subform is in the detail section.

If there is any detail that you need that I did not describe please let me know.

[Stewart Ross]

Hi TinyTom. This is a very helpful expansion of your previous post - many thanks.

OK, so you took over a single-table mess that was I would bet originally developed in Excel (which lends itself to such single-table non-database solutions) then imported into Access. However, as the quote below tells me, if you have 50 checkboxes for software you have yourself not created a relational table for software - you appear to have specified the possible software products column by column as yes/no columns in your table (hence you are referring in post 1 to things like Adobe Elements and so on). This means your table is not yet in first normal form (no repeated groups), never mind normalised to third normal (3NF) or Boyce-Codd Normal Form (BCNF).

From there, I did my best to transfer the software into a new Software table that I related back to the master table with the serial number. In this table I have made around 50 possible checkboxes for software.

3NF is essential if you are to use the database's relationships to assist you. The following howto article introduces database normalisation and table structures.

As an aside, relational designs are typically quite narrow in terms of number of columns (table attributes), but deeper in terms of number of rows (individual records). Substituting records as table attributes will hamper you to the point of making your project impossible, and hide the other relationships which exist between the tables.

In my opinion, the likely attributes of your software table would include Product ID (Primary Key (PK), potentially alpha), Supplier Code (Foreign Key (FK), alpha), Product name (alpha), licence renewal date (date/time), Date of Purchase (date time), number of licences (integer) and so on.

This relates 1-m to an Installation table which would list the PCs on which that software was installed: Product ID (FK, from Software), PC Serial No (FK, alpha) (Both FKs forming compound PK of the Installation table), Date Installed (date/time), Date Removed (date/time).

There is also an m-1 relationship from Software to a simple supplier table: Supplier Code (PK, alpha), Supplier Name (alpha)

Spending just a few minutes on this problem identifies three tables apart from your PC table: Supplier, Software, Installation, and PC.

It is usually possible to convert data from an apparently unstructured single table into relational data. I do this using Excel as an intermediate means of working with tabular data and for its ability to replicate values down columns quickly.

If you export the data from Access to Excel you can use Autofilter to find groups of specific values (such as Adobe Elements). You first add a new column to the table called Software ID that eventually will form part of the Installation key of your new installation table. This is done simply by assigning to all rows which contain Photoshop Elements the new Elements software code from the (properly defined) software table. You just need to filter for the word Elements, type in the new product code once, and copy it down all other occurrences.

You go on adding new columns as necessary to reflect parts of the new design. This leads to a big non-relational table in which you have identified new columns which, when extracted, will form the many-side elements of the new 1-m tables you have identified. Then you separate these into individual worksheets which will form the Installation, Supplier and other tables, copy these to Access, and begin the work of defining the relationships between the tables properly.

I am not having you on when I say that this will pay dividends - I can see no prospect of doing anything useful with Access unless you resolve the fundamental design problems that are apparent.

As colleagues on this forum would no doubt confirm, you really, really cannot work with non-relational data as you are doing - you will not find you can get meaningful results from a relational database unless you have relational data to work on.

-Stewart

[TinyTom]

I really appreciate all the help and advice. I'm doing my best to normalize this database and think that I'm on the right track. I do however need help with my add record button.

I now need the add record button to add the information on the one input for into all of the corresponding tables that now exist. I believe there are 5 of them. What is the coding or method to do this?

[Stewart Ross]

Hi TinyTom. I'm glad your efforts to normalise the DB are progressing.

As I've mentioned previously, in setting up tables from non-relational data I work manually, by copying and pasting to and from Excel. I generally don't do one-off tasks in code - because it is quicker dealing with it manually than coding it.

If you are looking for ideas related to your first post in this thread, taking the data in your subform and populating other tables with that data, there is a way to do so but it is messy and requires processing of two recordsets simultaneously. With your data being what it is we can only give a skeleton idea below:

Dim subformRS as DAO.recordset
Dim othertableRS as DAO.recordset
Set subformRS = Me.yoursubform_controlname.form.recordsetclone
set othertableRS = CurrentDB.OpenRecordset ("name of a table you want to add records to")
While not subformRS.EOF
	othertableRS.Addnew
	othertableRS![fieldname1] = subformRS![corresponding field1]
	othertableRS![fieldname2] = subformRS![corresponding field2]
	...
	othertableRS![fieldnameN] = subformRS![corresponding fieldN]
	othertableRS.Update
	subformRS.movenext
End While
subformRS.Close
othertableRS.Close

This code skeleton uses DAO recordsets. To use DAO please make sure there is a reference to the Microsoft DAO 3.6 Object Library (or later) - from the VB editor select Tools, References and ensure the entry for this is checked.

The code uses RecordsetClone to copy the subform's current recordset at the point you run it - so it will take account of the results of all filtering and so on. It then loops through all records in the cloned dataset and adds records to another table, setting field values for the new records to corresponding values in your subform's dataset. You need to specify the field names of all fields involved - hence why I can only provide you with a skeleton of the code.

This code is not limited to copying one recordset at a time - you can open several others to copy records simultaneously if you wish, as long as these are closed correctly after loop completion. However, for test purposes you may well wish to work with just one copy table to begin with.

Recordsetclone is a very handy way to get access to the underlying recordset data for a form. Unfortunately, it cannot be used directly with methods such as DoCmd.OutputTo to export the entire recordset in one go, as these methods operate on tables and queries (which in Access object terms are tabledef and querydef objects, related to but not the same as the recordset object created by RecordsetClone) .

-Stewart