Solved Open Form on a Specified Record (1 Viewer)

A

ahmad_rmh

Member
Local time
Today, 02:02
Joined
Jun 26, 2022
Messages
243
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    56.3 KB · Views: 76
  • Capture1.PNG
    Capture1.PNG
    22.9 KB · Views: 82
Last edited:
MarkK

MarkK

bit cruncher
Local time
Yesterday, 16:02
Joined
Mar 17, 2004
Messages
8,181
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.

hth
 
A

ahmad_rmh

Member
Local time
Today, 02:02
Joined
Jun 26, 2022
Messages
243
MarkK said:
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.

hth
Click to expand...

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
 
bastanu

bastanu

AWF VIP
Local time
Yesterday, 16:02
Joined
Apr 13, 2010
Messages
1,402
You are reseting the RecordSource property in the open event which forces a requery; comment out or remove all three .RecordSource = "SELECT *... statements.
 
A

ahmad_rmh

Member
Local time
Today, 02:02
Joined
Jun 26, 2022
Messages
243
bastanu said:
You are reseting the RecordSource property in the open event which forces a requery; comment out or remove all three .RecordSource = "SELECT *... statements.
Click to expand...

Not possible to remove RecordSource in frmTransactionsMain.

I want to open a specific record as per the document number on the summary form. The screenshot is attached in post #1.

Suggest other solution.
 
Last edited:
bastanu

bastanu

AWF VIP
Local time
Yesterday, 16:02
Joined
Apr 13, 2010
Messages
1,402
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.

Cheers,
 
A

ahmad_rmh

Member
Local time
Today, 02:02
Joined
Jun 26, 2022
Messages
243
bastanu said:
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.

Cheers,
Click to expand...

I know it works only on Receipt but what about transfers and adjustment because it opens blank record so therefore i have tried in this way.
 
June7

June7

AWF VIP
Local time
Yesterday, 15:02
Joined
Mar 9, 2014
Messages
5,475
Was the form saved with criteria in Filter property?
 
June7

June7

AWF VIP
Local time
Yesterday, 15:02
Joined
Mar 9, 2014
Messages
5,475
Never mind. I just did some testing and is irrelevant if anything saved in the Filter property as the code overrides it.

Maybe time to provide your db for analysis.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 00:02
Joined
Feb 19, 2013
Messages
16,618
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

simplify your code - instead of

.lblReceipt.Visible = True
.lblTransfer.Visible = False
.lblAdjustment.Visible = False

etc

just have a label called say lblForm and code

lblForm.caption=me.openargs

these two lines are not required (or should not be required

.TranTypeFK.ColumnCount = 2
.TranTypeFK.ColumnWidths = "0;1cm"

with regards these two lines

.EntityFK.Visible = False
.EntityFK_Label.Visible = False

if the label is bound to the textbox, you do not need to set its visible property.

similarly of these three lines

.frmTransactionsSub!NewQuantity.ColumnHidden = True
.frmTransactionsSub!TransactionsDetailPK.ColumnHidden = True
.frmTransactionsSub!TransactionsFK.ColumnHidden = True

you only need the first one

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
 
A

ahmad_rmh

Member
Local time
Today, 02:02
Joined
Jun 26, 2022
Messages
243
CJ_London said:
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

simplify your code - instead of

.lblReceipt.Visible = True
.lblTransfer.Visible = False
.lblAdjustment.Visible = False

etc

just have a label called say lblForm and code

lblForm.caption=me.openargs

these two lines are not required (or should not be required

.TranTypeFK.ColumnCount = 2
.TranTypeFK.ColumnWidths = "0;1cm"

with regards these two lines

.EntityFK.Visible = False
.EntityFK_Label.Visible = False

if the label is bound to the textbox, you do not need to set its visible property.

similarly of these three lines

.frmTransactionsSub!NewQuantity.ColumnHidden = True
.frmTransactionsSub!TransactionsDetailPK.ColumnHidden = True
.frmTransactionsSub!TransactionsFK.ColumnHidden = True

you only need the first one

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
Click to expand...

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.
 
Last edited:
C

CJ_London

Super Moderator
Staff member
Local time
Today, 00:02
Joined
Feb 19, 2013
Messages
16,618
my bad - in the documentnumber double click event

tranType should be TranTypePK
 
A

ahmad_rmh

Member
Local time
Today, 02:02
Joined
Jun 26, 2022
Messages
243
CJ_London said:
my bad - in the documentnumber double click event

tranType should be TranTypePK
Click to expand...

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
 
Minty

Minty

AWF VIP
Local time
Today, 00:02
Joined
Jul 26, 2013
Messages
10,371
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.
 
A

ahmad_rmh

Member
Local time
Today, 02:02
Joined
Jun 26, 2022
Messages
243
Minty said:
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.
Click to expand...

thanks Minty, solved as per the suggestion of CJ.
 
You must log in or register to reply here.

Similar threads

A
open form on specific record through listbox filter record issue
2
Replies
20
Views
1,227
ahmad_rmh
A
B
Solved ACCDE File On Click Error
Replies
9
Views
341
bonzitre
B
C
Close form; open a new form
Replies
11
Views
506
isladogs
isladogs
A
How i make remember password in form login on my pc only !
Replies
7
Views
553
KitaYama
K
M
Double click of listbox on a form
Replies
10
Views
311
moi
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom