Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006

    Unanswered: Update Records from Subform to different Database..with same subform


    Much like my last question/problem( I'm trying to take a set of hours, that's located on the form I'm using, and updating them to a seperate database that contains the same information on it. Now the hours are located on a subform...

    I'm using an INSERT INTO and it works fine...goes through my whole code fine, but when it actually needs to append the new data, it will not append it due to the fact of a type conversion error. Now this should work as it's exactly the same code as I used for a previous button as well..just changed it to pass the horus information. I'm not trying to pass the primary key which is a autonumber..I'm passing everything else.. here's the code used for this button.

    Private Sub Command94_Click()
    Dim rs2 As DAO.Recordset
    Dim sqlSI2 As String
    Dim sign2 As Variant
    Dim updateHours As DAO.Recordset
    Dim updateSQL2 As String
    Dim qryData2 As Variant

    sqlSI2 = "SELECT * FROM [SERVICE HOURS2] WHERE [SERVICE HOURS2].[ServiceRecordID] =" & Me.[ServiceRecordID] & ";"

    Set rs2 = CurrentDb.OpenRecordset(sqlSI2)
    Set sign2 = rs2
    MsgBox rs2.RecordCount

    If rs2.RecordCount = 0 Then

    updateSQL2 = "INSERT INTO [Service Hours2] (ServiceRecordID, EmployeeID, [Date], [Travel Start], [Plant In], [Plant Out], [Travel End] )" & _
    "SELECT [Service Hours].ServiceRecordID, [Service Hours].EmployeeID, [Service Hours].[Date], [Service Hours].[Travel Start], [Service Hours].[Plant In], [Service Hours].[Plant Out],[Service Hours].[Travel End]" & _
    "FROM [Service Hours] WHERE((([Service Hours].ServiceRecordID)=" & Me.ServiceRecordID & "));"

    DoCmd.RunSQL updateSQL2
    Set qryData2 = updateHours
    MsgBox "Hours already inserted. Please use the f:\common\databases\megabase"
    End If
    End Sub

    As I said everything works fine just getting a type conversion error..

    ServiceRecordID is a number, EmployeeID, is a Number, Date/Travel Start&End/Plant In&Out are all Date/Time fields...

    the only record I am not passing which is the primary key is ServiceTimeID which is an auto number..

    if ya need a sample DB let me know and I'll toss one up...

    Thanks guys
    Last edited by Smythe1000; 05-24-06 at 14:48.

  2. #2
    Join Date
    Aug 2003
    Cleveland USA


    Have you tried writing an append query that does the same thing? If you can get the append query to work, then there's probably something wrong with your code. If the append query will not work, there's something going on with the data. Make sure each field of the two tables has exactly the same data type (both Long Integer, not just number type), and same length for text fields.

    One thing I noticed is that your textbox on your form has the same name as your field name (ServiceRecordID). Even though that's the default name when you create a form, VBA gets confused sometimes. I usually rename the textboxes something like intServiceRecordID for integers, txtField for text fields, etc. You only need to rename the textboxes that are used in code.

  3. #3
    Join Date
    Mar 2006
    everything is correct for information wise...but even creating an append query I'm getting the same errors..and thats without having it on the form..

  4. #4
    Join Date
    Mar 2006
    Yippie..I now get a run-time 3134 error...

    "Syntax error in INSERT INTO statement"

Posting Permissions

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