INSERT INTO Skipping Rows

[RichH]

Hi all,



I have a function that adds records to a temporary table (tmpCart), which is then transferred to another data table (tblCheckOuts) once a user clicks the Check Out Button. I'm having some issues where the function that adds those records to the main table. It seems to be writing over some of them or deleting them with the next record in the temp table.

For instance, the tmpCart may have eight records, each having a movie id, kiosk id, and inventory id. The tblCheckOuts contains an auto number, customer ID, movie ID, kiosk ID, inventory ID, check out date, and a few yes/no fields used in other functions. The table currently has 179 records. When the strSQL runs, it may add the first two records in the tmpCart, increasing the record count to 181. It may then back up a row and add in record #182 where 181 was so now the record count goes "178, 179, 180, 182" I don't know how or why it's doing this... Here's the code below. Any help would be appreciated.

Private Sub cmdCheckout_Click()
    Dim strUser     As String
    Dim strKiosk    As String
    Dim strMovie   As String
    Dim strInvty    As String
    Dim lngCartCnt As Long

'   Initialize the variables
    strUser = Me.OpenArgs
    strKiosk = Me.frmCart.Form!Kiosk
    strMovie = Me.frmCart.Form!Movie
    strInvty = Me.frmCart.Form!Inventory_ID
    
    lngCartCnt = GetCartCnt

'   Confirm there are items in the cart
    If lngCartCnt = 0 Then
        MsgBox "Your cart is empty", vbInformation, "No Items in Cart"
    Else
'       Delete any selected titles and requery the subform
        DeleteTitle 'Function to delete any items in the cart that have been marked for removal
           
        strSQL = "INSERT INTO tblCheckOuts ( Customer, Movie, Kiosk, [Inventory ID], [Check Out Date]) SELECT '" & strUser & "', tmpCart.Movie, tmpCart.Kiosk, tmpCart.Inventory_ID, Date() AS CheckoutDt FROM tmpCart"
        

'       Execute the SQL
        CurrentDb.Execute strSQL
        
'       Initialize the SQL to update movie status
        strSQL = "UPDATE tmpCart INNER JOIN tblInventory ON tmpCart.Inventory_ID = tblInventory.[Inventory ID] SET tblInventory.Status = 'Unavailable'"
        
'       Execute the SQL
        CurrentDb.Execute strSQL
        
'       Remove the records from the cart table
        DeleteAllCart

'       Close the checkout and broswe form and display the main menu
        DoCmd.Close acForm, "frmCheckout", acSaveNo
        DoCmd.Close acForm, "frmBrowse", acSaveNo
        Forms!frmMainMenu.objOption.Form.Requery
        ShowForm Forms!frmMainMenu, True
        
        MsgBox "You have rented " & lngCartCnt & " Movie(s)", vbInformation, "Checkout Complete"
    End If
    
End Sub

BlueBox(1).zip

[orange]

Welcome to the forum.
You might get focused responses if you post a copy of your database(zip format) with enough record to repeat the issue.

[RichH]

Thank You! Usually the forums I've been to don't want people linking workbooks, so this is nice. Here's a link to my Google Drive.

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[June7]

Best to attach file to post. Many will not download files from other locations.

[davegri]

RichH, you may have attached the wrong DB. frmBrowse has no recordsource, so it's impossible to set up a checkout scenario.
Also, it will cause an error if you try to make a form visible or invisible that is not loaded. You need the below function to verify the form's status which would go into modFunctions:

Public Function fcnIsLoaded(frm As String) As Boolean
    If CurrentProject.AllForms(frm).IsLoaded Then
        fcnIsLoaded = True
    Else
        fcnIsLoaded = False
    End If
End Function

Then an example of use in frmLoginCust:

Private Sub cmdOk_Click()
On Error GoTo cmdOk_Click_Err
    Dim strUser     As String
'   Call the subroutine to reset the backcolor
    ResetBackcolor
        
'   Test to confirm required entries are present
    If EntryIsValid Then
'       Required entries were found so initialize the variables
        strUser = Me.txtUser.Column(1)
'       Hide the browse form in case they want to continue shopping
        If fcnIsLoaded("frmBrowse") Then
            ShowForm Forms!frmBrowse, False
        End If
'       Open the checkout form and close the login form
        DoCmd.OpenForm "frmCheckout", , , , , , strUser
        DoCmd.Close acForm, "frmLoginCust", acSaveNo
        
    Else
'       Missing required entry so prompt the user
        MsgBox "Please enter the required data", vbInformation, "Missing Entry"
    End If
cmdOk_Click_Exit:

[RichH]

The first menu that kind of kicks things off is the frmMainMenu. After the user selects either to browse movies at a physical location or via the online option, frmBrowse loads. A sub form is then loaded depending on filter selection (Actor/Genre/Director). the lstBrowse listbox chooses which form to load upon selection. The List Box lst(Genre/Actor/Director, dependng on the form) has a source that is dependent on the kioskID of the kiosk selected. If they browse online, the kioskID passed is a wildcard "*".