Two forms opening the same form

[MikeMairangi]

Hi. I am developing a call management tool to manage outbound telesales activities. On logging in the user is presented with a list of activities, ACTIVITY_LIST form, and can access the activity form, ACTIVITY, from this list. I have created a reminder pop-up form, REM_EVENT, so call backs can be actioned and want the user to have the ability to select the ACTIVITY form record that the reminder relates to. My issue is that the query that generates the record set from ACTIVITY_LIST passes a parameter ACTIVITY_NUM via Form.ACTIVITY_LIST.ACTIVITY_NUM however I want to use the same query to pull the reminder record by passing the ACTIVITY_ ID from REM_EVENT. I have this working by passing the variables via the form as follows;

WHERE (((ACTIVITY.ACTIVITY_NUM=Forms.[ACTIVITY_LIST.ACTIVITY_NUM) OR (ACTIVITY.ACTIVITY_NUM)=Forms.[REM_EVENT].ACTIVITY_NUM)

However running this from both forms creates a pop-up expecting the missing variable. What is the best way to sort this out? Thanks Mike

[rpeare]

There are several ways you can handle this.

Though I don't like doing it you can apply a filter on the fly using

formname.Filter = "Country = 'USA'"
formname.FilterOn = True

So that regardless of which form you open it from you can pass the filter from the form you're on to the form you're going to. Just be sure to remove the filter when you close the form and set the filteron to false.

Another way is to set the recordsource of the form being opened based on which form you're opening it form. you could set that with

something like

If SysCmd(acSysCmdGetObjectState, acForm, "FormName1") <> conObjStateClosed Then
    formname.recordsource = firstSQLstatement
else If SysCmd(acSysCmdGetObjectState, acForm, "FormName2") <> conObjStateClosed Then
    formname.recordsource = secondSQLstatement
endif

I haven't tested the code but you get the gist.

[MikeMairangi]

Thanks for this, it points me in the right direction,

Cheers

Mike

[MikeMairangi]

I wanted to use an existing query to run the sql statements. Is there a way of running a query from a conditional VBA statement that will pass different parameters to the query and then open a form based on the resulting recordset?

[rpeare]

If you're going to build a SQL statement there's no point saving it as a query. you can change the reports recordsource to be the SQL statement you generate (rather than the previous suggestion of a filter) when it runs and avoid having to write a query altogether, you'd just change the 'WHERE' clause of the SQL statment before attaching it to your report.

[MikeMairangi]

I wasn't going to build the SQL statement in VBA as I have it working succesfully in a Query. It's relatively complex and I can foresee syntax issues for me in rebuilding it. I wanted to change the exisitng Query criteria parameter, which currently takes the value from one specific form, to a generic parameter passed by the conditional statement in VBA.

i.e.

If SysCmd(acSysCmdGetObjectState, acForm, "FormName1") <> conObjStateClosed Then

Run Query with parameters from FormName1 and Open FormName3 with resulting recordset

else If SysCmd(acSysCmdGetObjectState, acForm, "FormName2") <> conObjStateClosed Then

Run Query with parameters from FormName2 and Open FormName3 with resulting recordset

End If

I don't know how to construct the run query and open form lines.