Access Update Help

[neilmcdonald]

Hi,

I'm trying to write a simple database to keep track of the jobs our company works on. Each job is entered into the system and then allocated to an engineer at a later stage.

I've created a query to show only unallocated jobs, and a form based on that query. What I need to happen is when an engineer is selected from the drop down (linked to another table), the status of the job should change to allocated.

I've tried an update query, but it only seems to do one record at a time. I'd also liketo be able to refresh the form to show only unallocated jobs again.

Can anyone tell me the best way to do this?

Thanks,

Neil

 

[barny2006]

when an engineer is assigned to a job from a drop down box, is he assigned to one job? is one job = one record?
how many records should be updated once the engineer is assigned?

 

[neilmcdonald]

Thanks for the quick reply! one job = one record. one engineer will be assigned to each job, and an enginner will be assigned to several jobs.

One record should be updated every time an engineer is allocated.

If it's easier to add a button to the form which will update the status for all the records on the form, then that would be okay.

 

[barny2006]

this is a little contradictory:
quote:

Thanks for the quick reply! one job = one record. one engineer will be assigned to each job, and an enginner will be assigned to several jobs.
we know one job = one record
is one engineer assigned to one job? or one engineer is assigned to all the jobs? or several jobs?
if you want to update all the records, you can use this.
UPDATE my_table, SET allocation_field = "ALLOCATED"
without any condition.

 

[neilmcdonald]

Apolgies if my last post is contradictory - one engineer will be assigned to several jobs.

If the update statement you posted still applies, where should I put it?

Thanks for your help - I'm very new to this...

 

[barny2006]

you need to relate which jobs will that engineer is assigned. what determines which job(s) he will be assigned?

 

[barny2006]

if the engineer's name is on the job record, then you can use this:

UPDATE my_table, SET allocation_field = "ALLOCATED" WHERE job_engineer_name = input_box_engineer_name

you can put this code on the event tab of a button that should update the job record under onclick section.

 

[traingamer]

On the 'After update' event for the 'engineer' field, update the status.
Add a 'refresh' command button, using the wizard.

Are you using 'continuous form' view, 'datasheet' view or 'single form' view?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[neilmcdonald]

Thanks for all your help - It's working now. I guess I was making it a lot more complicated than it needed to be.

Thanks again,

Neil