Code works in query but not in vba - help

S

SwissMiss

Need to use the same functionality to fill a table with info. But do not know
the name of the table before hand.

The code I created gets the error message "Item not found in this
collection. error 3265" as if it is not finding the table.

However if I copy the generated strSQL from the intermediate window and
paste it in a query, it works fine.

What do I need to do with my code, which is posted below. All help
appreciated.

Dim strSQL As String, myTable As String, qdf As DAO.QueryDef, myYear As
Integer
myTable = "tblM_IUFList" & "_" & Me.cboIUFList.Column(1)
myYear = Me.cboIUFlistYear
CurrentDb.TableDefs.Refresh
'either delete stuff from current table of create new one
If doesTableFormExist(myTable, "Table") = True Then
strSQL = "DELETE [" & myTable & "].* FROM [" & myTable & "];"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges
Else
DoCmd.CopyObject , myTable, acTable, "tblM_IUFListModel"
CurrentDb.TableDefs.Refresh
End If
'update org ids and info
strSQL = "INSERT INTO [" & myTable & "] ( Organization_ID, ReportOrder,
Region, SortOrder, CountryName, Name, MergedOrgID, SectorName, LastYear )" &
vbCrLf
strSQL = strSQL & "SELECT DISTINCT tblOrganizations.Organization_Id,
tblReportRegion.ReportOrder, tblReportRegion.English,
IIf([tblOrganizations]![Organization_Type_Id]=2,2,1) AS Expr1,
tblCountry.Country, [tblOrganizations]![Organization_Name] & IIf(Not
IsNull([tblOrganizations]![Abbreviation]),' [' &
[tblOrganizations]![Abbreviation] & ']','') AS Expr2,
tblOrganizations.MergedOrgID, tblIUF_List.List_Name, " & myYear & " AS Expr3"
& vbCrLf
strSQL = strSQL & "FROM tblReportRegion INNER JOIN ((tblCountry INNER JOIN
tblOrganizations ON tblCountry.Country_Id = tblOrganizations.Country_Id)
INNER JOIN (tblContacts INNER JOIN (tblIUF_List INNER JOIN
tblIUF_List_LinkedContacts ON tblIUF_List.List_Id =
tblIUF_List_LinkedContacts.List_Id) ON tblContacts.Contact_Id =
tblIUF_List_LinkedContacts.Contact_Id) ON tblOrganizations.Organization_Id =
tblContacts.Organization_Id) ON tblReportRegion.ReportRegion_ID =
tblCountry.ReportRegion_ID" & vbCrLf
strSQL = strSQL & "WHERE (((tblIUF_List_LinkedContacts.List_Id)= " &
Me.cboIUFList & "))" & vbCrLf
strSQL = strSQL & "ORDER BY tblOrganizations.Organization_Id;"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges
Set qdf = Nothing
etc.
 
S

SteveS

I see a few things that will/could cause problems.


First, you have a field named "NAME". "NAME" is a reserved word in Access.
Plus, it is not descriptive -'Name' of what?? A car, an animal, a city??

Next, you don't need to use " & vbCrLf" to seperate clauses in the SQL
string. Instead, use a space at the front . Example:

strSQL = strSQL & " SELECT DISTINCT tblOrganizations.Organization_Id, "

Space added between the double quote and the "S" in Select.....


The main problem is that SQL does not understand the function IIF() when it
is in a SQL string. If you add a "Debug.Print strSQL" statement or "MsgBox
strSQL", and looked at the SQL string, you would see that it is malformed.

You need to treat the IIF() functions the same as a variable, ie concantate
the results.

Example:

strSQL = strSQL & " WHERE (((tblIUF_List_LinkedContacts.List_Id)= " &
Me.cboIUFList & "))"

The variable "Me.cboIUFList" is concantated to, not included within the
string.


You could create a temporary Sub with the following code, modify and test
the SQL string until it is right, then move it up to the real Sub...

'-------------------------------
Public Sub test()
Dim strSQL As String, myTable As String, qdf As DAO.QueryDef, myYear As
Integer
myTable = "tblM_IUFList" & "_" & Me.cboIUFList.Column(1)
myYear = Me.cboIUFlistYear


