open form on specific record through listbox filter record issue (1 Viewer)

[ahmad_rmh]

I am using two list boxes.

On click in listbox1 value the data is shown in listbox2 as per the selection in listbox1.

on double click in listbox2, as per the selection the specific record should have to open.

Private Sub DynamicList1_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmTransactionsMain", , , "[TransactionsPK]=" & Me![DynamicList1]
End Sub

It's working on received transactions and opening the exact record what is required for.

But for "Transferred" transactions it's opening with blank record with the same head as "Receipt" as shown in the attachment.

what's the reason.

 

[MajP]

My guess is that "frmTransactionMain" is based on a query that does not include the value in DynamicList1. I am guessing frmTransactionMain may be mistakenly filtered to Reciepts only.

 

[MajP]

Not what I am suggesting. Assume dynamclist has a transactionPK of 2 and you do the following
DoCmd.OpenForm "frmTransactionsMain", , , "[TransactionsPK]=" & Me![DynamicList1]

If frmTransactionsMain does not include a record with TransactionPK = 2 then you will get a blank form. So I am thinking if your listbox code is correct then there is no matching record in frmTransactionsMain. Meaning you likely have a filter or query applied to frmTransactionsMain.

 

[Eugene-LS]

I am using the following code for filter in DynamicList1,

Try:

Private Sub DynamicList1_DblClick(Cancel As Integer)

    If Mid(Me.DynamicList1.RowSource, 8, 14) = "TransactionsPK" Then
        DoCmd.OpenForm "frmTransactionsMain", , , "[TransactionsPK]=" & Me.DynamicList1
    Else
        MsgBox "Wrong [DynamicList1.RowSource] Property!", vbCritical
    End If

End Sub

 

[MajP]

Put this in the query editor by switching to SQL view. Does it return the correct records?

SELECT * from tblTransactions WHERE TranTypeFK>=3 And TranTypeFK<=5

 

[ahmad_rmh]

Put this in the query editor by switching to SQL view. Does it return the correct records?

SELECT * from tblTransactions WHERE TranTypeFK>=3 And TranTypeFK<=5

Yes, It's giving the accurate results.

 

[Pat Hartman]

Don't have time to look at the db right now but putting the code in the Open event means that it only runs ONCE when the form opens. If you need the code to run for each record (and you probably do) then the code must go in the form's Current event.

 

[Cronk]

The recordsource for frmTransactionsMain is set for Transaction type 1. When you try to open it with a Transaction type 3, there will obviously be no records.

 

[ahmad_rmh]

I have resolved the blank form issue but now it's opening on 1st record and not going on specific record (not filtering the record) in the transfer case.

The code which I am using in the main form is as under

Case "Transfer" 'Transfer
                    lblForm.Caption = "Transfer"
                    lblTranDate.Caption = "Date"
                    lblDocumentNumber.Caption = "Transfer Number"
                    lblMainWarehouseFK.Caption = "Transfer From"
                    lblEntityFK.Caption = "Transfer To"
                    EntityFK.Visible = True
                    lblEntityFK.Visible = True
                    VendorsFK.Visible = False
                    TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypeFK>=3 And TranTypeFK<=5"
                    TranTypeFK.DefaultValue = 3
                    RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK>=3 And TranTypeFK<=5"
                    frmTransactionsSub!NewQuantity.ColumnHidden = True
                    Frame1.DefaultValue = 3

The code which i am using in the list box to open form is as under

Private Sub DynamicList_Click()

    If Me.DynamicList.ListIndex = 2 Then 'Transferred
    Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=3;"
    Me.DynamicList1.ColumnCount = 9
    Me.DynamicList1.ColumnHeads = True
    Me.DynamicList1.ColumnWidths = "0;0;3cm;3.5cm,6cm;3cm;3cm;3cm;5cm"
    End If

End Sub

Private Sub DynamicList1_DblClick(Cancel As Integer)

If Me.DynamicList.ListIndex = 2 Then
    DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPK=" & Me.DynamicList1, , , "Transfer"
End If

End Sub

I have tried in different ways and the most probably the issue is record source or case defining issue.

kindly suggest the solution

 

[CarlettoFed]

It would be easier to understand and help you if you attached an example file.

 

[ebs17]

on double click in listbox2, as per the selection the specific record should have to open

I only see a double click procedure on listbox1. When using proper descriptive names for the controls, the risk of confusion is certainly lower than between 1 and 2.

 

[ahmad_rmh]

DynamicList is listbox1,
DynamicList1 is listbox2

 

[ahmad_rmh]

I have attached the database for the solution of transfer case filter not working on double click DynamicList1.

 

[CarlettoFed]

In VBA the measures must be converted to twips.

Private Sub DynamicList_Click()
    If Me.DynamicList.RowSource = "SELECT * from tblMasterTables WHERE MasterTablesPK>3;" Then
        Select Case Me.DynamicList.ListIndex
            Case 0 'Current Stock
                Me.DynamicList1.RowSource = "SELECT ItemsPK, ItemsListName, CurrentStock, Nz([Opening Balance],0) AS Opening, Nz([NetPurchases],0) AS Purchases, Nz([NetTransfers],0) AS Transfers, Nz([MonthlyAdjustments],0) AS Adjustments, Round(Nz([NetTransfers],0)/Day(Date())*-1,2) AS AvgUsage, EntityName FROM qryStockSummary;"
                Me.DynamicList1.ColumnWidths = "0;3402;1417;1417;1417;1417;1417;1134;2835"
            Case 1 'Received
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=1;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 2 'Transferred
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=3;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 3 'Return to vendor
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=2;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 4 'Transer Return
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=4;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 5 'Expired/Damaged Inventory
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType,  Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=5;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 6 'Adjusted Inventory
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=6;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
        End Select
    End If
