Web Development·11,919 discussions

How to call a form from multiple locations in Access 2007?

Locked

I created an application in Access 2007 that allows a user to look up account information either with account number, name or address. All of these ultimately display the same group of forms and subforms. Those forms and subforms display the data by running queries using parameters passed by the calling form i.e. [FORMS]![frmAcctNumber].[txtAcctNumber]
The question is this. If the look up by name or address, there is an intermediate step because they get a list of similar names and they chose a record which passes the account number to the same screen as the look by account number screen. But the criteria is different. Instead of [FORMS]![frmAcctNumber].[txtAcctNumber] it is now [FORMS]![frmName].[txtAcctNumber] or [FORMS]![frmAddress].[txtAcctNumber] . Right now I have this working by have the different look up methods call different but identical forms that use different queries. This seems like a nightmare to maintain. I tried to have one query that uses OR in the criteria but when I do this, I always get a pop asking for the value from the look ups I’m not using, meaning if I look up by Account Number, the application asks me for the value for Names and Address ([FORMS]![frmName].[txtAcctNumber] or [FORMS]![frmAddress].[txtAcctNumber] ). How do I get around this?

Topic

Clarifications

In reply to How to call a form from multiple locations in Access 2007?

Clarifications

Check if there is a value….

In reply to How to call a form from multiple locations in Access 2007?

Look up Nz().

Nz(variablename) where variablename is the name of the control you are using for the query. This will check If it it null (ie has no value as you aren’t using it for this particular query), combining this checking with IIf statements can shape your query to only search on the value(s) you supplied, and not ask for other values (because if they are null they wont be included in the query). And you will only need one copy of a form or report which can be invoked by different calls with different query criteria.

Hope that helps.
Not entirely sure it makes sense to me now I read it back.

[Author]

Replies