top N values per group using VBA

[Kat]

Hello,
I am trying to follow an example for finding top N values using VBA given at
this link: http://support.microsoft.com/kb/210039/en-us#appliesto

I've had success with this in the Northwind example database (with my own
tables or with Northwind tables) ...

But in my own databases (with my own tables) I get "Compile error:
User'defined type not defined" with ", db As Database" highlighted in the
code when I compile in the module

and "Compile error.in query expression
'(((Orders.OrderDate)>=NthInGroup([Customers].[CustomerID],5)))'." in the
query design view when I try to run the query.

Am I missing a step? Any help would be very appreciated. Thank you.

 

[Duane Hookom]

You may need to set a reference to the DAO object library. While in a module
window, select Tools->References and scroll down to find the MS DAO 3.x or
higher object library and check it. Then close the references and attempt to
compile your application.

 

[Kat]

Hi,
I added the MS DAO 3.6 object library.
Now a different set of errors pop up:
Upon compiling: "Set rs = db.OpenRecordset (SQL)" gets an arrow and yellow
highlighting in the code.
Upon tying to run the query, "Run-time error '13': Type mismatch" pops up.
Thank you for your help so far!

You may need to set a reference to the DAO object library. While in a module
window, select Tools->References and scroll down to find the MS DAO 3.x or
higher object library and check it. Then close the references and attempt to
compile your application.
--
Duane Hookom
Microsoft Access MVP

Hello,
I am trying to follow an example for finding top N values using VBA given at
this link: http://support.microsoft.com/kb/210039/en-us#appliesto

I've had success with this in the Northwind example database (with my own
tables or with Northwind tables) ...

But in my own databases (with my own tables) I get "Compile error:
User'defined type not defined" with ", db As Database" highlighted in the
code when I compile in the module

and "Compile error.in query expression
'(((Orders.OrderDate)>=NthInGroup([Customers].[CustomerID],5)))'." in the
query design view when I try to run the query.

Am I missing a step? Any help would be very appreciated. Thank you.

 

[Duane Hookom]

It would help to see more code. I expect the declarations aren't explicit:
Dim db As DAO.Database
Dim rs As DAO.Recordset

If you didn't specify DAO on the Recordset, it might have defaulted to an
ADO recordset which could explain the error.

--
Duane Hookom
Microsoft Access MVP

Hi,
I added the MS DAO 3.6 object library.
Now a different set of errors pop up:
Upon compiling: "Set rs = db.OpenRecordset (SQL)" gets an arrow and yellow
highlighting in the code.
Upon tying to run the query, "Run-time error '13': Type mismatch" pops up.
Thank you for your help so far!

You may need to set a reference to the DAO object library. While in a module
window, select Tools->References and scroll down to find the MS DAO 3.x or
higher object library and check it. Then close the references and attempt to
compile your application.
--
Duane Hookom
Microsoft Access MVP

Hello,
I am trying to follow an example for finding top N values using VBA given at
this link: http://support.microsoft.com/kb/210039/en-us#appliesto

I've had success with this in the Northwind example database (with my own
tables or with Northwind tables) ...

But in my own databases (with my own tables) I get "Compile error:
User'defined type not defined" with ", db As Database" highlighted in the
code when I compile in the module

and "Compile error.in query expression
'(((Orders.OrderDate)>=NthInGroup([Customers].[CustomerID],5)))'." in the
query design view when I try to run the query.

Am I missing a step? Any help would be very appreciated. Thank you.

 

[Kat]

Thank you!. Adding the "DAO." as you showed below (along with adding the MS
DAO 3.6 object library) makes the query / code work in my databases. (I took
the starting code straight from that shown at this location
http://support.microsoft.com/kb/210039/en-us# and substitued my table and
fields).

