Filter a subform

[Microsoft]

Access 2007

i want to be able to ADD and EDIT existing records in a Subform.

When I click a button I want to pass the RefNo to Open a form that is
filtered with the value stored in a variable. The subform would also be
filtered to include on the records relating to the value stored in the
variable.
It is important that I can add new records in the subform and edit existing
ones.

How can I do this?

Thanks
A

 

[strive4peace]

Here is the syntax for the OpenForm action:

DoCmd.OpenForm FormName, View, FilterName, WhereCondition, DataMode,
WindowMode, OpenArgs

so, you can do something like this:

DoCmd.OpenForm "FormName",,, "RefNo_fieldname=" & RefNo,,,RefNo

WHERE
RefNo_fieldname is the name of the field you want to match (make sure it
is ON the form, even if Visible=No)

if RefNo is text, you will need to delimit it
"RefNo_fieldname='" & RefNo & "'"

~~~

on the popup form, you may need to set RefNo_fieldname when you add records:

on form BeforeInsert

'~~~~~~~~~
me.RefNo_fieldname = cLng(me.OpenArgs)
'~~~~~~~~~

if RefNo is a long integer

if it is text:

'~~~~~~~~~
me.RefNo_fieldname = me.OpenArgs
'~~~~~~~~~

If you are using Access 2000, there is no OpenArgs for OpenForm and you
will can reference a (maybe hidden) control on your other form


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*