VBA Help please

  • Thread starter Thread starter Steve Goodrich
  • Start date Start date
S

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
 
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.
 
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.
Click to expand...

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

Tony
 
Back
Top