Out of context in watch window

[krulle]

Hello,

I have a table with 120 records and 2 fields. I query this table. In the query a 3th field has been created referring to a function in a module.

The module looks like this:

Public function company (id as integer) as string
if id = 100 then
open "c:\company.txt" for output as #1
print #1, "record 100"
close #1
end if
end function.

If I run the query the file company.txt is not created. When I put a watch window on the id value I see that the id value gets "out of context" exactly on record 36 and the functions stops.

If I change the function like below the file company.txt gets created well

Public function company (id as integer) as string
if id = 20 then
open "c:\company.txt" for output as #1
print #1, "record 20"
close #1
end if
end function.

I really don't understand why the function stops each time on record 36.
I created a new table with other values, made a new access database on my and other PC. but still the same problem persists.

I getting more and more hopeless

Does someone know what could be the solution?

ps: This is an example of my problem. In my real program I have to make 150 txt files. My due date to find a solution is 16/9 as I have to import these txt files in another application.

Thanks a lot!!!

claeystina@hotmail.com

 

[WayneRyan]

Krulle,

I put your company function in a Public Module and it works fine.

In the query I put a new field:

NewField: Company([ID])

It works OK, I can't generate your error.

btw,

Your function has no return value.

Also, exactly what are your trying to do with this code?

Wayne

 

[RuralGuy]

I think Wayne figured it out. Change your Function to a Sub and see what happens.

 

[WayneRyan]

RG,

I don't know that even changing it to a Sub helps.

The intended action of the function (writing to a file) works
just fine. It would do that as a Sub just as well. I just
don't know what the problem is.

It's like there are too many files open or something. Nothing
like the code posted shows.

Wayne

 

[RuralGuy]

As Wayne suggested, Functions return values and you do not seem to avail yourself of the value. Maybe Access burps after 36 items left on the stack. Did you try changing to a Sub?

 

[WayneRyan]

RG,

As far as stacks go, I know that Access can handle recursion at least
that deep.

This seems to be more like exhausting the number of File Handles or
something like that. "end function." has a period and obviously can't
be the "real" code being run. If the Open statement used the FreeFile
call (and was outside the loop) it could promote behavior like that.

Despite not returning a value, the function call is resolved and won't
lead to an error.

In either case, I don't think that the approach:

Table --> Query --> PublicFunction --> TextFile

is really what's needed.

Regardless of why the initial function did/didn't work, I'm just
curious as to what we're doing in the first place.

Oh well, it's nice to just look at it as a third-party.

Wayne

 

[workmad3]

I'm curious as to what this code is being used for as well. From all that has been said, you seem to be using a function with almost identical code lots of times and using a seperate fuction for each one. Wouldn't it be better to, for example, pass in the filename as a parameter etc. Some more details would be useful, as then could probably help a bit better.

 

[RuralGuy]

Wayne,
Obviously I thought I was speaking to the OP. Duhh. Sorry. You have very valid points and I agree completely. I'll pay closer attention from now on. The OP has not gotten back to work yet. Maybe the mystery will be cleared up up today.