By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,435 Members | 2,033 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,435 IT Pros & Developers. It's quick & easy.

Open form based on querie with parameters

100+
P: 759
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.RecordSource = 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 !
Apr 14 '11 #1
Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Apr 14 '11 #2

100+
P: 759
The form must be updated:

1) When I open it, exactly as I use this code:
Expand|Select|Wrap|Line Numbers
  1.     stLinkCriteria = "[Cod]=" & Me![Cod]
  2.     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
Apr 15 '11 #3

100+
P: 759
This is an example how I work now:

lstApartamenteContorizate.RowSource = _
"SELECT Apartamente.Cod, Apartamente.Apartament " & _
"FROM Apartament_situatie INNER JOIN Apartamente ON Apartament_situatie.Cod = Apartamente.Cod_Sit " & _
"WHERE (((Apartamente.Cod_Nod) = " & curCod_Nod & ") And ((Apartamente.Cod_Sit) = 1)) " & _
"ORDER BY Apartamente.Apartament;"


lstApartamenteContorizate 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.
Apr 15 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.