Access Error: Object Variable or With block variable not set

[mekon]

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

[NeoPa]

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.

[mekon]

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

[B]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[/B]

Thanks again

[ADezii]

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

[B]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[/B]

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.

[mekon]

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.Create TableDef(conLOC ALTBL_NAME)

[mekon]

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