Variables Problem

[baronvont]

I am trying to use public variable in a database. When I open the main form, a sub calls the Init routine which creates a bunch of public variables. all & well. But as soon as I go to the last record in the database via the form, all my vars are reset. What could cause this?

I've tried creating a new copy by moving all objects over to a blank database but it's still the same.

Appreciate any input as it's driving me mad!

 

[Chance1234]

cvan you post your code ?

 

[baronvont]

I call Init from a form OnLoad event which sets the public vars at the start of a session. the strange thing is that now it seems to be happening intermitantly - it'll be fine for a while, them for no apparent reasn, all the variables will be reset. I will next try re-installing office97 to see it it helps.

.....(i've deleted some stuff so only the pertinant bit are here!)

Option Compare Database
Option Explicit

Public pvarGCGInitials As String
Public pvarGCGName As String
Public pvarGCSRVersion As String
Public MailFooter As String
Public pvarWebDBRecipient As String
Public pvarMyComputerName As String
Public pvarCurrentVersion As String
Public pvarDBVersion As String
Public pvarWebdatabaseSent As Boolean
Public mytestvar As String
Public pvarLastUpdate As String

Public Sub Init() 'Called from the main form OnLoad event

Dim db As Database
Dim rst As Recordset
Dim sqlstr As String

mytestvar = "YesSiree!!"
'Set the public variables
pvarMyComputerName = fGetComputerName()
pvarWebdatabaseSent = True

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM GCContacts&quot
sqlstr = "[PC_Number] = '" & pvarMyComputerName & "'"

'Match the pc with the users details

With rst
.MoveLast
.FindFirst sqlstr
If .NoMatch Then
pvarGCGInitials = "XX"
Else
pvarGCGInitials = !Initials
pvarGCGName = !GCContact
End If
End With

Application.MenuBar = "GCSR"

'Check to see if the version is the latest

Set rst = db.OpenRecordset("SELECT * FROM CurrentVersion&quot

'Check to see if we are running the same version as the 'live' version (we run frontend locally)
pvarCurrentVersion = Format(FileDateTime(the database server path here), "dd/mm/yy&quot
pvarDBVersion = rst!Date
If pvarCurrentVersion > pvarDBVersion Then
MsgBox "A new version on the GCG Database is available for download." & vbCrLf & _
"Instructions" ...
End If

pvarGCSRVersion = pvarDBVersion

'Turn off the stupid menubar animations
With CommandBars
.MenuAnimationStyle = msoMenuAnimationNone
End With

Set rst = Nothing

'Get the E-Mail footer info
Set rst = db.OpenRecordset("SELECT * FROM footertext&quot
rst.MoveFirst
MailFooter = rst!FooterText

End Sub

 

[Chance1234]

baffling


ok here are a few thigns that might help, firstly where you are declaring your public variables dont assign a data type

so
Public pvarGCGInitials
etc etc

run it through and see if you have any problems there, it might be a data type conversion that are causing them to blank thats one possibility

Add in error traping on all the modules and all the functions you are calling, also check your functions arent return anything rogue

check the table teh data is in a nd look for anything rogue ?

check to see no where else is calling the sub


also your search thing looks a bit iffy how about changing it the strsql and the openrecordset bit to this


strsql = "SELECT * FROM Currentversion WHERE" [PC_Number] = ' " & pvarMyComputerName & ";"
set rst = db.openrecordset(Strsql)


break the sub down for each rst change into seperate subs and call them, this might narrow down where the problem is coming from

 

[jebry]

Hi!

One more thing that needs to asked although it is very basic. If you lead the users through a series of forms, do you close and open forms as you go, i.e. is the main form being reopened on a regular basis?

Jeff Bridgham

 

[baronvont]

Also, why won't this work.. I am trying to update a field to store details after a record has been updated

Private Sub Form_AfterUpdate()

Dim db As Database
Dim rst As Recordset
Dim strSql As String

Me.Refresh
Me.AllowAdditions = False
Update_Status

Set db = CurrentDb
strSql = "SELECT RefNumber,LastUpdater FROM [GCSR Register] WHERE RefNumber = " & Me.RefNumber
Set rst = db.OpenRecordset(strSql)
rst.MoveLast
rst.Edit
rst!LastUpdater = pvarGCGInitials & " on " & Format(Now(), "dd/mm/yy hh:mm&quot

Set rst = Nothing
Set db = Nothing

End Sub

 

[baronvont]

Chance1234

Thanks, i'll answer yr questions...

ok here are a few thigns that might help, firstly where you are declaring your public variables dont assign a data type

so
Public pvarGCGInitials
etc etc

**** Ok I'll try that


Add in error traping on all the modules and all the functions you are calling, also check your functions arent return anything rogue

**** Been thru it many times and did spot a couple of errors which I corrected

check the table teh data is in a nd look for anything rogue ?
***** Will do

check to see no where else is calling the sub
**** Done - nothing there

also your search thing looks a bit iffy how about changing it the strsql and the openrecordset bit to this


strsql = "SELECT * FROM Currentversion WHERE" [PC_Number] = ' " & pvarMyComputerName & ";"
set rst = db.openrecordset(Strsql)


break the sub down for each rst change into seperate subs and call them, this might narrow down where the problem is coming from
****Currentversion shud be GCContacts!!

 

[baronvont]

Jebry,

The main form stays open all the time, thos it does call various other forms which are subsequently closed...

 

[Chance1234]

you need to add rst.update

 

[baronvont]

Jebry,

The main form stays open all the time, thos it does call various other forms which are subsequently closed...

 

[baronvont]

you need to add rst.update - thanx!!! forgot that bit.