Help Please...Code does not work why? 
Author Message
 Help Please...Code does not work why?

Hi all,

I have two different versions of an access application. One which uses
the standard Access mdb as a back end and one that uses SQL server as the
back end. With minor exceptions, the two are identical.

I created a routine that imports data from text files and stores it in
the db. This works in the SQL version of the program. However, when I
copied the code and pasted it in the access version I get an error 91
"Object variable or With block variable not set". For the life of me, I
can't figure out why. perhaps some one up here can help me figure it out.
Here is the sub in all it's  glory.

Function LoadToTemp() As Integer

  '=========================================================
  ' Comments   : Loads the data in the text file into the correct
  '              Temporary table.
  ' Parameters :  -
  ' Returns    : Integer -
  ' Created    :
  ' Modified   :
  '
  ' =========================================================

  Dim strFile As String
  Dim strFullFileName As String
  Dim strFullTableName As String
  Dim strWhereFile As String
  Dim strLinkFile As String
  Dim tblToCheck As Recordset
  Dim fldSidetrack As Field
  Dim tblName As String

  On Error GoTo LoadToTemp_Err
  LoadToTemp = True
  DoCmd.SetWarnings False
  fbSmartReporter16 = False

  strFile = Dir(gvarGstrTransferPath & "TEMP\*.EXP")
  Do Until Len(strFile) = 0
    strWhereFile = "[FileName] = '" & Left(strFile, Len(strFile) - 4) &
"'"
    strFullTableName = "Temp" & DLookup("TableName", "RIMBASETables",
strWhereFile)
    strFullFileName = gvarGstrTransferPath & "TEMP\" & strFile

    'If  data is from 16-bit SmartReporter Call the mapping routine [CMB]
    ' Attach text file
    strLinkFile = "txt" & Left(strFile, Len(strFile) - 4)

    DoCmd.TransferText acLinkDelim, "16bitSmartReporter", strLinkFile,
strFullFileName, True

    Set tblToCheck = gdbsRIMBase.OpenRecordset(strLinkFile,
dbOpenSnapshot)
    fldSidetrack = tblToCheck.Fields("SidetrackID")    ' !!!This is where
it fails and gives the error. The table does have the field BTW.

    If fbSmartReporter16 Then
        tblName = tblToCheck.Name
        ' Map the 16-bit wellID to the 32-bit wellID/Sidetrack Combo
        Select Case tblName

        Case "txtriginfo"
            DoEvents
        Case "txtexport"
            DoEvents
        Case Else
            gvarY = MapData(tblName)
            If gfbAddFlag = True Then
               DoCmd.OpenForm "WellInfoMenu"
               gvarY = False
               Exit Function
            End If
        End Select
        ' Call the import routine
        gvarY = TransferData(strFullTableName, strLinkFile)
    Else
        DoCmd.TransferText A_IMPORTDELIM, , strFullTableName,
strFullFileName, True

    End If
     strFile = Dir
  Loop

LoadToTemp_Exit:
  DoCmd.SetWarnings True
  DeleteTextTables
  gvarY = True
  Exit Function

LoadToTemp_Err:
   If Err = 3265 Then
        fbSmartReporter16 = True
        Err = 0
        Resume Next
    End If
  LoadToTemp = False
  If Not gintGfAutoImport Then MsgBox "Error: " + Error$ & Chr$(13) &
"Table: " & strFullTableName & Chr$(13) & "FileName: " & strFile, 0,
"LoadToTemp"
  gstrErrorFunction = "LoadToTemp"
  gstrErrorDesc = Error$ & "  File: " & strFile
  gvarX = WriteError()
  Resume LoadToTemp_Exit

End Function

TIA

Craig



Tue, 07 Nov 2000 03:00:00 GMT  
 Help Please...Code does not work why?


