OpenArgs (1 Viewer)

[tucker61]

Trying to use Openargs to filter a form using a value from a Combobox on a different form - but i just dont get how it works.

This is what i have so far.

Main Form

Private Sub BtnFobDespatches_Click()
On Error GoTo Handler
Dim Stlinkcriteria As String
WarningsOff
    Stlinkcriteria = "Calender Year=" & Forms!Frmukmenu.CboxYear.Value
    DoCmd.OpenForm "FrmUKFOBDespatches", acFormDS, , , , , Stlinkcriteria
    WarningsOff
BtnErrors_Click_Exit:
    Exit Sub

Handler:
    MsgBox Error$
    Resume BtnErrors_Click_Exit
End Sub

The StLink Criteria pulls back the correct year.

Then on my open event on the form i wish to open i have this code.

Private Sub Form_Open(Cancel As Integer)
    Dim CbYear As String
    CbYear = Forms!Frmukmenu.OpenArgs
    If Len(CbYear) > 0 Then
        DoCmd.GoToControl "Calender Year"
        DoCmd.FindRecord CbYear, , True, , True, , True
    End If
End Sub

The OpenArgs is showing as a Null Value - so produces the error "Invlaid Use of Null"

Please advise.

 

[arnelgp]

Main Form:

Private Sub BtnFobDespatches_Click()
On Error GoTo Handler
Dim Stlinkcriteria As String
WarningsOff
    'Stlinkcriteria = "Calender Year=" & Forms!Frmukmenu.CboxYear.Value
    Stlinkcriteria = Forms!Frmukmenu.CboxYear.Value
    DoCmd.OpenForm "FrmUKFOBDespatches", acFormDS, , , , , Stlinkcriteria
    WarningsOff
BtnErrors_Click_Exit:
    Exit Sub

Handler:
    MsgBox Error$
    Resume BtnErrors_Click_Exit
End Sub

you use Load Event instead of Open Event:

Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        If Val(Me.OpenArgs & "") > 0 Then
            Me![Calender Year].Setfocus
            DoCmd.FindRecord Me.OpenArgs
        End If
    End If
End Sub

 

[Pat Hartman]

Normally, you would use the Where argument of the OpenForms method rather than the OpenArgs to filter the opening form. This technique requires NO CODE in the form you are opening.

If you have to filter a subform, then you can't use the Where argument and so the OpenArgs would be appropriate. But to address the OpenArgs passed to the parent form, you need to use Me.Parent.OpenArgs. BUT since the subform loads before the mainform loads, that might not work. I would probably not have the subform load by default. I would populate the subform control in the parent form's Open event.

 

[tucker61]

Normally, you would use the Where argument of the OpenForms method rather than the OpenArgs to filter the opening form. This technique requires NO CODE in the form you are opening.

If you have to filter a subform, then you can't use the Where argument and so the OpenArgs would be appropriate. But to address the OpenArgs passed to the parent form, you need to use Me.Parent.OpenArgs. BUT since the subform loads before the mainform loads, that might not work. I would probably not have the subform load by default. I would populate the subform control in the parent form's Open event.

This is how it is currently set - but i am trying to move to using OpenArgs as i think i could use that in a few of my forms. Thanks for the advise though.

 

[CJ_London]

Normally, you would use the Where argument of the OpenForms method rather than the OpenArgs to filter the opening form.

OP doesn't want to filter the form, they want to find a specific record. Not tested but would think Arnel's suggestion would work

 

[ebs17]

Pared down to the bare essentials:

Private Sub BtnFobDespatches_Click()
    DoCmd.OpenForm "FrmUKFOBDespatches", acFormDS, , , , , Forms.Frmukmenu.CboxYear.Value
End Sub

Private Sub Form_Open(Cancel As Integer)
' A form instance can be created differently, i.e. also without passing OpenArgs.
' Therefore, you should always check whether a transfer takes place.
' OpenArgs generates a string as return. With other data types a type conversion must take place.
   
    If Not IsNull(Me.OpenArgs) Then
        Me.Recordset.FindFirst "[Calender Year] = " & CLng(Me.OpenArgs)
    End If
End Sub

Using OpenArgs is the gossamer introduction to object-oriented programming: Passing a value to an interface of the form to be opened.

There one should then also stuff like SetFocus and DoCmd commands (high proximity to macros and menu commands) clearly down.

 

[Pat Hartman]

This is how it is currently set - but i am trying to move to using OpenArgs as i think i could use that in a few of my forms. Thanks for the advise though.

Did someone tell you that this was a "better" method? Unless you are leaving the recordsource blank, using the OpenArgs to "filter" simply results in Access having to load the recordsource twice or load the whole table/query and then filter later. There are certainly reasons for using the OpenArgs but make sure you are using it because you KNOW that it will solve a problem that the where argument of the OpenForm/OpenReport method won't solve. For example, if you want to filter both the main form and a subform, use the where argument to provide criteria for the main form and then using the OpenArgs is one way you can use to filter the subforms but in that case, you refer to the parent's OpenArgs property. Me.Parent.OpenArgs

I use the OpenArgs property to control the flow between forms. I pass in the name of the calling form using OpenArgs. Then I hide the calling form. When the called form closes, it opens the form name passed in the OpenArgs. This actually just unhides the hidden form so it's state was as you left it when it was hidden.

If you are using ODBC data sources, you NEVER want to filter forms using the Access filter method or use .Find or .Seek to move through a recordset locally. Instead, you use a query with a where clause that gets its criteria from a different form. That way, each main form only fetches the single record you want to edit which is far more efficient. No need to bring entire tables or unfiltered queries across the network and filter locally.

Using the .FindRecord method is not wrong but it is likely to just be inefficient since loads the entire table/query and then moves to the row you want to work with. If your BE is Jet/ACE. This type of processing won't be awful unless your table contains a large number of rows but you really don't want to do this if the BE is SQL Server.

When you have a combo that provides criteria, then
1. bind the form to a query with a Where clause --- Where SomeID = Forms!yourformname!cboSomeID
or
2. use the where argument of the OpenForm method --- strCriteria = "SomeID = " & Me.cboSomeID

 

[CJ_London]

I actually use the openargs property to modify the recordsource before it is referenced in the load event to limit the number of records returned. Doesn't meet the OP's need for a find, but significantly reduces the volume of records in the form. e.g.

for example - just using OP's example, corrected as necessary

Stlinkcriteria = "[Calender Year]=" & Forms!Frmukmenu.CboxYear
DoCmd.OpenForm "FrmUKFOBDespatches", acFormDS, , , , , Stlinkcriteria

assume recordsource for FrmUKFOBDespatches is
SELECT * FROM Despatches ORDER BY somedate

and in the FrmUKFOBDespatches Open event

me.recordsource="SELECT * FROM (" & me.recordsource & ") WHERE " & me.openargs

 

[ebs17]

since loads the entire table/query and then moves to the row you want to work with

Maybe it is desired to have the whole table/query available and only set the focus specifically.
Measures are derived from what has to be done according to the workflow, not from individual preferences.

So that you don't have to work with the 250k table, you can also filter or use an appropriately pre-filtered table/query as the data source. But that is a different shoe.