locked
VBA Error 3464 RRS feed

  • Question

  • The below code runs in AutoCAD and checks a database for information. I had to change one of the fields  in the database (Jobnumber) to a numeric field. It had been a txt field. Now I am not able to get the Description and name out of the Database. Any Help would be great. I ran debug and this is where I get the error. The set tblrecord runs then jumps to the error. The field in the table is a numeric field.  Thanks

    Public Sub GetProjNameAndDesc()            'Grab the project name and description
      Dim ObjDB As DAO.Database                'Database object
      Dim tblRecord As DAO.Recordset           'Databse record
      Dim SQLString As String                  'SQL statement string
      Dim strMarkValue As String
      Dim strjobnumber As Integer
           
      On Error GoTo DBErrHandler2
     
      Set ObjDB = DAO.openDatabase(strDBaseLoc)      'Get database located at global variable: strDbaseLoc (see module 1 for value)
     
      If Err = 0 Then                                 'If no errors, procede
          SQLString = "select JName, JDescription from jobname where jobnumber = '" & JobNumber & "' order by jName"
          Set tblRecord = ObjDB.OpenRecordset(SQLString)  'Use above sql statement to get job name and description from the Microsoft Access Query called jobname
        If Not tblRecord.EOF Then                         'if not at the end of the query
          tblRecord.MoveFirst                              'look at the first record
          frmGatherInfo.txtJobDesc.Value = tblRecord!JDescription       'Put the job description in front of the user in the dialog
          frmGatherInfo.txtProjectName = tblRecord!JName                'Do the same with job name
    '      frmGatherInfo.txtJobDesc.Locked = True                       Description is to be now unlocked to allow user to change it
          frmGatherInfo.txtProjectName.Locked = True                    'Do not allow user to change job name

        Else  'Show error message if none found
          MsgBox ("No Job Name or Description Found in Jobs Table" _
          & vbCr & "If you are printing from the ManageDB command, there will be no Description or Name." _
          & vbCr & "If you are printing from a selection, you will be allowed to enter the values, but they will not be saved in the Job Table.")
          frmGatherInfo.txtJobDesc.Locked = False        'make sure field is not locked
          frmGatherInfo.txtProjectName.Locked = False     'make sure field is not locked
        End If
      Else
        MsgBox ("Could not open database")
       
      End If


    'Error Handler for database connectivity-----------------------------------------
    Exit_Subroutine2:
       Exit Sub

    DBErrHandler2:
       Dim errX As DAO.Error
       DBErrorspresent = True


    Rick

    Wednesday, November 19, 2014 2:34 AM

Answers

  • Hi RickTexas,
     
    Simple, change your SQL string by removing the apostrophes:
     
    SQLString = "select JName, JDescription from jobname where jobnumber = " _
                & JobNumber & " order by jName"
     
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    • Proposed as answer by danishani Wednesday, November 19, 2014 6:40 AM
    • Marked as answer by danishani Thursday, November 27, 2014 12:18 AM
    Wednesday, November 19, 2014 5:21 AM

All replies

  • Hi RickTexas,
     
    Simple, change your SQL string by removing the apostrophes:
     
    SQLString = "select JName, JDescription from jobname where jobnumber = " _
                & JobNumber & " order by jName"
     
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    • Proposed as answer by danishani Wednesday, November 19, 2014 6:40 AM
    • Marked as answer by danishani Thursday, November 27, 2014 12:18 AM
    Wednesday, November 19, 2014 5:21 AM
  • That worked Thanks

    Rick

    Thursday, November 27, 2014 7:11 PM