Open a form from a continuous form based on two field values

[biganthony via AccessMonster.com]

Hi,

In working out another problem with opening a form from a continuous form
(reported in another thread), I thought of the following case and would like
to know if the following scenario is possible:

Let's say you have a continuous form with three fields: Employee, Jobs, Dept.
This form is not a data-entry one, it would just display the overall data.

The "Dept" field on this continuous form could have the following values:
Marketing, Advertising, Buying, Selling. (Each of which coresponds to a form
with these names).

So sample data could look like this:

ID, Jobs, Dept
BRS, 12A, Advertising
HYT, 7Y, Advertising
JUH, GG, Selling
ASD, yy, Buying


So if I double-clicked the value "yy" above, it would open the "Buying" form
and set the focus to the "yy" field in that form.

If I double-clicked the value "12A" above, it would open the "Advertising"
form and set the focus to the "12A" field in that form.

I would like to know if this scenario would work and how it could be
implemented in VBA.

Thanks

Anthony

 

[Albert D. Kallal]

ID, Jobs, Dept
BRS, 12A, Advertising
HYT, 7Y, Advertising
JUH, GG, Selling
ASD, yy, Buying


So if I double-clicked the value "yy" above, it would open the "Buying"
form
and set the focus to the "yy" field in that form.

In your example, the "yy" field is the jobs field...right?

I not sure you should have a diffent form for Advertising, Selling, and
Buying
(it don't look correct from a relational data design point of view. You
can't build relational data joins based on a field that represents the
table...your designs need to be based on fields that represents a column in
a table...not an actual table name. However, lets just leave this issue for
another day).

Your code could would look like:

docmd.OpenForm me!Dept
forms(me!Dept).Jobs.SetFocus

The above code assumes that your Dept column is the actual same name as the
form used. If not, then would have to code it.

eg:

select case me!Dep

case "Selling"
docmd.Openform "frmSelll"
forms!frmSell.Buy.SetFocus
case "buying"
docmd.Openform "frmBuy"
forms!frmBuy.Buy.SetFocus

end select

 

[biganthony via AccessMonster.com]

I worked it out.


Dim strWhere As String
Dim strForm As String

strWhere = "[Jobs] = '" & Me.Jobs & "'"
strForm = Me.Dept

DoCmd.OpenForm strForm, acNormal, , strWhere, acFormEdit, acWindowNormal