End Sub

 

[ahmad_rmh]

What about the filter solution in transfer case

 

[ahmad_rmh]

In VBA the measures must be converted to twips.

Private Sub DynamicList_Click()
    If Me.DynamicList.RowSource = "SELECT * from tblMasterTables WHERE MasterTablesPK>3;" Then
        Select Case Me.DynamicList.ListIndex
            Case 0 'Current Stock
                Me.DynamicList1.RowSource = "SELECT ItemsPK, ItemsListName, CurrentStock, Nz([Opening Balance],0) AS Opening, Nz([NetPurchases],0) AS Purchases, Nz([NetTransfers],0) AS Transfers, Nz([MonthlyAdjustments],0) AS Adjustments, Round(Nz([NetTransfers],0)/Day(Date())*-1,2) AS AvgUsage, EntityName FROM qryStockSummary;"
               Me.DynamicList1.ColumnWidths = "0;3402;1417;1417;1417;1417;1417;1134;2835"
            Case 1 'Received
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=1;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 2 'Transferred
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=3;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 3 'Return to vendor
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=2;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 4 'Transer Return
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=4;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 5 'Expired/Damaged Inventory
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType,  Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=5;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
            Case 3402 'Adjusted Inventory
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=6;"
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
        End Select
    End If
End Sub

Is it possible to make it short through loop from case 1 to onward as there is only difference in each case TranTypeFK.

 

[CarlettoFed]

Private Sub DynamicList_Click()
    If Me.DynamicList.RowSource = "SELECT * from tblMasterTables WHERE MasterTablesPK>3;" Then
        Select Case Me.DynamicList.ListIndex
            Case 0 'Current Stock
                Me.DynamicList1.RowSource = "SELECT ItemsPK, ItemsListName, CurrentStock, Nz([Opening Balance],0) AS Opening, Nz([NetPurchases],0) AS Purchases, Nz([NetTransfers],0) AS Transfers, Nz([MonthlyAdjustments],0) AS Adjustments, Round(Nz([NetTransfers],0)/Day(Date())*-1,2) AS AvgUsage, EntityName FROM qryStockSummary;"
                Me.DynamicList1.ColumnWidths = "0;3402;1417;1417;1417;1417;1417;1134;2835"
            Case Else
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=" & Me.DynamicList.ListIndex
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
        End Select
    End If
End Sub

moreover you should take care to correctly manage the properties of each single field of the various tables at least the property Required. If you see the table tblTransactions admits the saving of the record despite having inserted only the data relating to the TransactionsPK field, the only one set with the Requested = Yes property.

 

[ahmad_rmh]

Private Sub DynamicList_Click()
    If Me.DynamicList.RowSource = "SELECT * from tblMasterTables WHERE MasterTablesPK>3;" Then
        Select Case Me.DynamicList.ListIndex
            Case 0 'Current Stock
                Me.DynamicList1.RowSource = "SELECT ItemsPK, ItemsListName, CurrentStock, Nz([Opening Balance],0) AS Opening, Nz([NetPurchases],0) AS Purchases, Nz([NetTransfers],0) AS Transfers, Nz([MonthlyAdjustments],0) AS Adjustments, Round(Nz([NetTransfers],0)/Day(Date())*-1,2) AS AvgUsage, EntityName FROM qryStockSummary;"
                Me.DynamicList1.ColumnWidths = "0;3402;1417;1417;1417;1417;1417;1134;2835"
            Case Else
                Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=" & Me.DynamicList.ListIndex
                Me.DynamicList1.ColumnWidths = "0;0;1701;1984,3402;1701;1701;1701;2835"
        End Select
    End If
End Sub

Thanks Carletto,

what about the transfer case solution,

open Navigation form, click on Inventory Management, then click in list box 1st - Transferred, it will show the related records in 2nd list box, double click on any record, it's opening on 1st record but not going on the specified record as of document number, where as in case of received it's working fine.

Suggest solution please.

 

[CarlettoFed]

The correct file is in the attachment, because the things to change were different. Excuse me but what you have achieved is truly something almost unwatchable.

 

[MajP]

is this correct? Or is 2 and 3 reversed.

  If Me.DynamicList.ListIndex = 2 Then 'Transferred
    Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=3;"
    Me.DynamicList1.ColumnCount = 9
    Me.DynamicList1.ColumnHeads = True
    Me.DynamicList1.ColumnWidths = "0;0;3cm;3.5cm,6cm;3cm;3cm;3cm;5cm"
    End If
    If Me.DynamicList.ListIndex = 3 Then 'Return to vendor
    Me.DynamicList1.RowSource = "SELECT TransactionsPK, TranTypePK, TranDate, DocumentNumber, ItemsListName, PackingSize, TranType, Stock_In_Out, EntityName from qryTransactionsExtended WHERE TranTypeFK=2;"
    Me.DynamicList1.ColumnCount = 9
    Me.DynamicList1.ColumnHeads = True
    Me.DynamicList1.ColumnWidths = "0;0;3cm;3.5cm,6cm;3cm;3cm;3cm;5cm"

if the listindex = 2 then open the query where tranTypeFK=3, if listIndex = 2 then open where transTypeFK = 3. Seems very confusing if that is correct. If you have to then I would add another field to the rowsource of the dynamic list. Instead of returning the listindex return the value of a field corresponding to the correct transType.