Mysterious variable value reset

[dalebeitz]

Greetings!

I'm working on a salemsan activity tracking database. The users want to group all activity they enter during a single editing session into a batch. To do that, I added code to my form's ON-OPEN event that looks up the largest existing batch number and adds one to it, as follows:

Dim myapp As Object
Dim myRS As Object
Dim mySQL As String

mySQL = "select max(batchnumber) as batchnumber from tblDSMActMaster"
Set myRS = CreateObject("ADODB.Recordset&quot
Set myapp = Application.CurrentProject.Connection
myRS.Open mySQL, myapp, 1
If IsNull(myRS![BatchNumber]) Then
DSMBatch = 1
Else
DSMBatch = myRS![BatchNumber] + 1
End If
myRS.Close

DSMBATCH is declared as a PUBLIC integer variable at the top of this module. Whenever a new record is entered I want to assign the batch number to the table column, but if a pre-existing record is being edited, I don't want to re-assign the batch number. So in the forms BEFORE-UPDATE event I put the following code:

If IsEmpty(Me.BatchNumber) Or IsNull(Me.BatchNumber) Or Me.BatchNumber = 0 Then
Me.BatchNumber = DSMBatch
End If

Perhaps the combination of empty, null, and equal zero is overkill, but I'm trying to make sure that I always assign the new batch number on a new record.
Most of the time, this works perfectly. However, occasionally the batch number column in the table will end up being zero for some part of a batch. Usually the user will start entering a batch and some of the records will go in with the correct batch number, but then the remainder of the batch will be all zero batch numbers. They generally don't notice this until they've entered a significant amount of data, then I have to go into the table and figure out what the correct batch numbers are and patch them in. During my debugging attempts, I've discovered that somehow the value stored in the variable DSMBatch is being reset to zero, which then results in all the records going into the table as batch zero. I don't have any other code that's resetting this batch number.

So far I haven't discovered any particular thing that triggers the batch variable being reset. It appears to occur entirely at random, but there may well be something that triggers it that I haven't recognized yet. Anybody got any ideas what might be causing this? Does the fact that DSMBatch is a public variable have anything to do with it?

Any ideas greatly appreciated,
Dale Beitz
dale.beitz@agreliantgenetics.com

 

[MichaelRed]

My "Guess" is that this is a monolithic multiuser db. If so, the BatchId is probably being reset by another user.

The soloution here is probably to create an additional (and possibly external) table which holds the ?salesmaen's? Id's and their BatchIds and retrieve the BatchID for the individual salesman from the table. Of course you still need to seperate the indvidual salesmen sessions, so htat the retrievial of "John's" BatchId doesn't over-write "Jane's" BatchId, but that's a small matter ...


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dalebeitz]

Thanks for the reply!
I thought each user would have their own instance of the DSMBatch variable, but maybe I'm wrong. Here's the situation. The database lives on a network file server, there's only one copy of the MDB file. Each user has a copy of Access installed on their PC, and they use that copy of Access to open the database. So the MDB file is a shared resource, but Access is running independently in the memory space of each PC. So there shouldn't be any way for two users to interfere with each other's memory space, thus no way for anybody to overwrite anybody else's batch number. And even if it was happening, you'd expect that whatever batch number the "other" user was working with would be the value assigned to the variable, not zero.
Plus, the app only has two users, and I've seen this happen when only one of the users was physically in the office. So it's definitely ocurring when only one user is in the app.
To use a table-based solution as suggested above, I'd have to create a USER table that carried a record for each user. Whenever a user opens the entry form, I'd still need to read the maximum batch number from the salesman activity table and add one to it, then store that result in the user's record of the USER table, then read it from the user table each time they saved a record to ensure that I was always using the same batch number. If I didn't re-read it every time I'd have to store it in a variable, and that variable could be overwritten by whatever's causing the problem in the first place. I'll consider giving the user table idea a try if I can't figure anything else out, but I shouldn't have to do that. Sticking the value in a variable should accomplish the same thing, but something's going wrong and causing the variable to be reset apparently at random.
Thanks again!
Dale