Can someone help me with a loop

[AndrewB]

Hi

I need help writing a bit of VBA code as I'm not really too familiar with it all.

I created the following to import a spreadsheet according to the values on a form and it works OK

Private Sub Command12_Click()
Dim InputDir, ImportFile, ImportExtension As String

InputDir = Forms![Input details].[Field1]
ImportFile = Forms![Input details].[Field2]
ImportExtension = Forms![Input details].[Field3]
DoCmd.TransferSpreadsheet acImport, , "DC Master Gelligron1", InputDir & ImportFile & "." & ImportExtension, True, ""

End Sub

However, I actually want to import a number of spreadsheets based on a screenful of data (i.e. a number of different records). The info will be on a table and will be displayed on the form (in Field1, Field2 and Field3 or whatever they end up being called). The name of the table will also be held in a field on the form - i.e. the "DC Master Gelligron1" will be changed, so don't worry about that.

How do I construct a loop or a DO While Not EOF or similar. I've been trying to cobble something together from other threads here but am failing.

I'm sure this has been answered before somewhere but would appreciate the help.

Thanks
Andrew

 

[KenHigg]

Hum...

Perhaps you could do this with ADO. Search Access help for recordsets, movenext, etc...

kh

 

[AndrewB]

Ken

It's looking hopeful - found a thread when I searched for Movenext as you said. I think its working now but will confirm and will reference the thread-that-might-have-saved-me in a few minutes.

 

[AndrewB]

OK

The thread that I found was
http://www.access-programmers.co.uk/forums/showthread.php?t=53452

Using that I can now go round the loop as many times as I have records but the question is : how do I make it chug through the records using the info on record 1 then record 2 then record 3 etc. I now have it importing record 1 eleven times which is cool but not particularly useful for my employers. The code now reads :

If Not rst.EOF Then

For i = 1 To rst.RecordCount

InputDir = Forms![Input details].[Field1]
ImportFile = Forms![Input details].[Field2]
ImportExtension = Forms![Input details].[Field3]
ImportTable = Forms![Input details].[Home name - table name]

DoCmd.TransferSpreadsheet acImport, , ImportTable, InputDir & ImportFile & "." & ImportExtension, True, ""

rst.MoveNext
Next i
End If

Presumably I need to make use of the i variable. How?

Thanks
Andrew

 

[AndrewB]

And while I'm at it, here is the next problem. If someone could solve either of these it would be progress for me.

I have added in a statement to clear down the file before the import takes place.

deletestatement = "DELETE FROM " & ImportTable
If I paste this into the Immediate window it looks to be giving the right format but when I run
CurrentProject.Connection.Execute deletestatement
it complains of a syntax error in the FROM clause and points to the Execute line.

If I hard code in the phrase "DELETE FROM DC Master Gelligron1", the code executes OK.

 

[KenHigg]

If Not rst.EOF Then

For i = 1 To rst.RecordCount

InputDir = rst![Field1]
ImportFile = rst![Field2]
ImportExtension = ![Field3]
ImportTable = rst![Home name - table name]

DoCmd.TransferSpreadsheet acImport, , ImportTable, InputDir & ImportFile & "." & ImportExtension, True, ""

rst.MoveNext
Next i
End If



???
kh

 

[AndrewB]

Ken

Fantastic. Working like a dream now.
My other query related to why my delete records statement wasn't working - and the answer was that the table name contained spaces.

I now have
deletestatement = "DELETE FROM [" & ImportTable & "]"

CurrentProject.Connection.Execute deletestatement

which is doing what I want it to do.

Thanks
Andrew