none
The rows of subform disappear after I close and reopen the form RRS feed

  • Question

  • I have a form for entering inventory transaction. It has a main form and a subform. The subform is for selecting inventory item and entering inventory transaction. I don't know why when I make a new form and enter information in it, after closing and reopening the form the row information (inventoryID) disappears. It is in all entered forms. The code for the main and sub form is as follow:

    Main Form code:

    Option Compare Database
    Private Sub cboInventoryPermissionID_DblClick(Cancel As Integer)
           
        If Not Me.NewRecord Then
            DoCmd.OpenReport "rptInventoryPermission", acViewPreview, , "TransferPermissionID=" & Me.cboInventoryPermissionID
        End If
    End Sub
    Private Sub cboTransactionType_AfterUpdate()
       
          If cboTransactionType.ListIndex = 0 Then
            Me.frmInventoryTransactionDetailSubform.Form.QtyDeducted.Enabled = False
            Me.frmInventoryTransactionDetailSubform.Form.QtyAdded.Enabled = True
            Me.frmInventoryTransactionDetailSubform.Form.cboCostPointID.Enabled = False
            PurchaseOrderID.Enabled = True
           
                  
        ElseIf cboTransactionType.ListIndex = 1 Then
            Me.frmInventoryTransactionDetailSubform.Form.QtyAdded.Enabled = False
            PurchaseOrderID.Enabled = False
            Me.frmInventoryTransactionDetailSubform.Form.QtyDeducted.Enabled = True
            Me.frmInventoryTransactionDetailSubform.Form.cboCostPointID.Enabled = True
           
           
           
        End If
           
         
    End Sub
    Private Sub cmdDelete_Click()
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim rec As DAO.Recordset
        Dim strSQL As String
       
      
       
       
        strSQL = "SELECT tblInventoryTransactionDetail.InventoryTransactionDetailID, tblInventoryTransactionDetail.InventoryTransactionID, tblInventoryTransactionDetail.InventoryID, tblInventoryTransactionDetail.QtyAdded, tblInventoryTransactionDetail.QtyDeducted, tblInventoryTransactionDetail.PricePerUnit, tblInventory.InventoryUnit, tblInventoryTransactionDetail.CostPointID, tblInventoryTransactionDetail.[Select] " _
                  & "FROM tblInventory INNER JOIN tblInventoryTransactionDetail ON tblInventory.InventoryID = tblInventoryTransactionDetail.InventoryID Where tblInventoryTransactionDetail.[Select]=TRUE;"
                 
        Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
       
        'If norecords in recordset,exit
        If rec.EOF Then Exit Sub
       
        Do Until rec.EOF
            rec.Delete
            rec.MoveNext
        Loop
       
        Me.Refresh
        rec.Close
       
     
    End Sub
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    'for controlling the necessary fields
        If IsNull(Me.StockID) Then
            MsgBox "ÇäÊÎÇÈ ÇäÈÇÑ ÏÑ ÊÑǘäÔåÇí ÇäÈÇÑ ÇÌÈÇÑí ÇÓÊ", vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading, "ÝíáÏ ÇÌÈÇÑí"
            Cancel = True
            Me.StockID.SetFocus
        End If
       
         If IsNull(Me.TransactionDate) Then
            MsgBox "æÑæÏ ÊÇÑíÎ ÏÑ ÊÑǘäÔåÇí ÇäÈÇÑ ÇÌÈÇÑí ÇÓÊ", vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading, "ÝíáÏ ÇÌÈÇÑí"
            Cancel = True
            Me.TransactionDate.SetFocus
        End If
       
         If IsNull(Me.TransactionType) Then
            MsgBox "ÇäÊÎÇÈ äæÚ ÊÑǘäÔ ÏÑ ÊÑǘäÔåÇí ÇäÈÇÑ ÇÌÈÇÑí ÇÓÊ", vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading, "ÝíáÏ ÇÌÈÇÑí"
            Cancel = True
            Me.cboTransactionType.SetFocus
        End If
       
         If IsNull(Me.CustomerID) Then
            MsgBox "ÇäÊÎÇÈ äÇã ØÑÝ ÍÓÇÈ ÏÑ ÊÑǘäÔåÇí ÇäÈÇÑ ÇÌÈÇÑí ÇÓÊ", vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading, "ÝíáÏ ÇÌÈÇÑí"
            Cancel = True
            Me.CustomerID.SetFocus
        End If
       
    End Sub

    Private Sub Form_Current()
        If optConfirmed.Value = True Then
            lblConfirmed.Visible = True
            Me.AllowEdits = False
            Me.AllowDeletions = False
            Me.frmInventoryTransactionDetailSubform.Form.AllowEdits = False
            Me.frmInventoryTransactionDetailSubform.Form.AllowAdditions = False
            Me.frmInventoryTransactionDetailSubform.Form.AllowDeletions = False
        Else
            lblConfirmed.Visible = False
            Me.AllowEdits = True
            Me.AllowDeletions = True
            Me.frmInventoryTransactionDetailSubform.Form.AllowEdits = True
            Me.frmInventoryTransactionDetailSubform.Form.AllowAdditions = True
            Me.frmInventoryTransactionDetailSubform.Form.AllowDeletions = True

        End If

        'For enabling and disabling field based on transaction type

        If cboTransactionType.ListIndex = 0 Then
            Me.frmInventoryTransactionDetailSubform.Form.QtyDeducted.Enabled = False
            Me.frmInventoryTransactionDetailSubform.Form.QtyAdded.Enabled = True
            PurchaseOrderID.Enabled = True
            Me.frmInventoryTransactionDetailSubform.Form.cboCostPointID.Enabled = False


        ElseIf cboTransactionType.ListIndex = 1 Then
            Me.frmInventoryTransactionDetailSubform.Form.QtyAdded.Enabled = False
            PurchaseOrderID.Enabled = False
            Me.frmInventoryTransactionDetailSubform.Form.QtyDeducted.Enabled = True
            Me.frmInventoryTransactionDetailSubform.Form.cboCostPointID.Enabled = True
        End If



    End Sub

    Sub Form Code:

    Option Compare Database
    Private Sub cboInventoryCategoryID_AfterUpdate()
        Me!cboInventoryGroupID = Null
       
        If IsNull(cboInventoryCategoryID) Then
            Me!cboInventoryGroupID.Enabled = False
        Else
            Me!cboInventoryGroupID.Enabled = True
            Me!cboInventoryGroupID.RowSource = "SELECT InventoryGroupID,InventoryGroupName " & _
                "FROM tblInventoryGroup " & _
                "WHERE InventoryCategoryID = " & Me.cboInventoryCategoryID & " " & _
                "ORDER BY InventoryGroupName"
        End If
    End Sub
    Private Sub cboInventoryGroupID_AfterUpdate()
        DoCmd.RunCommand acCmdSaveRecord
       
        If IsNull(cboInventoryGroupID) Then
            Me!cboInventoryID.Enabled = False
        Else
            Me!cboInventoryID.Enabled = True
            Me!cboInventoryID.RowSource = "Select InventoryID,InventoryName " _
            & "FROM tblInventory " _
            & "WHERE InventoryGroupID= " & Me.cboInventoryGroupID & " " & _
            "ORDER BY InventoryName"
           
        End If
    End Sub
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        'For avoiding two value in QtyAdded and QtyDeducted I added this code
       
        If Me.QtyAdded.Value <> 0 And Me.QtyDeducted <> 0 Then
            MsgBox "ÝÞØ í˜í ÇÒ ÝíáÏåÇí æÇÑÏå íÇ ÕÇÏÑå ãíÊæÇääÏ ãÞÏÇÑ ÏÇÔÊå ÈÇÔäÏ", vbOKOnly + vbCritical, "Duplicate Value"
            Me.QtyAdded.Value = 0
            Me.QtyDeducted.Value = 0
            Cancel = True
        End If
       
        
        
       
       
           
           
    End Sub
    Private Sub cboInventoryID_DblClick(Cancel As Integer)
        Dim strWhere As String
        strWhere = "True"
       
        If Not IsNull(Me.Parent.StockID) Then
            strWhere = strWhere & " AND tblInventoryTransaction.StockID= " & Me.Parent.StockID
        End If
        If Not IsNull(Me.InventoryID) Then
            strWhere = strWhere & "AND tblInventory.InventoryID= " & Me.cboInventoryID
        End If
            DoCmd.OpenReport ReportName:="rptInventoryBalanceByItemSum", View:=acViewPreview, WhereCondition:=strWhere
    End Sub
       

    Private Sub QtyDeducted_BeforeUpdate(Cancel As Integer)
    'For controling the minus inventory I add this procedure
    'for the problems in decimal places I changed the variable "lngStockInHand" from double to currency
           
            If Not IsNull(Me.cboInventoryID) Then
            Const Message = "ÈÇÇÚãÇá Çíä ÊÑǘäÔ ãæÌæÏí ãäÝí ãíÔæÏ¡áØÝÇ ËÈÊ ÑÇ ÇÕáÇÍ äãÇííÏ"
            Dim curStockInHand As Currency
            Dim strWhere As String
        'Find the criteria for comparing balance
           
        strWhere = "True"
        If Not IsNull(Me.Parent.StockID) Then
            strWhere = strWhere & " AND tblInventoryTransaction.StockID= " & Me.Parent.StockID
        End If
        If Not IsNull(Me.cboInventoryID) Then
            strWhere = strWhere & " AND tblInventory.InventoryID= " & Me.cboInventoryID
        End If
       
        'find the quantity in stock from query
             curStockInHand = Nz(DLookup("[Balance]", "qryInventoryBalanceControl", strWhere), -1)
            If curStockInHand <> -1 Then
            If Me.QtyDeducted > curStockInHand Then
                  MsgBox Message, vbExclamation + vbMsgBoxRight + vbMsgBoxRtlReading, "ÚÏã ˜ÝÇíÊ ãæÌæÏí"
                  Cancel = True
            End If
        Else
           MsgBox "Çíä äæÚ ˜ÇáÇ ÈÑÇí Çíä ØÑÝ ÍÓÇÈ æÌæÏ äÏÇÑÏ", vbExclamation + vbMsgBoxRight + vbMsgBoxRtlReading, "ÚÏã ÊØÇÈÞ äæÚ ˜ÇáÇ"
                Cancel = True
        End If
            End If
        
       
    End Sub

                                                     


    Karim Vaziri Regards,


    • Edited by kvaziri Saturday, July 16, 2016 9:27 PM
    Saturday, July 16, 2016 9:26 PM

