Import multiple text files with filename as column

[jdog1340]

Ok, this is driving me crazy. This code is supposed to import all text files
in a directory, input the name of the file (filename) in the FileName column
into a database. There are only two columns total (SerialNumber, and
FileName) I created a form called "Q-form". All works fine except that the
FileName column is only updated with the first filename for every row. No
matter how many files it imports, the filename is the same (the first .txt
file that it opens). Can you help me please....

Here is the code....
------------------------------------------------
Private Sub Command15_Click()
Dim path As String
Dim myfile As String
Dim FileName As String

path = "C:\temp\temp2\"
myfile = Dir(path & "*.txt", vbHidden) 'gets first txt file in path specified

Do While myfile <> "" 'will cause to loop through all txt files in path

DoCmd.TransferText acImportDelim, "Q spec", "Q db", path + myfile, -1
DoCmd.OpenForm "Q-form"
FileName = Left(myfile, 26)
Do While Forms![Q-form]![SerialNumber] <> ""

Forms![Q-form]![FileName] = FileName

DoCmd.GoToRecord , , acNext

Loop



DoCmd.Close


myfile = Dir 'grabs next txt file


Loop

End Sub

 

[Douglas J. Steele]

Forms![Q-form]![FileName] always refers to the same control on the form.

You really don't want to go via form controls. Insert the data directly into
the underlying table, either by using an INSERT INTO SQL statement or by
opening a recordset and adding the entry.