search, transfer file & open form in one macro???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help. I am sure this can be done. Unlimited csv files hav 2b imported
into table. Each file name is a jobbag number and is never duplicated.
Trialled different ways. Hav set macro to transfer individual files but want
to transfer multiples. Already have a paramater query to type in jobbag no
which opens form to add data to. Have also put this is in a macro on
switchboard. This all works fine for files already transferred. Can this
macro be automated to transfer csv file at same time as searching for a
specific jobbag no.
 
If you just want to import data from all the files in the folder, search
http://groups.google.com for
fallon "import all files in a folder"
and you'll find what you need to get started.

Otherwise, it can be done, but you may need to explain more clearly what
it is. Do you want to import all the files in the folder, or just the
file whose name corresponds to a specific jobbag number, or something
else?

Does the jobbag number have to be imported into a field in the table?
Does the import process need to keep a record of the files that have
been imported?
 
Thanks for the reply John
Does this help you?
Need to import file that corresponds to specific jobbag number.
Yes is imported into a filed in table.
No do not have to have record of file imported.
To clear up. This database is for operators to input jobbag number, MS
Access to search for jobbag number and transfer file into table and open form
to that specific jobbag no for further data to be input.
 
On the basis of the explanation so far, you could do it along these
lines:

1) A form (I'll call it frmImportJobBag) with a textbox where the user
can type the jobbag number (let's call it txtJobBagNum) and a
commandbutton (let's call it cmdImport)

2) A temporary table (tblTemp) containing all the same fields as the
jobbag textfiles.

3) A main table (I'll call it tblMain) containing the same fields as the
temporary table plus a field for JobBagNum.

4) An append query (qryAppendJobBag) that appends records from the temp
table to the main table, including a calculated field like this:
JobBagNum: Forms!frmImportJobBag!txtJobBagNum

5) A delete query (qryDeleteTblTemp) that deletes all records from the
Temp table)

The idea is that the user types the jobbag number and clicks the button.
In the button's Click event procecure, you have something like this
untested air code:

Const FOLDER_PATH = "C:\Folder\Job Bags\" 'location of job bag files
Const FILE_EXT = ".csv" 'file extension for job bag files
Dim strFileSpec As String

CurrentDb.Execute qryDeleteTblTemp 'empty temp table

strFileSpec = FOLDER_PATH & Me.txtJobBagNum.Value & FILE_EXT

'Check whether specified job bag file exists
If Len(Dir(strFileSpec)) = 0 Then
MsgBox "File " & strFileSpec & " not found", _
vbInformation + vbOkOnly, "Import Job Bag"

Else 'file found, import it to temp table
DoCmd.TransferText acImportDelim, , "tblTemp", _
strFileSpec, True
'Append it to main table
CurrentDB.Execute qryAppendJobBag
End if
 
Back
Top