Access 2003: Auto add a column, value depends on string selected by user

[compooper]

Hi there,



I would like to know how to automatically add a column once the user has uploaded a file.

I am using Ken Getz code to upload files, but before the file is loaded to the system, I would like the user to describe the file, using option groups & combo lists. (not sure how to do this as well)

The reason for that is, the user can identify which company the file belongs to and with that information I want the system to automatically add a column to that table stating the company name.

That way, when I come to merge the various company tables, I will know which company any specific record belongs to.

Any help will be greatly appreciated.

[boblarson]

First off, let me say I'm sorry for having to say this, but your table structure is not good, if you have to add fields like this. You should be adding ROWS to tables for data like that, not fields in the tables. So, the first thing I would suggest is to fix your table structure as you are going to find it increasingly difficult to get good information out without doing major contortions as you go along.

[compooper]

Haha. So I've heard.

Okay lets assume I fixed my table structure:

So how do I get my database to:
- take a file imported by a user and extract the necessary info I need from it to add to my new tables
-does this require mostly VBA or macros if the user will be importing on a routine basis?

[Rhino373]

I do a similiar thing. Rather than asking a user for input I assign an AutoNumber to a table I create that has each file I import. I run an update query to update the associated column in the information I imported. The table of filenames makes it so a user cannot import duplicates. It runs a dlookup against the table of filenames to decide whether it should import it or not. I run the update query so I can easily find those records that came from a specific file so if there were any problems with the import I can find those entries and get rid of them or find where duplicates came in (it allows duplicates if they were part of two different files with two different names.)

So you just create a one column table with company names. Create a form with a combo box and tie it to that table. Once a person chooses to update, you run a dlookup against the company name table to see if what is in the combo box does exist in the company name table. You do this because the combo box will let you type in junk and not select and actual value and if it does match, run the function to upload the file and then run an update query to add the company name to the field in the database.

You don't create the column on the fly, Create the table structure with the CompanyName field on the end. When it imports and the original file doesn't have that field it will just skip it. (assuming its at the end). When you write the update query you'll want to add the criteria 'Is Null' so it only updates records whose CompanyName is blank thus the files you just imported.

It just requires one function to upload the file and an update query. Technically the SQL for the update query could be written directly into the function so it would just appear like one function.

[compooper]

So from my understanding you suggest:
- create a table with company names and tie Select Company combo box to it
- user imports file, filename and an autonumber are stored in a table
- when user selects to import file, the update query runs and fills all Null values under the column Company with the corresponding company name selected
- it also runs dlookup to ensure no duplicate files are being imported

[Rhino373]

You don't need the filename and autonumber unless you care about duplicates. You need to consider whether your data has the possibility to contain duplicates and whether it should. I've just done this personally because with my database, the upload function doesn't have a user pick a file, it reads all files that sit in a specific location. So I'm able to upload hundreds of files at a time. The files I upload each month don't move from the specific location. Each month I read in the same files plus ones I haven't read it. I just needed to develop a way to not import the same file hundreds of times. The other option of course was to dump the table and upload all files again but that just takes too much time.

[compooper]

I was considering importing automatically from a folder, but I need to know the invoice date.

Is there a way to extract a particular cell in an excel file, & repeat it as a value in a field? For example, the first few rows of an invoice might be a company's header, which includes lots of information I don't need but it also includes the invoice period. I would like to extract the invoice period, trash all the other information and create a field called Invoice Period and display it there.

[Rhino373]

Possibly, the invoice period would always have to exist in the same place or be parsable (like its the 5 item in a row). This begins to get a bit difficult. Here is how I would handle this. I use Access to open what I'll refer to as a MacroFile (just a helper workbook with macros in it) and the source file/files. Then I have access run the macro from the MacroFile against the source file.

 
This is an example of that:
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
Set objMacroWorkbook = objExcel.workbooks.Open(CurrentProject.Path & "\Format Alignment Workbook.xlsm")
Set objWorkbook = objExcel.workbooks.Open(ExportMasterListFile)
objExcel.Visible = True
objExcel.Application.Run "'Format Alignment Workbook.xlsm'!wirereports"
objWorkbook.Close False
objMacroWorkbook.Close
objExcel.quit

The macro would select the first row or specific cell and write it to a new workbook called temp.xls. It would then delete those rows. You need to delete those so the upload will upload correctly. Personally I use a TransferSpreadSheet command. So you would read the first line, create a new file, delete the line, save the file,close it, use a TransferSpreadSheet to import the file, import the temp file (if you have to parse out text). If you copied just one cell we can get fancier. When you paste that cell to a new workbook we can name the workbook the name of that cell. Then we can just read the name of that file and import just the name and us and update query. Then we loop and do it all over again if you're reading multiple file or if you just gave them one file option you are done.

This is an example of editing a workbook and saving it as a temp file. You don't see the TransferSpreadSheet command because it is in the Access function. This is one of my Excel Macros:

 
Public Sub Combine()
Call WMVCEdit
Call OpticalEdit
 
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
Sheets("WM VC").Activate ' make the sheet active
Selection.CurrentRegion.Select ' select all cells in this sheets
 
Selection.Copy
Sheets("Combined").Range("A65536").End(xlUp).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sam's Optical").Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy
Sheets("Combined").Range("A65536").End(xlUp).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Worksheets("Combined").Copy
Set wb = ActiveWorkbook
wb.SaveAs Workbooks(2).Path & "\Temp.csv", FileFormat:=6
wb.Close
 
 
End Sub

This is kind of how I import. You see I run a TransferText command in this example and then I run an UPDATE query to update fields with a date based on the name of the file I imported. I only update NULL values. I then delete the temporary file I created and add the filename I imported to a table

'Import the .csv file to the Applied Report table
DoCmd.TransferText acImportDelim, "spec with concatenate", "Applied Report", strPath & "temp.csv", True

        'All the filename date to the GroupingDate field
        strSQL = "UPDATE [Applied Report] SET GroupingDate = '" _
        & strProperName _
        & "' WHERE GroupingDate IS NULL;"
        CurrentDb.Execute strSQL
 
        'Delete the .csv file that was created
        Kill strPath & "temp.csv"
 
        strDate = FileDateTime(strPath & strFile)
        rs.AddNew
        'to save the full path using strPath & strFile
        'save only the filename
        rs!FileName = strFile
        rs!FileDate = strDate
        rs!UploadedDate = Now()
        rs.Update

Note these examples don't exactly go together they are just parts of a very big database I wrote.