combobox , select query, afterupdate event

[Guest]

Hello im sorta new

Im building a database to hold a list of Volunteers and a list of
Assignments that the Volunteers will be assigned to. So, I created a
"Volunteers" table (with "pkVolunteerID" as its primarykey) and a
"Assignments" table (with "pkAssignmentID" as the primarykey).


Each Volunteer can work on multiple Assigments but each Assignment can only
have ONE Volunteer working on it. So, in the "Assignments" table i created a
foreignkey called "fkVolunteerID" which is linked to the "Volunteers" tables
primarykey called "pkVolunteerID" in a relationship which allows multiple
Assignments per Volunteer but restricts ONE Volunteer per Assignment.


works great.


Then I created one form for each table, "VolunteerForm" and "AssignmetForm",
where the user can enter/view/edit values for each field of the table.
However, On the "AssignmentForm", i did something special, i made the
"fkVolunteerID" into a COMBO-BOX whose ROW SOURCE is a query that SELECTS
pkVolunteerID, Fname, Lname, DOB and Gender FROM the "Volunteers" Table WHERE
the "Status" field is either equal to "waiting" or "active".

Works great.


However, when the user makes his selection from the combo-box, id like the
combo-box to check the "status" field of that particular (volunteer) record.
And if the value is "waiting" then IT SHOULD BE CHANGED to "active".


Im new to VBA, and havent found a comprehensive tutorial on MS ACCESS VBA
syntax, structure, uses or examples. DOes anyone have any ideas?


Cheers,
WebDude

 

[Steve Schapel]

WebDude,

On the After Update event of the combobox, you could run an Update Query
to handle this. Something like...
CurrentDb.Execute "UPDATE Volunteers SET Status = 'active' WHERE
pkVolunteerID=" & Me.fkVolunteerID

There could be a problem if the user accidentally enters the wrong
Volunteer, or if an Assignment has the originally allocated volunteer
replaced by another, as in these cases I suppose you will want the
status put back to 'waiting' if the volunteer doesn't have another
assignment as well.

 

[Guest]

There could be a problem if the user accidentally enters the wrong
Volunteer.......

Steve, you have raised a very good point.
um, Ill see if i cant design it better.


WebDude out