ExcelKid_10
Board Regular
- Joined
- Mar 17, 2004
- Messages
- 87
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
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