I need a little more help though. I think that I created an error in one
of my databases when adding the object library or when importing a module.
On trying to remove any module from the project manager list, a pop up shows,
"Error accessing file. Network connection my have been lost." I deleted the
modules from the main access database view, and they no longer show there.
In the list of available references for this database there are two MS Office
Object Libraries (9.0 & 12.0). When trying to remove the 12.0 an pop up
shows, "Can't perform requested operation". Could the two office libraries
be the problem? Is there another way to remove these or the modules (which
don't work)? Sorry, I am new to VBA and libraries.

Thank you so much for you help!

It would help to see more code. I expect the declarations aren't explicit:
Dim db As DAO.Database
Dim rs As DAO.Recordset

If you didn't specify DAO on the Recordset, it might have defaulted to an
ADO recordset which could explain the error.

--
Duane Hookom
Microsoft Access MVP

Hi,
I added the MS DAO 3.6 object library.
Now a different set of errors pop up:
Upon compiling: "Set rs = db.OpenRecordset (SQL)" gets an arrow and yellow
highlighting in the code.
Upon tying to run the query, "Run-time error '13': Type mismatch" pops up.
Thank you for your help so far!

You may need to set a reference to the DAO object library. While in a module
window, select Tools->References and scroll down to find the MS DAO 3.x or
higher object library and check it. Then close the references and attempt to
compile your application.
--
Duane Hookom
Microsoft Access MVP


:

Hello,
I am trying to follow an example for finding top N values using VBA given at
this link: http://support.microsoft.com/kb/210039/en-us#appliesto

I've had success with this in the Northwind example database (with my own
tables or with Northwind tables) ...

But in my own databases (with my own tables) I get "Compile error:
User'defined type not defined" with ", db As Database" highlighted in the
code when I compile in the module

and "Compile error.in query expression
'(((Orders.OrderDate)>=NthInGroup([Customers].[CustomerID],5)))'." in the
query design view when I try to run the query.

Am I missing a step? Any help would be very appreciated. Thank you.

 

[Duane Hookom]

You should only have a single MS Office Object library checked. Uncheck the
lowest number. All you should have done is checked the DAO in addition to the
ones that were already checked.

--
Duane Hookom
Microsoft Access MVP

Thank you!. Adding the "DAO." as you showed below (along with adding the MS
DAO 3.6 object library) makes the query / code work in my databases. (I took
the starting code straight from that shown at this location
http://support.microsoft.com/kb/210039/en-us# and substitued my table and
fields).

I need a little more help though. I think that I created an error in one
of my databases when adding the object library or when importing a module.
On trying to remove any module from the project manager list, a pop up shows,
"Error accessing file. Network connection my have been lost." I deleted the
modules from the main access database view, and they no longer show there.
In the list of available references for this database there are two MS Office
Object Libraries (9.0 & 12.0). When trying to remove the 12.0 an pop up
shows, "Can't perform requested operation". Could the two office libraries
be the problem? Is there another way to remove these or the modules (which
don't work)? Sorry, I am new to VBA and libraries.

Thank you so much for you help!

It would help to see more code. I expect the declarations aren't explicit:
Dim db As DAO.Database
Dim rs As DAO.Recordset

If you didn't specify DAO on the Recordset, it might have defaulted to an
ADO recordset which could explain the error.

--
Duane Hookom
Microsoft Access MVP

Hi,
I added the MS DAO 3.6 object library.
Now a different set of errors pop up:
Upon compiling: "Set rs = db.OpenRecordset (SQL)" gets an arrow and yellow
highlighting in the code.
Upon tying to run the query, "Run-time error '13': Type mismatch" pops up.
Thank you for your help so far!


:

You may need to set a reference to the DAO object library. While in a module
window, select Tools->References and scroll down to find the MS DAO 3.x or
higher object library and check it. Then close the references and attempt to
compile your application.
--
Duane Hookom
Microsoft Access MVP


:

Hello,
I am trying to follow an example for finding top N values using VBA given at
this link: http://support.microsoft.com/kb/210039/en-us#appliesto

I've had success with this in the Northwind example database (with my own
tables or with Northwind tables) ...

But in my own databases (with my own tables) I get "Compile error:
User'defined type not defined" with ", db As Database" highlighted in the
code when I compile in the module

and "Compile error.in query expression
'(((Orders.OrderDate)>=NthInGroup([Customers].[CustomerID],5)))'." in the
query design view when I try to run the query.

Am I missing a step? Any help would be very appreciated. Thank you.