'generate a vaild SQL string
strSQL = "INSERT INTO [" & myTable & "] ( Organization_ID, ReportOrder,
Region, SortOrder, CountryName, Name, MergedOrgID, SectorName, LastYear )"

strSQL = strSQL & " SELECT DISTINCT tblOrganizations.Organization_Id,
tblReportRegion.ReportOrder, tblReportRegion.English,
IIf([tblOrganizations]![Organization_Type_Id]=2,2,1) AS Expr1,
tblCountry.Country, [tblOrganizations]![Organization_Name] & IIf(Not
IsNull([tblOrganizations]![Abbreviation]),' [' &
[tblOrganizations]![Abbreviation] & ']','') AS Expr2,
tblOrganizations.MergedOrgID, tblIUF_List.List_Name, " & myYear & " AS Expr3"

strSQL = strSQL & " FROM tblReportRegion INNER JOIN ((tblCountry INNER JOIN
tblOrganizations ON tblCountry.Country_Id = tblOrganizations.Country_Id)
INNER JOIN (tblContacts INNER JOIN (tblIUF_List INNER JOIN
tblIUF_List_LinkedContacts ON tblIUF_List.List_Id =
tblIUF_List_LinkedContacts.List_Id) ON tblContacts.Contact_Id =
tblIUF_List_LinkedContacts.Contact_Id) ON tblOrganizations.Organization_Id =
tblContacts.Organization_Id) ON tblReportRegion.ReportRegion_ID =
tblCountry.ReportRegion_ID"

strSQL = strSQL & " WHERE (((tblIUF_List_LinkedContacts.List_Id)= " &
Me.cboIUFList & "))"

strSQL = strSQL & " ORDER BY tblOrganizations.Organization_Id;"

'show me in the immediate window
DEBUG.PRINT strSQL

'or
' msgbox strSQL


End Sub
'-------------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SwissMiss said:
Need to use the same functionality to fill a table with info. But do not know
the name of the table before hand.

The code I created gets the error message "Item not found in this
collection. error 3265" as if it is not finding the table.

However if I copy the generated strSQL from the intermediate window and
paste it in a query, it works fine.

What do I need to do with my code, which is posted below. All help
appreciated.

Dim strSQL As String, myTable As String, qdf As DAO.QueryDef, myYear As
Integer
myTable = "tblM_IUFList" & "_" & Me.cboIUFList.Column(1)
myYear = Me.cboIUFlistYear
CurrentDb.TableDefs.Refresh
'either delete stuff from current table of create new one
If doesTableFormExist(myTable, "Table") = True Then
strSQL = "DELETE [" & myTable & "].* FROM [" & myTable & "];"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges
Else
DoCmd.CopyObject , myTable, acTable, "tblM_IUFListModel"
CurrentDb.TableDefs.Refresh
End If
'update org ids and info
strSQL = "INSERT INTO [" & myTable & "] ( Organization_ID, ReportOrder,
Region, SortOrder, CountryName, Name, MergedOrgID, SectorName, LastYear )" &
vbCrLf
strSQL = strSQL & "SELECT DISTINCT tblOrganizations.Organization_Id,
tblReportRegion.ReportOrder, tblReportRegion.English,
IIf([tblOrganizations]![Organization_Type_Id]=2,2,1) AS Expr1,
tblCountry.Country, [tblOrganizations]![Organization_Name] & IIf(Not
IsNull([tblOrganizations]![Abbreviation]),' [' &
[tblOrganizations]![Abbreviation] & ']','') AS Expr2,
tblOrganizations.MergedOrgID, tblIUF_List.List_Name, " & myYear & " AS Expr3"
& vbCrLf
strSQL = strSQL & "FROM tblReportRegion INNER JOIN ((tblCountry INNER JOIN
tblOrganizations ON tblCountry.Country_Id = tblOrganizations.Country_Id)
INNER JOIN (tblContacts INNER JOIN (tblIUF_List INNER JOIN
tblIUF_List_LinkedContacts ON tblIUF_List.List_Id =
tblIUF_List_LinkedContacts.List_Id) ON tblContacts.Contact_Id =
tblIUF_List_LinkedContacts.Contact_Id) ON tblOrganizations.Organization_Id =
tblContacts.Organization_Id) ON tblReportRegion.ReportRegion_ID =
tblCountry.ReportRegion_ID" & vbCrLf
strSQL = strSQL & "WHERE (((tblIUF_List_LinkedContacts.List_Id)= " &
Me.cboIUFList & "))" & vbCrLf
strSQL = strSQL & "ORDER BY tblOrganizations.Organization_Id;"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges
Set qdf = Nothing
etc.
Click to expand...
 
