Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Help with insert subroutine

    I have a form that has an ADD button. When I click it it runs the sub shown here. For some reason that I cannot explain, it ends up adding two rows to my table. Can someone explain why this is happening? I've attached a screen print of the resulting rows that are created (the first 2 rows)

    Code:
    Private Sub cmdAdd_Click()
    On Error GoTo Err_cmdAdd_Click
    
        Call AddRecords
        
    Exit_cmdAdd_Click:
        Exit Sub
    
    Err_cmdAdd_Click:
        MsgBox Err.Description
        Resume Exit_cmdAdd_Click
        
    End Sub
    
    Private Sub AddRecords()
    
        Dim strSQL                      As String
        Dim frm                         As Form
    
        Dim LaneID                      As String
        Dim LocationID                  As String
        Dim Finaldest                   As String
        Dim carrierid                   As String
        Dim shipday                     As String
        Dim deliveryday                 As String
        Dim TIME_AT_LOCATION            As String
        Dim nostops                     As Integer
        Dim stopnum                     As Integer
        Dim CarrierNameOutOfXDock       As String
        Dim OutOfXDockShippingLaneID    As String
        Dim XDockID                     As String
        Dim NOTES                       As String
    
        Set frm = Forms!tblAddRouting
    
        LaneID = frm!UNIQUE_LANE_ID
        LocationID = frm!LOCATION_ID
        Finaldest = frm!FINAL_DEST
        carrierid = frm!CARRIER_ID
        shipday = UCase(frm!SHIP_DAY)
        deliveryday = UCase(frm!DELIVERY_DAY)
        TIME_AT_LOCATION = frm!TIME_AT_LOCATION
        CarrierNameOutOfXDock = frm!CARRIER_NAME_OUT_OF_X_DOCK
        OutOfXDockShippingLaneID = frm!OUT_OF_X_DOCK_SHIPPING_LANE_ID
        XDockID = frm!X_DOCK_ID
        NOTES = UCase(frm!NOTES)
        nostops = frm!NO_STOPS
        stopnum = frm!STOP_NUM
    
        strSQL = "INSERT INTO tblRouting(unique_lane_id, " & _
                                           " location_id, " & _
                                           " final_dest, " & _
                                           " carrier_id, " & _
                                           " ship_day, " & _
                                           " delivery_day, " & _
                                           " time_at_location, " & _
                                           " carrier_name_out_of_x_dock, " & _
                                           " out_of_x_dock_shipping_lane_id, " & _
                                           " x_dock_id, " & _
                                           " notes, " & _
                                           " no_stops, " & _
                                           " stop_num)" & _
                                " VALUES ('" & LaneID & "', " & _
                                        " '" & LocationID & "', " & _
                                        " '" & Finaldest & "', " & _
                                        " '" & carrierid & "', " & _
                                        " '" & shipday & "', " & _
                                        " '" & deliveryday & "', " & _
                                        " '" & TIME_AT_LOCATION & "', " & _
                                        " '" & CarrierNameOutOfXDock & "', " & _
                                        " '" & OutOfXDockShippingLaneID & "', " & _
                                        " '" & XDockID & "', " & _
                                        " '" & NOTES & "', " & _
                                        " " & nostops & ", " & stopnum & ");"
                                        
        DoCmd.RunSQL strSQL
        
        MsgBox ("A new Routing was added for: " & LaneID)
        
        DoCmd.Close acForm, "tblAddRouting"
        
    End Sub
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Why are there differences in the two records? The name of the week is different, and the ID is different. Can you explain these? Also, have you added more than one new record, to see if it always adds two? Are you sure the "other" or second record was not already in the table, just with a different ID number?

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    The ID is being assigned by Access. I also noticed the different spellings of the day of the week. I am trying to force the record in the table to have all uppercase fields for strings. To test this, I entered a day of the week in mixed case and low and behold, I get one record that is all uppercase, and another that is the same case as I entered in the form. I can assure you that both records are new and are created at the same time. That brings up another intersting point, why is Access assigning the next sequential number to one record, and the other ID number is the lowest of all ID numbers.

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    OK, now I've tried something different in an attempt to get this to work but I still am unable to make it work. I don't know a lot about recordsets so please correct me if I am wrong.

    Code:
    Sub AddNewRoutings()
    On Error GoTo Err_Handler
    
        Dim db As Database
        Set db = CurrentDb
        Dim frm As Form
        Dim rs As Recordset
        Set rs = db.OpenRecordset("tblRouting")
        
        DoCmd.OpenForm "tblAddRouting"
        
        Set frm = Forms!tblAddRouting
        
        rs.MoveLast
        rs.AddNew
        rs("UNIQUE_LANE_ID") = frm![UNIQUE_LANE_ID]
        rs("LOCATION_ID") = frm![LOCATION_ID]
        rs("CARRIER_ID") = frm![CARRIER_ID]
        rs("FINAL_DEST") = frm![FINAL_DEST]
        rs("SHIP_DAY") = frm![SHIP_DAY]
        rs("DELIVERY_DAY") = frm![DELIVERY_DAY]
        rs("TIME_AT_LOCATION") = frm![TIME_AT_LOCATION]
        rs("CARRIER_NAME_OUT_OF_X_DOCK") = frm![CARRIER_NAME_OUT_OF_X_DOCK]
        rs("X_Dock_ID") = frm![X_DOCK_ID]
        rs("NOTES") = frm![NOTES]
        rs("NO_STOPS") = frm![NO_STOPS]
        rs("STOP_NUM") = frm![STOP_NUM]
        rs.Update
        rs.Close
        
        MsgBox ("Added Record")
        
    Exit_Err_Handler:
        Exit Sub
        
    Err_Handler:
        MsgBox Err.Number & Err.Description
        Resume Exit_Err_Handler
        
    End Sub

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I think I figured it out. Your form is bound to the table, therefore, when you add or change a record with the form, it is being added to the table. But, you are also adding a record to the table using SQL, or in this last try, a recordset. Just use the form and do not click the button, then add a record. Now, check to see if the record has been added to the table. REMEMBER, DO NOT CLICK THE BUTTON. Access was designed to take care of adding records to the table as a service to us when we build a form on a table or query.

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    Thanks, I'll give that a try. How does the form know when I have completed filling it out? Is it when I tab from the last to the first field in the form?

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Anytime you leave a record, such as going to the next record in the table, or closing the form will update (or add) the record to the table.

Posting Permissions

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