VBA Help please

[Steve Goodrich]

Hi folks, hope you can help

Using Access 97

Two of the fields on my form are "Manager" & "Dept"

When I select a "Dept". I would like to populate the "manager" field with
the manager responsible for the dept.
Not sure if it is correct as I am very new to this, but it seems to work.

if me.dept = "SO - Sub dept1_name" then
me.manager = "manager_name"

Some managers are responsible for more than one dept. so I typed this

if me.dept = "dept_name" or me.dept = "dept_name2" then
me.manager = "manager_name"
both work fine.

I have two questions:

Can I use a wild card?, or is there another method when numerous departments
start the same
For example, I have 25 depts starting with their dept code followed by a sub
dept
SO - Sub dept1
SO - Sub dept2
SO - Sub dept3
and so on,


The sub depts are always changing so I would have to alter the code each
time, but the dept code rarely changes so if I enter the code
something like DEPT = "SO*" then

Each time the sub dept changes the code would still work

2nd question is:
how do I enter the syntax to delete the manager field when the user deletes
the dept field

Tried a few options but can't get the syntax correct

something like
if me. dept isnull then
me.manager is null

Hope all this make sense

Thanks in advance for your help

Steve

 

[Douglas J. Steele]

You probably shouldn't be using VBA at all for this. You should have a table
that contains the information of which manager is responsible for which
departments, and join that table to your existing table.

 

[Tony Toews]

You probably shouldn't be using VBA at all for this. You should have a table
that contains the information of which manager is responsible for which
departments, and join that table to your existing table.

"join that table to your existing table" using a query.

Tony