Quote:
>I created a routine that imports data from text files and stores it in
>the db. This works in the SQL version of the program. However, when I
>copied the code and pasted it in the access version I get an error 91
>"Object variable or With block variable not set". For the life of me, I
>can't figure out why. perhaps some one up here can help me figure it out.
>Here is the sub in all it's  glory.
>    Set tblToCheck = gdbsRIMBase.OpenRecordset(strLinkFile,
>dbOpenSnapshot)
>    fldSidetrack = tblToCheck.Fields("SidetrackID")    ' !!!This is where
>it fails and gives the error. The table does have the field BTW.

The line before your indicated error uses a global object variable to represent
the datbase "gdbsRIMBase."  If you have been doing a lot of debugging, it is
quite likely that you reset the assignment.  Consider setting a watch to ensure
the variable is assigned, and closing and opening your database.

Hope this helps,
Todd Lutton



Tue, 07 Nov 2000 03:00:00 GMT  
 Help Please...Code does not work why?

Craig,

I think fields are objects, therefore you have to use
Set fldSidetrack = tblToCheck.Fields("SidetrackID")

I always get that error when I leave out a Set where I need one.  Hope
this is true for you too.

Keri Hardwick

<snip>

Quote:
>   Dim fldSidetrack As Field
<snip>
>     Set tblToCheck = gdbsRIMBase.OpenRecordset(strLinkFile,
> dbOpenSnapshot)
>     fldSidetrack = tblToCheck.Fields("SidetrackID")    ' !!!This is where it fails and gives the error. The table does have the field BTW.



Tue, 07 Nov 2000 03:00:00 GMT  
 Help Please...Code does not work why?

Your last 'If' statement does not contain an 'End If', this will probably
come up after addressing the other problem. From what I observed i would try
using 'CurrentDb'. access needs to know what database it is using and is
does not assume the current open database.

Dim db as Database

Set db = CurrentDb()

fldSidetrack = db.tblToCheck.Fields("SidetrackID")



Tue, 07 Nov 2000 03:00:00 GMT  
 Help Please...Code does not work why?

Quote:

>     fldSidetrack = tblToCheck.Fields("SidetrackID")    ' !!!This is where
> it fails and gives the error. The table does have the field BTW.

Since you declared fldSidetrack as type Field, you need:

Set fldSidetrack = tblToCheck.Fields("SidetrackID")

but what are you doing with fldSidetrack?  This is the only place it
appears in the sub.

Also, if you just want the value of SidetrackID (and no other field
properties), you should declare fldSidetrack as the same type as
SidetrackID, then load it thus:

fldSidetrack = tblToCheck("SideTrackID")

And is tblToCheck a one record table?  In any case, I would issue
tblToCheck.MoveFirst before attempting to read any fields.

HTH

Regards,

S.Y.



Tue, 07 Nov 2000 03:00:00 GMT  
 Help Please...Code does not work why?


Quote:
> Craig,

> I think fields are objects, therefore you have to use
> Set fldSidetrack = tblToCheck.Fields("SidetrackID")

> I always get that error when I leave out a Set where I need one.  Hope
> this is true for you too.

> Keri Hardwick


> <snip>
> >   Dim fldSidetrack As Field
> <snip>
> >     Set tblToCheck = gdbsRIMBase.OpenRecordset(strLinkFile,
> > dbOpenSnapshot)
> >     fldSidetrack = tblToCheck.Fields("SidetrackID")    ' !!!This is where it fails and gives the error. The table does have the field BTW.

Thanks all,

It was the "SET" that was missing. :-) All is well now. FWIW, I'm using
this as part of an import routine. and if the field exists it goes one
route to importing, otherwise I have to set a couple global vars so the
routine knows where to map it to.

Again a major thanksto all who answered.



Sat, 11 Nov 2000 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. HELP PLEASE - Why does this script not work?

2. Why does the following code not work?

3. this code does not work, why?

4. Why code will not work?

5. Why does this piece of code work with Word 97 but not Word 2000

6. why will this code not work

7. Find/Replace code not working - why?

8. Why this code does not work:

9. why the @#$@% does this code not work

10. datagrid - why is not working again, please

11. Anyone know why this code only works in 32bit and not 16 bit vb 4

12. Why this code does not work properly?

 

 
Powered by phpBB® Forum Software