Event Procedure - NewB Help

[rlmoon517]

I'm working with a Search & Rescue Foundation (KlaasKids.com) and I am trying to develop a small prototype MS Access Application that will be used to; 1) Register Volunteers to Search and 2) Assign Volunteers to Search Teams. I am NEW to Access, but I have a simple core application developed, but there is one function I would yet like to build and I am stuck ..... please help if you can. I hope I can explain this properly in the correct Access "lingo" - sorry if it's incorrect. Here goes.

The database only has 2 tables (people & tasks), people.lastname is bound to task.team_assignment. In the people table is the field people.out (defaults to no). This field indicates if a Volunteer Searcher has been assigned to a Search Team. If they have been assigned to a Search Team this needs to be changed to "Yes).

I have a form "Task & Team Assignments". This form operates against the task table, but I have a field (task.team_assignment) that has a table query that displays a people drop-down list that have a people.out that = "no". These are people available to be assigned to a Search Team. From the display list a name can be selected and the "lastname" is stored in the task.team_assignment field.

What I need to add (sorry it's taken a while to get to this, but I thought some background would help) ......

When the selection of the lastname is made on the form from the people list and posted to the task.team-assignment field I would like to trigger an "event procedure/code) to go to the people table and change the people.out value from "no" to "yes". This shows the person has been assigned to a Search Team and will show up on other Search Team inquiries and forms.

My problem .... I don't know VB syntax/commands or where the best position is to include the event-trigger, i.e. is "After Update" the best spot? It seems like I need a VB Update command point to the right people.lastname record and then change people.out from no to yes ........

If someone could point me to the right form trigger-position and provide me some starting VB code examples, I will fight my way to make this work - if it's possible!!

A BIG Thanks in advance to any help on this. Your assistance is greatly appreciated!

Ray Mooney
Pensacola, Fl
KlaasKids Foundation for Missing & Trafficked Children

 

[GinaWhipp]

I would think an UPDATE query would do the trick but without knowing your forms, tables and/or field names. Here are some samples...

Simple UPDATE query…

UPDATE YourTable SET YourTable.YourField1 = "Your Value";

Update a field in your table with values from another field in your table…

UPDATE YourTable SET YourTable.YourField1 = [YourField2];

Update a field based on a field on your form (TEXT)…
Note: The form must be open to perform this action!

UPDATE YourTable SET YourTable.YourField1 = "YourValue"
WHERE (((YourTable.YourField2)=[Forms]![YourFormNameGoesHere]![YourTextBoxOrComboBox]));

(NUMERIC)
Note: The form must be open to perform this action!

UPDATE YourTable SET YourTable.YourField1 = 1234
WHERE (((YourTable.YourField2)=[Forms]![YourFormNameGoesHere]![YourTextBoxOrComboBox]));

 

[rlmoon517]

.... Given that I'm a NewB on Access (and fumble with VB), the answer may be in the reply above, but it's Greek-to-me! Could someone advise (with example) of how to update a table/field based on the input-field in a form?

This may help;
1. table=people field=fullname * field=out (defaults to "No")
2. table=task field=team_assignment *

* These 2 fields contain the same data (example: John Doe) and I have theses fields "joined" with a relationship.

I have a Form that updates the task table, and when it does, I want to change the field=out that's in the people table to "Yes". The field=team_assignment is on the Form.

I hope this helps explain my need!

Thanks in advance for any help!
"struggling NewB!

 

[GinaWhipp]

Yes, I belive the TEXT exmple will work but there were no attachments. So can you please upload those two images hee so I can say for sure...

 

[rlmoon517]

Thanks, but I've been able to figure out how to do the update using an Access Update Query ..... took some time, looking at examples on the Internet. My "Light-Bulb-Moment" happened when I saw how you could change the view of the Access Query into a SQL view and back again to the Access Design view.

The data in the FullName & 'Task Assignment' is the same data (example; John Doe). This allowed me to compare the two fields.

Attached is screen-shot of the final 'Access Update Query' - Design View that worked.

 

[GinaWhipp]

I didn't realize that was an issue... Hmmm, next time I do that I should post instructions on how to use the SQL View. Glad you got it working.