I am trying to open form on a specified record with openargs condition but it is opening the form on 1st record of the form and not opening on the specified record. The code is as under
Code:
Private Sub DocumentNumber_DblClick(Cancel As Integer)
If Me.TranTypePK <= 2 Then
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , "Receipt"
End If
If Me.TranTypePK >= 3 And Me.TranTypePK <= 5 Then
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , "Transfer"
End If
If Me.TranTypePK = 6 Then
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , "Adjustment"
End If
End Sub[
/CODE]
The screenshots are attached herewith. Kindly suggest the solution.
The code you posted looks fine. I would refactor it like...
Code:
Private Sub DocumentNumber_DblClick(Cancel As Integer)
Dim args As String
If Me.TranTypePK <= 2 Then
args = "Receipt"
ElseIf Me.TranTypePK = 6 Then
args = "Adjustment"
Else
args = "Transfer"
End If
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , args
End Sub
...but the problem must be somewhere else. Maybe post the database, or the code in frmTransactionsMain.
The code you posted looks fine. I would refactor it like...
Code:
Private Sub DocumentNumber_DblClick(Cancel As Integer)
Dim args As String
If Me.TranTypePK <= 2 Then
args = "Receipt"
ElseIf Me.TranTypePK = 6 Then
args = "Adjustment"
Else
args = "Transfer"
End If
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , args
End Sub
...but the problem must be somewhere else. Maybe post the database, or the code in frmTransactionsMain.
Thanks @MarkK, The code of the "frmTransactionsMain" is as under;
Code:
Private Sub Form_Open(Cancel As Integer)
Select Case Me.OpenArgs
Case "Receipt"
With Me
.lblReceipt.Visible = True
.lblTransfer.Visible = False
.lblAdjustment.Visible = False
.EntityFK.Visible = False
.EntityFK_Label.Visible = False
.VendorsFK.Visible = True
.VendorsFK_Label.Visible = True
.TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK<=2"
.TranTypeFK.ColumnCount = 2
.TranTypeFK.ColumnWidths = "0;1cm"
.TranTypeFK.DefaultValue = 1
.RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK<=2"
End With
If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
With Me
.frmTransactionsSub!NewQuantity.ColumnHidden = True
.frmTransactionsSub!TransactionsDetailPK.ColumnHidden = True
.frmTransactionsSub!TransactionsFK.ColumnHidden = True
End With
End If
Case "Transfer"
With Me
.lblReceipt.Visible = False
.lblTransfer.Visible = True
.lblAdjustment.Visible = False
.EntityFK.Visible = True
.EntityFK_Label.Visible = True
.VendorsFK.Visible = False
.VendorsFK_Label.Visible = False
.TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK>=3 And TranTypePK<=5"
.TranTypeFK.ColumnCount = 2
.TranTypeFK.ColumnWidths = "0;1cm"
.TranTypeFK.DefaultValue = 3
.RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK>=3 And TranTypeFK<=5"
End With
If Forms!frmTransactionsMain!lblTransfer.Visible = True Then
With Me
.frmTransactionsSub!NewQuantity.ColumnHidden = True
.frmTransactionsSub!TransactionsDetailPK.ColumnHidden = True
.frmTransactionsSub!TransactionsFK.ColumnHidden = True
End With
End If
Case "Adjustment"
With Me
.lblReceipt.Visible = False
.lblTransfer.Visible = False
.lblAdjustment.Visible = True
.EntityFK.Visible = True
.EntityFK_Label.Visible = True
.VendorsFK.Visible = False
.VendorsFK_Label.Visible = False
.TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK=6"
.TranTypeFK.ColumnCount = 2
.TranTypeFK.ColumnWidths = "0;1cm"
.TranTypeFK.DefaultValue = 6
.RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK=6"
End With
If Forms!frmTransactionsMain!lblAdjustment.Visible = True Then
With Me
.frmTransactionsSub!NewQuantity.ColumnHidden = False
.frmTransactionsSub!TransactionsDetailPK.ColumnHidden = True
.frmTransactionsSub!TransactionsFK.ColumnHidden = True
End With
End If
End Select
End Sub
You are reseting the RecordSource property in the open event which forces a requery; comment out or remove all three .RecordSource = "SELECT *... statements.
You are reseting the RecordSource property in the open event which forces a requery; comment out or remove all three .RecordSource = "SELECT *... statements.
This statement's attempt to open the form at the desired record:
If Me.TranTypePK <= 2 Then
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , "Receipt"
End If
is negated by this one in the target's form Open event (specifically the .RecordSource ="SELECT * FROM tblTransactions WHERE TranTypeFK<=2):
Code:
Case "Receipt"
With Me
.lblReceipt.Visible = True
.lblTransfer.Visible = False
.lblAdjustment.Visible = False
.EntityFK.Visible = False
.EntityFK_Label.Visible = False
.VendorsFK.Visible = True
.VendorsFK_Label.Visible = True
.TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK<=2"
.TranTypeFK.ColumnCount = 2
.TranTypeFK.ColumnWidths = "0;1cm"
.TranTypeFK.DefaultValue = 1
.RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK<=2"
End With
If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
With Me
.frmTransactionsSub!NewQuantity.ColumnHidden = True
.frmTransactionsSub!TransactionsDetailPK.ColumnHidden = True
.frmTransactionsSub!TransactionsFK.ColumnHidden = True
End With
End If
Than line resets the record source of the target form to only include "receipts" which in turn requeries the form and you end up on the first record. You do not need to do that, the first statement to open the form at Me.TransactionPK would work fine without it.
This statement's attempt to open the form at the desired record:
If Me.TranTypePK <= 2 Then
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , "Receipt"
End If
is negated by this one in the target's form Open event (specifically the .RecordSource ="SELECT * FROM tblTransactions WHERE TranTypeFK<=2):
Code:
Case "Receipt"
With Me
.lblReceipt.Visible = True
.lblTransfer.Visible = False
.lblAdjustment.Visible = False
.EntityFK.Visible = False
.EntityFK_Label.Visible = False
.VendorsFK.Visible = True
.VendorsFK_Label.Visible = True
.TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK<=2"
.TranTypeFK.ColumnCount = 2
.TranTypeFK.ColumnWidths = "0;1cm"
.TranTypeFK.DefaultValue = 1
.RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK<=2"
End With
If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
With Me
.frmTransactionsSub!NewQuantity.ColumnHidden = True
.frmTransactionsSub!TransactionsDetailPK.ColumnHidden = True
.frmTransactionsSub!TransactionsFK.ColumnHidden = True
End With
End If
Than line resets the record source of the target form to only include "receipts" which in turn requeries the form and you end up on the first record. You do not need to do that, the first statement to open the form at Me.TransactionPK would work fine without it.
your code could be simpler, and as Vlad has said your code changing the recordsource removes the filter - so why do you need to change the recordsource?
But some suggestions, not tested
1. put me.filteron=true in the load event
2. or change your recordsource assignment to include your filter e.g. for receipts
.RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK<=2 AND " & me.filter
In fact rather than passing a text value as the openarg, I would pass the value
Code:
Private Sub DocumentNumber_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , TranType
End Sub
and your open event would become - although not clear what your form is intended to do - are users allowed to change the transaction type, vendor etc for an existing transaction? Having selected a transaction 'header' is the idea they can then add new ones?
Code:
Private Sub Form_Open(Cancel As Integer)
Select Case Me.OpenArgs
Case Is <= 2 'receipt
lblForm.Caption = "Receipt"
EntityFK.Visible = False
VendorsFK.Visible = True
TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK<=2"
TranTypeFK.DefaultValue = 1
'not sure why you need this next line - better to disable the navigation buttons and set the form allow additions property to false
RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK<=2 AND " & Me.Filter
frmTransactionsSub.NewQuantity.ColumnHidden = True
Case Is <= 5 'Transfer (no need to specify >=3 since previous case statement will have taken care of that)
lblForm.Caption = "Transfer"
EntityFK.Visible = True
VendorsFK.Visible = False
TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK>=3 And TranTypePK<=5"
TranTypeFK.DefaultValue = 3
RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK>=3 And TranTypeFK<=5 AND " & Me.Filter
frmTransactionsSub.NewQuantity.ColumnHidden = True
Case 6 'adjustment
lblForm.Caption = "Adjustment"
EntityFK.Visible = True
VendorsFK.Visible = False
TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK=6"
TranTypeFK.DefaultValue = 6
RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK=6 AND " & Me.Filter
frmTransactionsSub.NewQuantity.ColumnHidden = False
End Select
End Sub
your code could be simpler, and as Vlad has said your code changing the recordsource removes the filter - so why do you need to change the recordsource?
But some suggestions, not tested
1. put me.filteron=true in the load event
2. or change your recordsource assignment to include your filter e.g. for receipts
.RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK<=2 AND " & me.filter
In fact rather than passing a text value as the openarg, I would pass the value
Code:
Private Sub DocumentNumber_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmTransactionsMain", , , "TransactionsPk=" & Me.TransactionsPK, , , TranType
End Sub
and your open event would become - although not clear what your form is intended to do - are users allowed to change the transaction type, vendor etc for an existing transaction? Having selected a transaction 'header' is the idea they can then add new ones?
Code:
Private Sub Form_Open(Cancel As Integer)
Select Case Me.OpenArgs
Case Is <= 2 'receipt
lblForm.Caption = "Receipt"
EntityFK.Visible = False
VendorsFK.Visible = True
TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK<=2"
TranTypeFK.DefaultValue = 1
'not sure why you need this next line - better to disable the navigation buttons and set the form allow additions property to false
RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK<=2 AND " & Me.Filter
frmTransactionsSub.NewQuantity.ColumnHidden = True
Case Is <= 5 'Transfer (no need to specify >=3 since previous case statement will have taken care of that)
lblForm.Caption = "Transfer"
EntityFK.Visible = True
VendorsFK.Visible = False
TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK>=3 And TranTypePK<=5"
TranTypeFK.DefaultValue = 3
RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK>=3 And TranTypeFK<=5 AND " & Me.Filter
frmTransactionsSub.NewQuantity.ColumnHidden = True
Case 6 'adjustment
lblForm.Caption = "Adjustment"
EntityFK.Visible = True
VendorsFK.Visible = False
TranTypeFK.RowSource = "SELECT TranTypePK, TranType from tblTranTypes WHERE TranTypePK=6"
TranTypeFK.DefaultValue = 6
RecordSource = "SELECT * from tblTransactions WHERE TranTypeFK=6 AND " & Me.Filter
frmTransactionsSub.NewQuantity.ColumnHidden = False
End Select
End Sub
Thanks a lot CJ giving me your precious and valuable time , I have implemented what you have suggested but "Case Is" is not responding. I don't know why.
No, I had changed before, I am talking about the opening of form for data entry in a normal way through dashboard.
Code:
Private Sub NavigationButton_Receipt_Click()
DoCmd.OpenForm "frmTransactionsMain", , , , , , "<=2"
End Sub
Private Sub NavigationButton_Transfer_Click()
DoCmd.OpenForm "frmTransactionsMain", , , , , , "<=5"
End Sub
Private Sub NavigationButton_Adjustment_Click()
DoCmd.OpenForm "frmTransactionsMain", , , , , , "6"
End Sub
Private Sub Form_Load()
DoCmd.Maximize
End Sub
As CJ is offline - you should simply be passing the number, not the string of the comparison, and you can probably use the same code/ button for all actions by doing something like...
Private Sub NavigationButton_Receipt_Click()
DoCmd.OpenForm "frmTransactionsMain", , , , , , Me.TranTypePK
End Sub
Which would pick the correct transaction type by virtue of it having the correct type number.
As CJ is offline - you should simply be passing the number, not the string of the comparison, and you can probably use the same code/ button for all actions by doing something like...
Private Sub NavigationButton_Receipt_Click()
DoCmd.OpenForm "frmTransactionsMain", , , , , , Me.TranTypePK
End Sub
Which would pick the correct transaction type by virtue of it having the correct type number.