VBA code recalcuating on window resize?

  • Thread starter Thread starter ElPresidente
  • Start date Start date
E

ElPresidente

I need to export some data from Access however one of the columns
needs to be a sequential ID. The underlying table does not have any
numeric fields which I can use to create this numbered colum using the
sub-query trick.

Instead, I tried this. In a module I wrote the following code:

Public Function currentLine(test As String) As Integer
counterVB = counterVB + 1
currentLine = counterVB
End Function

In my query, I wrote one of the columns as

LINE_NUMBER: currentLine([InvoiceNumber])

If you're wondering why I'm passing a variable that's not being used
it's because this forces the function to be run for each row.
Otherwise, the entire column contained the same number.

The strange thing is that every time I move the window, resize the
window, or scroll, the entire LINE_NUMBER column recalculates from top
to bottom. So I still have a sequential recordset however the numbers
quicky balloon into the thousands with enough movements.

How can I stop this recalcuation?
 
On Mon, 4 May 2009 11:01:38 -0700 (PDT), ElPresidente

That is difficult to do. In a report you have a different option: set
a control to "=1" and turn on Running Sum.
If you must use a form, I might want to pre-populate a table, and show
that data (read-only).

One thought: experiment with a form-level flag that is false by
default (as all booleans are) and you set it to True in Form_Load.
Then in your function you write:
If myFlag = False
'Increment logic goes here.
End If

-Tom.
Microsoft Access MVP
 
ElPresidente said:
I need to export some data from Access however one of the columns
needs to be a sequential ID. The underlying table does not have any
numeric fields which I can use to create this numbered colum using the
sub-query trick.

Instead, I tried this. In a module I wrote the following code:

Public Function currentLine(test As String) As Integer
counterVB = counterVB + 1
currentLine = counterVB
End Function

In my query, I wrote one of the columns as

LINE_NUMBER: currentLine([InvoiceNumber])

If you're wondering why I'm passing a variable that's not being used
it's because this forces the function to be run for each row.
Otherwise, the entire column contained the same number.

The strange thing is that every time I move the window, resize the
window, or scroll, the entire LINE_NUMBER column recalculates from top
to bottom. So I still have a sequential recordset however the numbers
quicky balloon into the thousands with enough movements.

How can I stop this recalcuation?
Click to expand...


Try specifying the DISTINCT keyword in your query's SQL (or use its property
sheet to set the Unique Values property to Yes).

Normally, Access tries to improve query performance by only calling a VBA
function when its result will be displayed or output, so moving a window or
scrolling back and forth causes the function to be called over and over.
But if you specify the DISTINCT keyword in the query, each row of the query
must be calcuated and stored, so that Access can compare each row to the
others and enforce uniqueness. Therefore, in such a case the VBA function
should be called only once for each row.
 
Back
Top