Hello,

I am a total newcomer to programming, and I am having trouble with a
macro I am trying to adapt. All I want to do is use strBM to filter
the import from MSAccess. It adds the correct name from strBM for the
added sheet, but the import doesn't bring any results in. I've tried
importing manually and that works fine. Its the syntax for "WHERE
(qryInflationIndicesBudgetsWithout.FiledUserName=s trBM)"

thats at fault I know, but I've tried reading the Help files, my
"Learn Excel Programming in 24 Hours" and scoured the net but I am
still at a loss. I am sure someone can spot straight away what I have
done wrong, can someone correct my lousy syntax?!

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 22/09/2003 by Luke Brunning
'
'
Dim strBM As String

strBM = InputBox("Enter a Budget Manager: ")

Sheets.Add.Name = strBM

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access
Database;DBQ=H:\ms_access\current.mdb;DefaultDir=H :\ms_access;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTi" _
), Array("meout=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT qryInflationIndicesBudgetsWithout.FiledUserName,
qryInflationIndicesBudgetsWithout.CostCentre,
qryInflationIndicesBudgetsWithout.Description,
qryInflationIndicesBudgetsWithout.FLCode, qryInflat" _
, _
"ionIndicesBudgetsWithout.`Inflation Index`,
qryInflationIndicesBudgetsWithout.Budget" & Chr(13) & "" & Chr(10) &
"FROM `H:\ms_access\current`.qryInflationIndicesBudgetsW ithout
qryInflationIndicesBudgetsWithout" & Chr(13) & "" & Chr(10) & "WHERE
(qryInflati" _
, _
"onIndicesBudgetsWithout.FiledUserName=strBM)" & Chr(13) & ""
& Chr(10) & "ORDER BY qryInflationIndicesBudgetsWithout.FLCode" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub