Bizarre Problem!

[DALIEN51]

Hi All!

This to me seems absolutely bizzare! It happens in both Access 97 and Access 2003 so I know its not a version issue.

I have a Access database with a link table to a spreadsheet. I have written a query which displays data from this link table which filters out blank rows, the spreadsheet header etc and just leaves me with the data I want to import which first time I ran it was perfect and exactly what I wanted. However, it turns out that this query only works intermittently. If I run it subsequently I get a "numeric field overflow" error message. But if I go into the query design and resave it without making any changes it will then run again!!! Running it thereafter will bring back up the error message until I resave it again!!! Why is this? Any ideas anyone? I dont want to have to keep resaving it to run it.

Actually thinking about it unless I could of course resave it using code???

Regards,

Dalien51

 

[Uncle Gizmo]

Well the short answer is "I don't know". However I thought if I reiterate one of my war stories it may have some bearing on the matter, and maybe not!

I had a similar problem with publisher. I set up my information in a spreadsheet and I linked it to the fields in publisher, then when you printed, it would print out 50 A4 price labels for 50 products ALL different! It worked lovely,,, the first time! Then you had to go through the whole routine and reset it up again to get it to work right the next time.

Now bearing in mind this goes back years, and my memory is not as good as it was! (it never has been very good) whats my name? I did resolve the problem, and it was to do with the data in the first row in the spreadsheet.

Like I said, I can't remember the exact details so this may not make sense first time around, but here's the gist of it. If a particular field in the first row of the spreadsheet say for example had "double format" that set the whole column under that field to "double format". So if the next row had currency, that currency value would print out as "double format" and not currency. It would not print the"£" sign. It took me ages to discover this, I just wondered if it had any bearing on your case.

 

[The_Doc_Man]

When you get that error... does ANYTHING get displayed? Or does it just refuse to display even the first row?

Also, how are you filtering? If you have written some VBA code as a public function to do the filtering and it has an "Own" variable (or whatever the keyword is for a persistent, semi-permanent variable), look to that code to see what is overflowing the variable. 'cause the behavior you display isn't Datasheet View behavior. It is CODE behavior and Access doesn't have code like that in its libraries.

That would also occur if you declared your variable inside the module but outside the subroutine by putting it in the declaration area and making it public. What you have done if you did that is made your code non-reentrant.

 

[WindSailor]

This is going to be Apples and Oranges.
Can you force it to go to Row1?
Besides having a type mismatch or coding issues, maybe it is running at EOF?
Using a DataGrid in .Net it was always best to force it to Row1 to work correctly.
Just wondering if this was carried over?
Not sure how to do it here, just adding additional info or comment.
Some more testing should narrow down the error.