Newcomer to VBA; trouble with strings?

[reclusive monkey]

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=strBM)"

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(Array( _
"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`.qryInflationIndicesBudgetsWithout
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

 

[Jean-Paul Viel]

Hi,



Your strBM is a variable but you gave it as text. Make this modification:



"onIndicesBudgetsWithout.FiledUserName=" & strBM & ")"

 

[Luke Brunning]

Thanks for the reply. I tried what you suggested and got the error
message:-

Run-time error '1004':

SQL Syntax Error

Strangely enough though the VB editor highlights the

line .Refresh BackgroundQuery:=False

rather than the one with my problematic variable in.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!