Answers

  • Dear Dirk,

    Thanks for your help.

    After checking all row sources I noticed that there is a criteria in "Inventory ID" combo box that after removing it the problem was solved.


    Karim Vaziri Regards,

    Monday, July 18, 2016 9:11 PM

All replies

  • Hi Karim,

    >> after closing and reopening the form the row information (inventoryID) disappears

    What do you mean with this? Do you mean the rows did not show up in Form which exist in the table or the new rows did not insert into table when you close this form? If it is previous one, I suggest you set filter in Form load. If it is later one, is your subform an unbound Form? If it is unbound, you will need code to insert the data, if it is bound, it will insert the data automatically. You could try “Me.Dirty = False” to force Access database insert the new data.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, July 18, 2016 2:01 AM
  • Dear Edward,

    It is the first one. After we insert information in rows and save it, after reopening the form there is no information in inventory field but when we print the "inventory receipt" it is OK and all information are in print.
    What do you mean by setting filter in form load?


    Karim Vaziri Regards,

    Monday, July 18, 2016 6:19 AM
  • I can think of two things you should check, if you haven't already.  The first is that the subform's DataEntry property may be set to Yes (True).  If that's so, then the form will always open to allow entry of new records, but will not display existing records.  The Data Entry property should be set to No (False).

    The second is that the recordsource of the subform may be such that no existing records meet the criteria.  This could only be the case if the recordsouce is a query, not a table.  You can check it by opening the recordsource query directly as a datasheet, and see if all the expected records are displayed.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, July 18, 2016 8:26 PM
  • Dear Dirk,

    Thanks for your help.

    After checking all row sources I noticed that there is a criteria in "Inventory ID" combo box that after removing it the problem was solved.


    Karim Vaziri Regards,

    Monday, July 18, 2016 9:11 PM