Hi All,
I'm hoping someone out there can give me a hand with this problem I've got. I have an Access DB that people connect to using Access RT 2000. There are some machines that can connect through to the DB but most cannot and I can't work it out. The error people receive when trying to log onto the DB is 'Object variable or With block variable not set'.
I don't understand how some machines will connect but most won't.
I didn't create the DB, it's been around for ages and no one here knows how it works properly and I'm a complete novice with this. I had a search and saw lots of the same error but nothing that I felt comfortable with.
People get the error even with the full version of Access 2000 and XP and also RT 2003.
Any help would be greatly appreciated
Thanks in advance
5 5677 NeoPa 32,556
Expert Mod 16PB
Unfortunately, that error message just means you're trying to refer to an object variable that is unset.
Practically, it could mean anything.
We really have nothing to work on I'm afraid.
Thanks for the reply NeoPa, very quick
Would it help if I posted the code? I'll put it in anyway in case it will - Private Sub btnOK_Click()
-
Dim FldDef As Field, IndexDef As Index
-
Dim holdtime, i As Integer
-
Dim x
-
-
On Error GoTo btnOK_Click_Err
-
DoCmd.Hourglass True
-
-
'*** beep and exit if txtUserID and txtUserPassword are empty
-
If txtUserID & "" = "" Or txtUserPassword & "" = "" Then
-
Beep
-
GoTo Exit_btnOK_Click
-
End If
-
-
-
'*** check user table for Access level and Owner ID of current user
-
txtAccessLevel = DLookup("AccessLevel", "tblUsers", "[UserID] = forms![frmLogon]![txtUserID] and [UserPassword] = forms![frmLogon]![txtUserPassword]")
-
txtOwnerID = DLookup("OwnerID", "tblUsers", "[UserID] = forms![frmLogon]![txtUserID] and [UserPassword] = forms![frmLogon]![txtUserPassword]")
-
-
'*** if user ID or Pass word is invalid, display message
-
If (IsNull(txtAccessLevel)) Then '*** User group does not exist
-
MsgBox "Invalid Signon Attempt, check your user name and password.", 16, Me.Caption
-
Else
-
'*** AH set the current section name to
-
' gOwnerName = DLookup("Owner", "tlkpValidOwners", "OwnerID = forms![frmLogon]![txtOwnerID]")
-
If (IsNull(gOwnerName = DLookup("Owner", "tlkpValidOwners", "OwnerID = " & txtOwnerID))) And (txtOwnerID = 255) Then
-
gOwnerName = "Admin"
-
Else
-
gOwnerName = DLookup("Owner", "tlkpValidOwners", "OwnerID = " & txtOwnerID)
-
End If
-
gFormRecordSource = ""
-
Me.Visible = False '*** Hide the logon form but not close it, because it contain some values used by other parts of the system
-
-
'*** Set global variables
-
gCRLF = Chr$(13) & Chr$(10)
-
Set gThisDB = CurrentDb()
-
-
On Error Resume Next '*** in case of error like path not exist for Dir() and path already exist for Mkdir
-
If Dir(conLOCALDB_NAME) = "" Then '*** create a temporary database on local drive if not already exist
-
MkDir conLOCALDB_PATH
-
Set gLocalDB = DBEngine.Workspaces(0).CreateDatabase(conLOCALDB_NAME, DB_LANG_GENERAL)
-
Else
-
Set gLocalDB = DBEngine.Workspaces(0).OpenDatabase(conLOCALDB_NAME)
-
End If
-
On Error GoTo btnOK_Click_Err
-
-
'*** create a temporary table(attached) and a qry in this database for current user to hold selected records
-
txtLogonTime = Now
-
gFilterQueryName = "qry" & Format(Now, "ddhhnnss")
-
gFilterTableName = "tbl" & Format(Now, "ddhhnnss")
-
Set TempQry = gThisDB.CreateQueryDef(gFilterQueryName, "SELECT DISTINCTROW tblClients.* FROM " & gFilterTableName & " INNER JOIN tblClients ON " & gFilterTableName & ".ClientID = tblClients.ClientId;")
-
Set AttachedTbl = gThisDB.CreateTableDef(gFilterTableName)
-
AttachedTbl.Connect = ";DATABASE=" & conLOCALDB_NAME
-
AttachedTbl.SourceTableName = conLOCALTBL_NAME
-
-
On Error Resume Next
-
gThisDB.TableDefs.Append AttachedTbl
-
If Err > 0 Then
-
On Error GoTo btnOK_Click_Err
-
Set LocalTbl = gLocalDB.CreateTableDef(conLOCALTBL_NAME)
-
'*** Create a field call ClientID
-
Set FldDef = LocalTbl.CreateField("ClientID", DB_LONG)
-
LocalTbl.Fields.Append FldDef
-
'*** Save TableDef definition by appending it to TableDefs collection.
-
gLocalDB.TableDefs.Append LocalTbl
-
'*** Create index field in the temporary table in work database
-
Set IndexDef = LocalTbl.CreateIndex("ClientID")
-
Set FldDef = IndexDef.CreateField("ClientID")
-
IndexDef.Primary = True
-
IndexDef.Required = True
-
IndexDef.Fields.Append FldDef
-
'*** Save Index definition by appending it to Indexes collection.
-
LocalTbl.Indexes.Append IndexDef
-
gThisDB.TableDefs.Append AttachedTbl
-
End If
-
On Error GoTo btnOK_Click_Err
-
-
'***DoCmd RunSQL "INSERT INTO tblLog ( UserName, LogonTime, LogoffTime ) VALUES (" & conDOUBLE_QUOTES & forms![frmLogon]![txtUserID] & conDOUBLE_QUOTES & "," & conDOUBLE_QUOTES & HoldTime & conDOUBLE_QUOTES & ", null)"
-
DoCmd.SetWarnings False
-
DoCmd.OpenQuery "qryWriteLogonLog"
-
DoCmd.SetWarnings True
-
-
DoCmd.Hourglass False
-
-
'This only applied for special logon for ******** to transfer records.
-
If Forms!frmLogon!txtAccessLevel = 8 Then '*** DOC SUPP user
-
'** x = TransferDocSuppText("qryDocSuppSingleLabel", "C:\personal\address\docsu.txt")
-
'** x = TransferDocSuppText("qryDocSuppSingleLabel", "C:\docs\docsu.txt")
-
'** x = TransferDocSuppText("C:\docs\")
-
x = TransferDocSuppText("qryDocSuppSingleLabel", "G:\Div3\Interlen\Docss\temp\docsu.txt")
-
'** x = TransferDocSuppText("qryDocSuppSingleLabel", "\\Docslabels\labels$\docsu.txt")
-
End If
-
-
'*** Open the Mainmenu
-
-
DoCmd.OpenForm conMENUFORM
-
DoCmd.Maximize
-
-
End If
-
-
Exit_btnOK_Click:
-
DoCmd.Hourglass False
-
Exit Sub
-
-
btnOK_Click_Err:
-
MsgBox Error$, , Me.Caption
-
Resume Exit_btnOK_Click
-
End Sub
Thanks again
Thanks for the reply NeoPa, very quick
Would it help if I posted the code? I'll put it in anyway in case it will - Private Sub btnOK_Click()
-
Dim FldDef As Field, IndexDef As Index
-
Dim holdtime, i As Integer
-
Dim x
-
-
On Error GoTo btnOK_Click_Err
-
DoCmd.Hourglass True
-
-
'*** beep and exit if txtUserID and txtUserPassword are empty
-
If txtUserID & "" = "" Or txtUserPassword & "" = "" Then
-
Beep
-
GoTo Exit_btnOK_Click
-
End If
-
-
-
'*** check user table for Access level and Owner ID of current user
-
txtAccessLevel = DLookup("AccessLevel", "tblUsers", "[UserID] = forms![frmLogon]![txtUserID] and [UserPassword] = forms![frmLogon]![txtUserPassword]")
-
txtOwnerID = DLookup("OwnerID", "tblUsers", "[UserID] = forms![frmLogon]![txtUserID] and [UserPassword] = forms![frmLogon]![txtUserPassword]")
-
-
'*** if user ID or Pass word is invalid, display message
-
If (IsNull(txtAccessLevel)) Then '*** User group does not exist
-
MsgBox "Invalid Signon Attempt, check your user name and password.", 16, Me.Caption
-
Else
-
'*** AH set the current section name to
-
' gOwnerName = DLookup("Owner", "tlkpValidOwners", "OwnerID = forms![frmLogon]![txtOwnerID]")
-
If (IsNull(gOwnerName = DLookup("Owner", "tlkpValidOwners", "OwnerID = " & txtOwnerID))) And (txtOwnerID = 255) Then
-
gOwnerName = "Admin"
-
Else
-
gOwnerName = DLookup("Owner", "tlkpValidOwners", "OwnerID = " & txtOwnerID)
-
End If
-
gFormRecordSource = ""
-
Me.Visible = False '*** Hide the logon form but not close it, because it contain some values used by other parts of the system
-
-
'*** Set global variables
-
gCRLF = Chr$(13) & Chr$(10)
-
Set gThisDB = CurrentDb()
-
-
On Error Resume Next '*** in case of error like path not exist for Dir() and path already exist for Mkdir
-
If Dir(conLOCALDB_NAME) = "" Then '*** create a temporary database on local drive if not already exist
-
MkDir conLOCALDB_PATH
-
Set gLocalDB = DBEngine.Workspaces(0).CreateDatabase(conLOCALDB_NAME, DB_LANG_GENERAL)
-
Else
-
Set gLocalDB = DBEngine.Workspaces(0).OpenDatabase(conLOCALDB_NAME)
-
End If
-
On Error GoTo btnOK_Click_Err
-
-
'*** create a temporary table(attached) and a qry in this database for current user to hold selected records
-
txtLogonTime = Now
-
gFilterQueryName = "qry" & Format(Now, "ddhhnnss")
-
gFilterTableName = "tbl" & Format(Now, "ddhhnnss")
-
Set TempQry = gThisDB.CreateQueryDef(gFilterQueryName, "SELECT DISTINCTROW tblClients.* FROM " & gFilterTableName & " INNER JOIN tblClients ON " & gFilterTableName & ".ClientID = tblClients.ClientId;")
-
Set AttachedTbl = gThisDB.CreateTableDef(gFilterTableName)
-
AttachedTbl.Connect = ";DATABASE=" & conLOCALDB_NAME
-
AttachedTbl.SourceTableName = conLOCALTBL_NAME
-
-
On Error Resume Next
-
gThisDB.TableDefs.Append AttachedTbl
-
If Err > 0 Then
-
On Error GoTo btnOK_Click_Err
-
Set LocalTbl = gLocalDB.CreateTableDef(conLOCALTBL_NAME)
-
'*** Create a field call ClientID
-
Set FldDef = LocalTbl.CreateField("ClientID", DB_LONG)
-
LocalTbl.Fields.Append FldDef
-
'*** Save TableDef definition by appending it to TableDefs collection.
-
gLocalDB.TableDefs.Append LocalTbl
-
'*** Create index field in the temporary table in work database
-
Set IndexDef = LocalTbl.CreateIndex("ClientID")
-
Set FldDef = IndexDef.CreateField("ClientID")
-
IndexDef.Primary = True
-
IndexDef.Required = True
-
IndexDef.Fields.Append FldDef
-
'*** Save Index definition by appending it to Indexes collection.
-
LocalTbl.Indexes.Append IndexDef
-
gThisDB.TableDefs.Append AttachedTbl
-
End If
-
On Error GoTo btnOK_Click_Err
-
-
'***DoCmd RunSQL "INSERT INTO tblLog ( UserName, LogonTime, LogoffTime ) VALUES (" & conDOUBLE_QUOTES & forms![frmLogon]![txtUserID] & conDOUBLE_QUOTES & "," & conDOUBLE_QUOTES & HoldTime & conDOUBLE_QUOTES & ", null)"
-
DoCmd.SetWarnings False
-
DoCmd.OpenQuery "qryWriteLogonLog"
-
DoCmd.SetWarnings True
-
-
DoCmd.Hourglass False
-
-
'This only applied for special logon for ******** to transfer records.
-
If Forms!frmLogon!txtAccessLevel = 8 Then '*** DOC SUPP user
-
'** x = TransferDocSuppText("qryDocSuppSingleLabel", "C:\personal\address\docsu.txt")
-
'** x = TransferDocSuppText("qryDocSuppSingleLabel", "C:\docs\docsu.txt")
-
'** x = TransferDocSuppText("C:\docs\")
-
x = TransferDocSuppText("qryDocSuppSingleLabel", "G:\Div3\Interlen\Docss\temp\docsu.txt")
-
'** x = TransferDocSuppText("qryDocSuppSingleLabel", "\\Docslabels\labels$\docsu.txt")
-
End If
-
-
'*** Open the Mainmenu
-
-
DoCmd.OpenForm conMENUFORM
-
DoCmd.Maximize
-
-
End If
-
-
Exit_btnOK_Click:
-
DoCmd.Hourglass False
-
Exit Sub
-
-
btnOK_Click_Err:
-
MsgBox Error$, , Me.Caption
-
Resume Exit_btnOK_Click
-
End Sub
Thanks again
__1 gThisDB and gLocalDB are Global Object Variables. Are they Declared as 'Public' in a Standard Code Module?
__2 By the code's naming convention, TempQry, AttachedTable, and LocalTbl are not Global Object Variables (not prefaced with g) but I do not see them dimensioned within the Procedure. Are they dimensioned within the Form's Class Module?
__3 If the answers to the previous 2 questions are Yes, the best option for you would be to enter a Breakpoint at the 1st executable line of code within the Procedure, Single Step through the code, and pinpoint the exact line with the Error is occurring.
I'm not sure how to check the first two points but we put message boxes through the script to see where it got it and it fails here Set LocalTbl = gLocalDB.CreateTableDef(conLOCALTBL_NAME)
Solved it - thanks for your help.
The problem was that it was trying to write to a temporary space on the D:\. We never used to have the DB do that but someone changed it. The problem was happening because sometimes the CD drive took the D:\ or the D:\ partition wouldn't be formatted.
Thanks again
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ike |
last post by:
Anyone knows what cuases this under 6.0 ? -Ike
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB...
|
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much...
|
by: majo |
last post by:
Hi,
I had posted this once earlier, but couldnt get my problem solved.
When i run an asp.net application in windows 2003 server with IIS 6.0, it
"SOMETIMES" gives me the below given error....
|
by: axs221 |
last post by:
I am trying to move some of our large VBA Access front-end file into
ActiveX DLL files. I created two DLL files so far, one was a module
that contains code to integrate into the QuickBooks...
|
by: etuncer |
last post by:
Hello All,
I have Access 2003, and am trying to build a database for my small
company. I want to be able to create a word document based on the data
entered through a form. the real question is...
|
by: Newbie19 |
last post by:
I'm trying to get a list of all subfolders in a folder on a share drive, but I keep on getting this error message:
Object variable or With block variable not set.
Description: An unhandled...
|
by: akaess |
last post by:
I am trying to merge information from the the database into word documents using the following methods but it keeps giving the error 91: Object Variable or With Block Variable not set. could anybody...
|
by: akaess |
last post by:
Hey everybody I am currently trying to merger data from a form in MS access but it keeps giving me the error 91: Object variable or with block variable not set. could somebody please look at the code...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |