Code works in several databases except one

  • Thread starter Thread starter Billy Rogers
  • Start date Start date
B

Billy Rogers

I have a form that I use in several databases. It lets the user select a
starting and ending query from a listbox then it runs all the queries in
between. There is a refresh button that loads the query list into a list
box. I've included the query below that deletes all the queries from the
[Query] table and repopulates the table so that any new queries show up. The
it refreshed the query listbox. This works fine in several databases except
one. I've checked and the references are identical.

It errors out on the line Set dbs = Application.CurrentData and says
"Compile error: invalid outside procedure"

I can't figure out why it won't work in this one database.


Dim obj, dbs As Object
Dim SQLStr, QuerySource As String
Dim number As Integer



Set dbs = Application.CurrentData ' ***this is where the error message pops
up ***
Set obj = dbs.AllQueries


number = 0
QuerySource = "INSERT into Queries (QueryList, QueryNumber) VALUES ('"

DoCmd.SetWarnings 0

Delete = "DELETE Queries.* FROM Queries"
DoCmd.RunSQL Delete


For Each obj In dbs.AllQueries
QuerySource = QuerySource & obj.Name & "', " & number & ")"
SQLStr = QuerySource
DoCmd.RunSQL SQLStr
number = number + 1
QuerySource = "INSERT into Queries (QueryList, QueryNumber) VALUES ('"
Next obj
lstQueryList.Requery

--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/
 
It's in a button. I actually imported the form twice from a two other
databases that i use it in without any problems.
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/


Douglas J. Steele said:
Sounds as though that code isn't in a Sub or Function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Billy Rogers said:
I have a form that I use in several databases. It lets the user select a
starting and ending query from a listbox then it runs all the queries in
between. There is a refresh button that loads the query list into a list
box. I've included the query below that deletes all the queries from the
[Query] table and repopulates the table so that any new queries show up.
The
it refreshed the query listbox. This works fine in several databases
except
one. I've checked and the references are identical.

It errors out on the line Set dbs = Application.CurrentData and says
"Compile error: invalid outside procedure"

I can't figure out why it won't work in this one database.


Dim obj, dbs As Object
Dim SQLStr, QuerySource As String
Dim number As Integer



Set dbs = Application.CurrentData ' ***this is where the error message
pops
up ***
Set obj = dbs.AllQueries


number = 0
QuerySource = "INSERT into Queries (QueryList, QueryNumber) VALUES ('"

DoCmd.SetWarnings 0

Delete = "DELETE Queries.* FROM Queries"
DoCmd.RunSQL Delete


For Each obj In dbs.AllQueries
QuerySource = QuerySource & obj.Name & "', " & number & ")"
SQLStr = QuerySource
DoCmd.RunSQL SQLStr
number = number + 1
QuerySource = "INSERT into Queries (QueryList, QueryNumber) VALUES ('"
Next obj
lstQueryList.Requery

--
"Just because you don''t know how to do something doesn''t mean it can''t
be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/
Click to expand...
Click to expand...
 
Actually you were right Doug. In the first form the "sub" line was deleted.
I didn't realize this and then when i tried to use the imported form it
would error out on the other form.......I didn't realize the problem was in a
different form.

Thanks,
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/


Billy Rogers said:
It's in a button. I actually imported the form twice from a two other
databases that i use it in without any problems.
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/


Douglas J. Steele said:
Sounds as though that code isn't in a Sub or Function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Billy Rogers said:
I have a form that I use in several databases. It lets the user select a
starting and ending query from a listbox then it runs all the queries in
between. There is a refresh button that loads the query list into a list
box. I've included the query below that deletes all the queries from the
[Query] table and repopulates the table so that any new queries show up.
The
it refreshed the query listbox. This works fine in several databases
except
one. I've checked and the references are identical.

It errors out on the line Set dbs = Application.CurrentData and says
"Compile error: invalid outside procedure"

I can't figure out why it won't work in this one database.


Dim obj, dbs As Object
Dim SQLStr, QuerySource As String
Dim number As Integer



Set dbs = Application.CurrentData ' ***this is where the error message
pops
up ***
Set obj = dbs.AllQueries


number = 0
QuerySource = "INSERT into Queries (QueryList, QueryNumber) VALUES ('"

DoCmd.SetWarnings 0

Delete = "DELETE Queries.* FROM Queries"
DoCmd.RunSQL Delete


For Each obj In dbs.AllQueries
QuerySource = QuerySource & obj.Name & "', " & number & ")"
SQLStr = QuerySource
DoCmd.RunSQL SQLStr
number = number + 1
QuerySource = "INSERT into Queries (QueryList, QueryNumber) VALUES ('"
Next obj
lstQueryList.Requery

--
"Just because you don''t know how to do something doesn''t mean it can''t
be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/
Click to expand...
Click to expand...
Click to expand...
 
Back
Top