Update query help...

[Eugene Wohlfarth]

Greetings all,

My questiioin may seem simple but it has me puzzled.

I have a datbase set up to track cases. I have direct access to the table
restricted.

I have a general query that prompts a user for the case number.If they need
to change the name of the person assigned to a case, can an update query be
used to prompt for the new person assigned to the case? Can that data be
selected from a dropdown?

Or am I going about this in the wrong way?

Thanks in advance,

Eugene

 

[Larry Daugherty]

"Wrong" might be too harsh but there may be more elegant ways to get
the job done:

Your Form should be based on a Query which includes the target table.
At the point where your question begins, your Form is showing the
target record with the currently assigned case worker's name in the
appropriate textboxes.

Use a combobox based on a Query that returns the sorted list of all
case workers. Call it cboFindCaseWorker. The combobox should have
Autoexpand enabled so that it will help you search. When you find the
record for the case worker, select it. The After_Update event of
cboFindCaseWorker would then load the appropriate textboxes on the
Form with the correct name data. The only tricky thing to bear in
mind is that while references to the columns in the combobox's
properties is "1" based, the columns collection is "0" based.

The code in the After_Update event might look like the below. Change
the names of things to suit the names you are already using. Assuming
an Autonumber primary key in cboFindCaseWorker.column(0) ...

me!txtFirstName = me!cboFindCaseWorker.column(1)
me!txtLasttName = me!cboFindCaseWorker.column(2)

You could use cboFindCaseWorker to enter the case worker's name in the
first place, cutting down on typing and typing errors.

HTH