Open form based on querie with parameters

[Mihail]

I have some global variables.
I use this variables to open a form based on a query with parameters.

Now I use the On Load event to prepare an SQL string which include values of the global variables then: FORM.RecordSour ce = SQL .
Works fine but, if I wish to make changes is a hard work.

The question is: Is here any way to pass values directly to the RecordSource query ?

The same question for a ListBos RowSource.

The link criteria using with DoCmd.OpenForm is not an option.

Thank you !

[TheSmileyCoder]

The question is at what TIME or tied to what EVENT do you wish to change the forms recordsource? It can be done at runtime, and not only in the LOAD event.

Same goes for combobox and listbox, with 1 small difference. While changing a forms recordsource, will trigger a requery automatically, it must be done manually (in code) for listbox and combobox.

[Mihail]

The form must be updated:

1) When I open it, exactly as I use this code:

    stLinkCriteria = "[Cod]=" & Me![Cod]
    DoCmd.OpenForm "FormToOpen", , , stLinkCriteria

But there are more variables (not only "Cod"), this variables are GLOBAL and, for some reasons, I can't use stLinkCriteria. Is too hard for my english to explain why.

2) At run time when is necessary (one of the global variables is change)

The ListBox must be populate when the containing form is open and at run time.

In both cases I like to define the .RecordSource (or .RowSource) at design time, using parametric queries, and, at run time, to pass values to this parameters (using VBA, of course, not manually in the InputBox) and requery.

Can be done ? How ?
The code example is great.

Thank you

[Mihail]

This is an example how I work now:

lstApartamenteC ontorizate.RowS ource = _
"SELECT Apartamente.Cod , Apartamente.Apa rtament " & _
"FROM Apartament_situ atie INNER JOIN Apartamente ON Apartament_situ atie.Cod = Apartamente.Cod _Sit " & _
"WHERE (((Apartamente. Cod_Nod) = " & curCod_Nod & ") And ((Apartamente.C od_Sit) = 1)) " & _
"ORDER BY Apartamente.Apa rtament;"


lstApartamenteC ontorizate is a ListBox.

If the global variable curCod_Nod is change, then I rerun this code. Works, as I say, ok. But, if I wish to change all SQL, is a work for an old chinese.