Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2006
    Posts
    73

    Exclamation Unanswered: Run-Time Error!! yippie!!!

    Alright..I'm getting a run-time error 3061 for the following chunk of code..

    Code:
    Private Sub Command93_Click()
       Dim rs As DAO.Recordset
       Dim sqlSI As String
       Dim Sign As Variant
       Dim updateRec As DAO.Recordset
       Dim updateSQL As String
       Dim qryData As Variant
          
    
       sqlSI = "SELECT ([SERVICE RECORDS2].[Service Record])FROM [SERVICE RECORDS2]WHERE [SERVICE RECORDS2].[Service Report #]=" & Me.[Service_Report__] & ";"
    
       Set rs = CurrentDb.OpenRecordset(sqlSI)
       Set Sign = rs
       MsgBox rs.RecordCount
       
       If rs.RecordCount = 0 Then
        'Set updateSQL = dbsCurrent.QueryDefs("QRYSErvice")
        updateSQL = "INSERT INTO [Service Records2] ( [Service Report #], ServiceDate, EmployeeID, Description, ContactID, ProjectID, [Dash #], [Service Type], [Test Speed], [Test containers], [Fit Test], [Fit Notes], [Tech Signature], [Customer Signature], [Work Requested], [Customer Comments], [Static Speed], [Jog Speed], [Line Speed], [N/A For Speed], [No Containers], [Mould or Test Container], Containers, [Full Production], [N/A For Service], [Checked Loose/Tight Cont], [Out Of Time Cond], [Checked Loose/Tight Cores] )" & _
        "SELECT [Service Records].[Service Report #], [Service Records].ServiceDate, [Service Records].EmployeeID, [Service Records].Description, [Service Records].ContactID, [Service Records].ProjectID, [Service Records].[Dash #], [Service Records].[Service Type], [Service Records].[Test Speed], [Service Records].[Test containers], [Service Records].[Fit Test], [Service Records].[Fit Notes], [Service Records].[Tech Signature], [Service Records].[Customer Signature], [Service Records].[Work Requested], [Service Records].[Customer Comments], [Service Records].[Static Speed], [Service Records].[Jog Speed], [Service Records].[Line Speed], [Service Records].[N/A For Speed], [Service Records].[No Containers], [Service Records].[Mould or Test Container], [Service Records].Containers, [Service Records].[Full Production], [Service Records].[N/A For Service], [Service Records].[Checked Loose/Tight Cont], [Service Records].[Out Of Time Cond], [Service Records].[Checked Loose/Tight Cores]" & _
        "FROM [Service Records]WHERE ((([Service Records].[Service Report #])=" & Me.[Service_Report__] & "));"
    
        DoCmd.RunSQL updateSQL
        'Set updateRec = CurrentDb.OpenRecordset(updateSQL)
        Set qryData = updateRec
       Else
        MsgBox "Record Already Exists.  Please Use the f:\commom\databases\megabase"
       End If
       'Forms![FormName]!SignIn = Sign
    End Sub
    And the line of code I keep getting highlighted is..

    Set rs = CurrentDb.OpenRecordset(sqlSI)

    Any reason you guys can see why I'd be getting this error??

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It does not appear that there is a space before FROM or WHERE. The spaces, symbols and multiple underscores in names are not a good idea, and will make debugging trickier, especially the multiple underscores.
    Paul

  3. #3
    Join Date
    Mar 2006
    Posts
    73
    the thing with the underscors is the actual field name...

    when I do Me. it comes up in the list...as for the symbols again..the field as well..

    What I'm trying to do is push the data from this database to another database that contains all the same information...

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you make sure there is a space before FROM and WHERE?
    Paul

  5. #5
    Join Date
    Mar 2006
    Posts
    73
    yup..no difference still gettin it..

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is "Service Report #" a numeric field in the table?
    Paul

  7. #7
    Join Date
    Mar 2006
    Posts
    73
    yup that it is...

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Add a debug.print line and post the value of sqlSI after it's created. Better yet, post a sample db.
    Paul

  9. #9
    Join Date
    Mar 2006
    Posts
    73
    I added in a msgbox(sqlSI) and I got the following on a return

    SELECT ([SERVICE RECORDS2].[Service Record])FROM [SERVICE RECORDS2]WHERE [SERVICE RECORDS2].[Service Report #]=888888;

    Edit: Sorry it's taken me a while to repost on this...was a holiday long weekend here..
    Last edited by Smythe1000; 05-23-06 at 10:05.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not seeing a space before FROM or WHERE, which you said you checked on. Are we having a miscommunication?
    Paul

  11. #11
    Join Date
    Mar 2006
    Posts
    73
    it didn't make any difference at all when I did it..gave me the same error if I had the space in or not..

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The spaces are absolutely necessary, or the SQL is invalid. Put them back in, run it and post both the revised SQL from the Debug.Print and the error. I take it you can't post a sample db?
    Paul

  13. #13
    Join Date
    Mar 2006
    Posts
    73
    I'm in the process of doin a sample one(well two since you need the second one)

    and as for the revised message...

    SELECT ([SERVICE RECORDS2].[Service Record]) FROM [SERVICE RECORDS2] WHERE [SERVICE RECORDS2].[Service Report #]=888888;

    and still same error...

  14. #14
    Join Date
    Mar 2006
    Posts
    73
    Alright I'm pretty sure this should do it for a sample...

    if it doesn't work let me know asap..tested it on my end and it works..

    just unzip both DBs to your desktop
    Attached Files Attached Files

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You'll probably kick yourself. "Service Record" is not a field in "SERVICE RECORDS2", so the SQL fails. Changing it to "SELECT * ..." seems to make it work.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •