SQL Help!

[Excel_Kid10]

Hi all-

I need some expert advice on what I am doing wrong on my SQL/VBA code below....

Sub FundName()
Dim db As Database
Dim strSQL As String
Dim rs As Recordset
Dim FName As Variant

strSQL = "INSERT INTO tblTopExposures ( [Date], Port, FundOrAcct, Cusip, Description, [%MV] )" & _
"SELECT TOP 10 tblHoldings.Date, tblHoldings.Port, tblHoldings.FundOrAcct, tblHoldings.Cusip, tblHoldings.Description, [qry%MV].[%MV]" & _
"FROM [qry%MV] INNER JOIN tblHoldings ON ([qry%MV].Cusip = tblHoldings.Cusip) AND ([qry%MV].FundOrAcct = tblHoldings.FundOrAcct) AND ([qry%MV].Port = tblHoldings.Port) AND ([qry%MV].Date = tblHoldings.Date)" & _
"Where Port = FName;"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do Until rs.EOF
FName = rs
DoCmd.RunSQL strSQL
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub

It keeps bombing out on me when the code hits this:

Set rs = CurrentDb.OpenRecordset(strSQL)

The end product of my SQL is simply to append the data using a loop that passes a Port name through as the criteria.

Any help is much appreciated!

Thanks!

EK

 

[pbaldy]

Welcome to the site. For starters, you have to concatenate variables into the SQL, like

"Where Port = " & FName & ";"

if Port is text,

"Where Port = '" & FName & "';"

That said, there seems to be a circular reference there. FName depends on the recordset, but the recordset depends on FName. Your recordset SQL is an append query, so I don't think there will be a recordset to step through. Can you give more detail on what you're trying to do?

 

[Excel_Kid1081]

Hi Paul-

Thanks for your reply...I had some trouble logging back in so I created a new user name....

I've done some tweaking of my code and I am very close to getting it to run....

Sub FundName()
Dim db As Database
Dim strSQL As String
Dim rs As Recordset
Dim FName As Variant
strSQL = "INSERT INTO tblTopExposures ( [Date], Port, FundOrAcct, Cusip, Description, [%MV] )SELECT TOP 10 tblHoldings.Date, tblFundsOrAccts.Port, tblHoldings.FundOrAcct, tblHoldings.Cusip, tblHoldings.Description, [qry%MV].[%MV]FROM ([qry%MV] INNER JOIN tblHoldings ON ([qry%MV].Cusip = tblHoldings.Cusip) AND ([qry%MV].FundOrAcct = tblHoldings.FundOrAcct) AND ([qry%MV].Port = tblHoldings.Port) AND ([qry%MV].Date = tblHoldings.Date)) INNER JOIN tblFundsOrAccts ON (tblFundsOrAccts.Port = tblHoldings.Port) AND ([qry%MV].Port = tblFundsOrAccts.Port) WHERE tblFundsOrAccts.Port=(FName);"

Set rs = CurrentDb.OpenRecordset("SELECT Port FROM tblFundsOrAccts")

Do Until rs.EOF
FName = rs!Port
DoCmd.RunSQL strSQL
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub

What seems to be happening is that the FName variable is not being passed correctly into the SQL string...the query will run, but since it is not recognizing a Port then it just shows blank rows.

Any thoughts? Thanks a million!!

EK

 

[pbaldy]

Uh, same thoughts as in post 2...you still aren't concatenating the variable into the string. Also, you will want to build the string within the loop to get the current value of the field, rather than before the recordset is opened (what do you think FName contains at the point you are currently building the string? Hint...nothing).