open form that depends on parameter query

[kittyyo]

Hi all!

I've got a form that depends on a query having the following two parameters:
month, year (integer).
If the user opens the form from the main access window, he has to enter both parameters before the form opens, and this is intendedly so.
However in some circumstances I'd like the user to open the form out of a "welcome form", where month and year are already given in text boxes. Then the user shall not be prompted to enter values for month and year again.

The queries that the forms depend on all have the form

SELECT something FROM sometable
WHERE DatePart("m";MyDate) = [month?] AND
DatePart("yyyy";MyDate) = [year?];

I know that I could let the form depend on such a query:

SELECT something FROM sometable
WHERE DatePart("m";MyDate) = [WelcomeForm.txtMonth.Value]
AND DatePart("yyyy";MyDate) = [WelcomeForm.txtYear.Value];

but then it's not possible any more to open the form from the main access window and entering the parameters by hand.

So what can I do to set the parameters [month?] and [year?] programmatically?

Thanks a lot,
Anne

 

[spizotfl]

one thing you could try, and i am not sure how well it would work, is using openargs part of the docmd.openform. set openargs to "needdate" if opened from where you want them to enter the date and something else (or nothing) when you want to prefill the date. then on the form based on the queries on load, use an if...then to check the value of openargs and change the forms recordsource between a query that uses the parameters and one that uses the dates you want it to use.

 

[kittyyo]

Thanks spizotfl! Now that I've played around for an hour a really nice solution popped up:

I created to public functions, GetMonth() and GetYear() as follows (trimmed-down):

Public Function GetMonth() As Integer
  If CurrentProject.AllForms("Welcome").IsLoaded Then
    GetMonth = Form_Welcome.txtMonth.Value
  Else
    GetMonth = InputBox("Month?")
  End If
End Function

Public Function GetYear() As Integer
  If CurrentProject.AllForms("Welcome").IsLoaded Then
    GetYear = Form_Welcome.txtYear.Value
  Else
    GetYear = InputBox("Year?")
  End If
End Function

And then I changed the WHERE clause in my query to

WHERE DatePart("m";MyDate) = GetMonth() AND
DatePart("yyyy";MyDate) = GetYear();

Works like a charm when opening the form that depends on the query!