S

SwissMiss

Thanks for your response. Unfortunately, while you provided lots of good
tips, it did not answer my question. So let me try again.

In my code the first works and the second does not. Can you explain why.

this works

myTable = "tblM_IUFList" & "_" & Me.cboIUFList
strSQL = "DELETE " & myTable & ".* FROM " & myTable & ";"
DoCmd.RunSQL "DELETE " & myTable & ".* FROM " & myTable & ";"

this gives me the error message "Item not found in this selection. error 3265"

myTable = "tblM_IUFList" & "_" & Me.cboIUFList
strSQL = "DELETE " & myTable & ".* FROM " & myTable & ";"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges

--
SwissMiss


SteveS said:
I see a few things that will/could cause problems.


First, you have a field named "NAME". "NAME" is a reserved word in Access.
Plus, it is not descriptive -'Name' of what?? A car, an animal, a city??

Next, you don't need to use " & vbCrLf" to seperate clauses in the SQL
string. Instead, use a space at the front . Example:

strSQL = strSQL & " SELECT DISTINCT tblOrganizations.Organization_Id, "

Space added between the double quote and the "S" in Select.....


The main problem is that SQL does not understand the function IIF() when it
is in a SQL string. If you add a "Debug.Print strSQL" statement or "MsgBox
strSQL", and looked at the SQL string, you would see that it is malformed.

You need to treat the IIF() functions the same as a variable, ie concantate
the results.

Example:

strSQL = strSQL & " WHERE (((tblIUF_List_LinkedContacts.List_Id)= " &
Me.cboIUFList & "))"

The variable "Me.cboIUFList" is concantated to, not included within the
string.


You could create a temporary Sub with the following code, modify and test
the SQL string until it is right, then move it up to the real Sub...

'-------------------------------
Public Sub test()
Dim strSQL As String, myTable As String, qdf As DAO.QueryDef, myYear As
Integer
myTable = "tblM_IUFList" & "_" & Me.cboIUFList.Column(1)
myYear = Me.cboIUFlistYear


'generate a vaild SQL string
strSQL = "INSERT INTO [" & myTable & "] ( Organization_ID, ReportOrder,
Region, SortOrder, CountryName, Name, MergedOrgID, SectorName, LastYear )"

strSQL = strSQL & " SELECT DISTINCT tblOrganizations.Organization_Id,
tblReportRegion.ReportOrder, tblReportRegion.English,
IIf([tblOrganizations]![Organization_Type_Id]=2,2,1) AS Expr1,
tblCountry.Country, [tblOrganizations]![Organization_Name] & IIf(Not
IsNull([tblOrganizations]![Abbreviation]),' [' &
[tblOrganizations]![Abbreviation] & ']','') AS Expr2,
tblOrganizations.MergedOrgID, tblIUF_List.List_Name, " & myYear & " AS Expr3"

strSQL = strSQL & " FROM tblReportRegion INNER JOIN ((tblCountry INNER JOIN
tblOrganizations ON tblCountry.Country_Id = tblOrganizations.Country_Id)
INNER JOIN (tblContacts INNER JOIN (tblIUF_List INNER JOIN
tblIUF_List_LinkedContacts ON tblIUF_List.List_Id =
tblIUF_List_LinkedContacts.List_Id) ON tblContacts.Contact_Id =
tblIUF_List_LinkedContacts.Contact_Id) ON tblOrganizations.Organization_Id =
tblContacts.Organization_Id) ON tblReportRegion.ReportRegion_ID =
tblCountry.ReportRegion_ID"

strSQL = strSQL & " WHERE (((tblIUF_List_LinkedContacts.List_Id)= " &
Me.cboIUFList & "))"

strSQL = strSQL & " ORDER BY tblOrganizations.Organization_Id;"

'show me in the immediate window
DEBUG.PRINT strSQL

'or
' msgbox strSQL


End Sub
'-------------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


