Complex forms as subforms

[Cavern]

I have a complex form (in that it contains a lot of VB code and events) to display the information of various articles in my database. I would like to add some browsing options to the form (like a list of all articles on the left, and some different back/forward options), but I also need to preserve the form in its current state.
Rather than make a new copy of the form (and have to maintain TWO near-identical copies of the same form), I attempted to make a new form, with just the browsing list, and insert the old article information form as a subform on the left. However, it seems that any of the code from the original form which refers to Forms!Article Information!Article_ID ("Article Information" is the name of the original form) are not able to load the correct information, as I am prompted to supply it myself. Needless to say, I don't want the user to be prompted for the article number each time they open the form .

I'm wondering how I would go about opening the old form as a subform in such a way that I am not prompted each time. Or alternativley, if there's a better way to do what I'm trying to do, how should I do it?

 

[Pat Hartman]

When you make a form a subform, you need to change how you reference it.

Forms!mainformname!subformname.Form!controlname

 

[Superock]

Does it also aplly to...

When you make a form a subform, you need to change how you reference it.

Forms!mainformname!subformname.Form!controlname

So if I understand you correctly Pat, if I had a main form with a subform on it and the subform was populated with variables from the mainform and I then want to double click a record (or Multipule records) on the subform to pass the record(or Recordset) information to a report all I have to do is ref the recordset using Forms!mainformname!subformname.Form!controlname ?

 

[Cavern]

When you make a form a subform, you need to change how you reference it.

Forms!mainformname!subformname.Form!controlname

So I have to go through all the code in the original form and change any references from Froms!mainformname!controlName to Forms!mainformname!subformname.Form!controlName ?

This is exactly what I was hoping I wouldn't have to do, modify the original form. I was hoping there was some way to re-use the original form, without modification (or with a modification that would still alow it to open as a stand-alone form) so that I would avoid maintaining two copies of the same form, one with the VB code referencing the Me.ControlName and the other with the Forms!mainformname!subformname.Form!controlName ...

I'm guessing no-one knows any way to do this?

 

[Pat Hartman]

When the code is in the form's own class module, you should ALWAYS use "Me." to reference form objects. It is shorter, more efficient, and it provides intellisense which minimizes typos. The ONLY time you should ever use the more formal Forms!formname!controlname is from outside the form.

Code in a subform that refers to objects on its parent form should use "Me.Parent.".

 

[Cavern]

OKay, I think I found the reason that my form is messing up, its because on the form, it wasn't the VB code as I suspected, its a couple of list-boxes on the form that have a SQL statement of this type:

SELECT Categories.Name
  FROM Categories INNER JOIN ArticleCategories ON Categories.Category_ID=ArticleCategories.Category_ID
  WHERE (((ArticleCategories.Article_ID) Like [Forms]![Article Information]!Article_ID))
  ORDER BY Categories.Name;

The [Forms]![Article Information]!Article_ID is whats screwing it up, prompting the user for the value of Article_ID when this form is palced as a subform. I tried changing it to 'Me!Article_ID' but no dice. Any ideas?

 

[Pat Hartman]

1. "Like" is costly to process and so shouldn't be used unnecessarily. Your situation required "=" as the relational operator.
2. You can make a public function to return the variable. Each form would have to set the value in the variable and the query would use the function to retrieve it. I have posted code for this several times. It is very simple
1. dim the global variable
2. one line in each form to set the value
3. function to return the value

 

[Cavern]

Thank you. I'll look into doing it the way you mentioned, by creating the function. Thank you also for pointing out the 'Like' in my code.