I dont think I understand the problem:

I thought you wanted to use your UDF in an access query to populate a
recordset (cannot be done AFAIK).

I dont understand why you want to add a field to a recordset (you can do
that for an updateable recordset, and you can use your UDF to update a
recordset), if you have already added the field in a maketable procedure.

To add a field to the recordset resulting from a query you just add a field
to the SELECT.

I thought you wanted to populate a recordset from a query that contained a
parameter that was input by the user:
if so just slot the parameter value into the sql string (something like
WHERE fieldname="Paramvalue")

if the parameter value is the result of using your function on user input
then you just call the function in your excel VBA and put the result in the
sql.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Matt." wrote in message
.. .
Thanks Charles!

I have managed to fire a UDF referenced in an Access Module from an Access
automation session triggered from Excel. Here's the code I use:

Dim acApp As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase
("\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003. mdb")
acApp.Run ("WeeklyUtilization") 'procedure found in Module 1 of the
Access database
acApp.Quit
Set acApp = Nothing

The procedure WeeklyUtilization calls the function WeekNumber which is a
UDF. The procedure deletes and then repopulates a table.

It has been suggested to me previously that I cannot add a field to a
recordset. If this is so, then how do I create the field I need? For
example, if the value of ProDate is 2003/12/01, I want a YearWeek text
field
that looks like 2003-49. An example (even aircode) would be much
appreciated.

I'll investigate the querydef option, as I've never used it before.

Thanks again,
Matt.


"Charles Williams" wrote in message
...
Hi Matt,

I would use DAO/ADO to create/revise a Querydef that you pass the sql
to,
(you dont need to start access via automation etc), and then create a
recordset from the querydef.

However you will have a problem with the user-defined function, because
I
do
not think it is possible to get Dao/Ado/Automation to utilise a
user-defined
function built into the query, even if stored in both Access or Excel. I
spent two days recently trying to do this and concluded that it was not
possible (other good authority also thought so).

The alternative way I finally used is to loop through the recordset and
execute the function for each record.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm


"Matt." wrote in message
.. .
Sorry for the repost, but I'm hoping the question is difficult, rather
than
everybody's ignoring me..... ;-)

Hi all!

I'm really stuck on this. The problem I'm trying to solve is two
fold.
The
first is that I need to pass a value entered by a user in Excel to a
query
residing in Access. Then I need to populate a recordset with the
result
for
later processing. The second problem is the query MUST use a User
Defined
Function (WeekNumber). My non-working code is below.

I'm using automation to resolve the UDF problem. The problem is
acApp.Run
expects the name of a module (or function within), and I want to run
the
SQL
statement I've built. I could write the query into an Access module
if
I
knew a way to pass the user entry from Excel to Access. A day's worth
of
Google searching and testing has failed to provide me with an answer.

Any help greatly appreciated.

cheers,
Matt.
========================================

Sub GetQuality()
On Error GoTo ErrorHandler
Dim strJob As String
Dim strSQL As String
Dim acApp As Object
Dim rsJobData As ADODB.Recordset
strJob = "1240" 'User entry at a later date

strSQL = "SELECT [Job #], Year(ProDate) & '-' & WeekNumber(ProDate) as
YearWeek, " _
& "SUM([Cast Shots]) AS CastShots " _
& "FROM Production " _
& "WHERE LEFT([Job #],4) = " & strJob & " " _
& "AND WeekNumber(ProDate) = WeekNumber(Date())-11 " _
& "AND Year(ProDate) = Year(Date()) " _
& "GROUP BY [Job #], Year(ProDate) & '-' &
WeekNumber(ProDate)"

MsgBox strSQL

Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase
("\\hserver01\hworking\pcntrl\FORECAST\xAmcan2003. mdb")
'Set rsJobData = acApp.Run(strSQL)
'While Not rsJobData.EOF
' MsgBox "Job: " & rsJobData("Job #") & "YearWeek: " &
rsJobData("YearWeek")
' rsJobData.MoveNext
'Wend

SubQuit:
acApp.Quit
Set acApp = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Description, vbCritical
GoTo SubQuit

End Sub