Open Form With a Where Clause (A2K SP1)

Topic

New Reply

I am creating an app where the user will open several forms in succession. One of the forms contains 2 subforms.

As I understand it, I cannot link the subforms to a key passed from the form unless I open the form with a keyed record.

This is a 2 part question:

1 Is there a way to view a specific record in a subform without linking from the master form or alternatively is there a way to open a linked subform from a non-linked master form?

2 What is the appropriate syntax for opening a form from another form when the record key consists of 2 fields?

Reply | Quote

Replies

>>As I understand it, I cannot link the subforms to a key passed from the form unless I open the form with a keyed record.<<

I'm not sure I understand this. But let me explain what you can do with subforms. When you place a subform on a form, it can be linked to the mainform via the Child/master linking properties. When you do this, the information on the subform will change whenever you change to a different record on the mainform. If you don't use the master/child linking, then you will manually have to tell the subform to requery. In this circumstance, the subform's recordsource is usually a query that includes a selection criteria based on a value on the mainform (or anywhere else for that matter).

When opening a form from a form, you use the DoCmd.OpenForm method. If you look in help, you will see the allowed parameters. One of these is the WHERE clause, which is like an SQL WHERE clause except you don't use the word "where". So, build your selection criteria the same way, using AND between the fields if more than one.

Reply | Quote

I am still not clear on the syntax for the where clause. All of the examples that I have seen are based on the search key being a field on the form that contains the Open Form command. Assuming that I want to Open a form with a query as the record source containing the field Key – what would the Open Form where clause look like if I wanted the search key to be a local variable rather than a control on the currently open form, e.g. instead of “Forms![Form Name]![Control Name] = [Key]” what should the where clause look like if the local variable in named Local?

Reply | Quote

Hi
Is this the sort of thing you are after.

docmd.OpenForm “frmName”,acNormal,[QueryNameHere optional if blank leave a comma],[SQLClauseHere without ‘WHERE’ optional],acFormEdit

Cheers
Geof

Reply | Quote

Well actually, I understand the syntax at that level and use this form of command often. My question had to do with th specific syntax for the SQL clause where the search key is not a bound control on a form.

Reply | Quote

If you’re trying to do that from a saved query, you can’t reference a form variable because queries don’t understand variables. You may be able to reference a module level variable declared public, since that makes it a property of the form. Then you could try Forms!Form.Variable as the syntax.

If you’re trying to reference an unbound control on a form, the syntax is identical to using a bound control.

Reply | Quote