SwissMiss said:
Need to use the same functionality to fill a table with info. But do not know
the name of the table before hand.

The code I created gets the error message "Item not found in this
collection. error 3265" as if it is not finding the table.

However if I copy the generated strSQL from the intermediate window and
paste it in a query, it works fine.

What do I need to do with my code, which is posted below. All help
appreciated.

Dim strSQL As String, myTable As String, qdf As DAO.QueryDef, myYear As
Integer
myTable = "tblM_IUFList" & "_" & Me.cboIUFList.Column(1)
myYear = Me.cboIUFlistYear
CurrentDb.TableDefs.Refresh
'either delete stuff from current table of create new one
If doesTableFormExist(myTable, "Table") = True Then
strSQL = "DELETE [" & myTable & "].* FROM [" & myTable & "];"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges
Else
DoCmd.CopyObject , myTable, acTable, "tblM_IUFListModel"
CurrentDb.TableDefs.Refresh
End If
'update org ids and info
strSQL = "INSERT INTO [" & myTable & "] ( Organization_ID, ReportOrder,
Region, SortOrder, CountryName, Name, MergedOrgID, SectorName, LastYear )" &
vbCrLf
strSQL = strSQL & "SELECT DISTINCT tblOrganizations.Organization_Id,
tblReportRegion.ReportOrder, tblReportRegion.English,
IIf([tblOrganizations]![Organization_Type_Id]=2,2,1) AS Expr1,
tblCountry.Country, [tblOrganizations]![Organization_Name] & IIf(Not
IsNull([tblOrganizations]![Abbreviation]),' [' &
[tblOrganizations]![Abbreviation] & ']','') AS Expr2,
tblOrganizations.MergedOrgID, tblIUF_List.List_Name, " & myYear & " AS Expr3"
& vbCrLf
strSQL = strSQL & "FROM tblReportRegion INNER JOIN ((tblCountry INNER JOIN
tblOrganizations ON tblCountry.Country_Id = tblOrganizations.Country_Id)
INNER JOIN (tblContacts INNER JOIN (tblIUF_List INNER JOIN
tblIUF_List_LinkedContacts ON tblIUF_List.List_Id =
tblIUF_List_LinkedContacts.List_Id) ON tblContacts.Contact_Id =
tblIUF_List_LinkedContacts.Contact_Id) ON tblOrganizations.Organization_Id =
tblContacts.Organization_Id) ON tblReportRegion.ReportRegion_ID =
tblCountry.ReportRegion_ID" & vbCrLf
strSQL = strSQL & "WHERE (((tblIUF_List_LinkedContacts.List_Id)= " &
Me.cboIUFList & "))" & vbCrLf
strSQL = strSQL & "ORDER BY tblOrganizations.Organization_Id;"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges
Set qdf = Nothing
etc.
Click to expand...
Click to expand...
 
B

Bob Hairgrove

Thanks for your response. Unfortunately, while you provided lots of good
tips, it did not answer my question. So let me try again.

In my code the first works and the second does not. Can you explain why.

this works

myTable = "tblM_IUFList" & "_" & Me.cboIUFList
strSQL = "DELETE " & myTable & ".* FROM " & myTable & ";"
DoCmd.RunSQL "DELETE " & myTable & ".* FROM " & myTable & ";"

this gives me the error message "Item not found in this selection. error3265"

myTable = "tblM_IUFList" & "_" & Me.cboIUFList
strSQL = "DELETE " & myTable & ".* FROM " & myTable & ";"
Set qdf = CurrentDb.QueryDefs(strSQL)
qdf.Execute dbSeeChanges
Click to expand...

It doesn't work because CurrentDb.QueryDefs() expects the name of an
existing query in your database.

You can also just write this:
CurrentDb.Execute strSQL, dbSeeChanges

That would be better than using RunSQL.
 
S

SwissMiss

Thanks Bob. That clarifies alot. Feel silly I didn't think of that. Have a
good day. You have made mine.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DoComd.RunSQL works, Execute doesn't - WHY??? 4
CODE HELP! 2
Delete Query syntax in VBA querydef 9
Creating a select query using sql in VBA 6
SQL Query def by vba not working.....please help 2
SQL statement works in a query, but not in VBA 5
Query works, SQL in VBA does not 4
Works in query, but not in VBA 1

Top