DoCmd.RunSQL in Access 2007
Posted on 2009-12-18
I have an Access 2007 DB that we are using as a project management/ticket tracking solution. My boss wants to be able to track how long different groups work on the project so we can start to trend and see where the bottlenecks are.
So, I have the main table called Issues, and a 2nd table called tblWithGroup. The structure for tblWithGroup is:
Issues_ID (FK to Issues table),
Group (linked to Issues table field called with_group),
The fields in Issues that come in to play are:
with_group (list box with list of groups)
group_ID links to tblWithGroup ID so we can match records
Step 1 should be, look and see if there is already an entry in Issues.group_ID
If not, add a new record to tblWithGroup and update group_ID
If group_ID = tblWithGroup.ID put an end_date THEN add a new record to tblWithGroup and update group_ID
Right now I am trying to do it with docmd.runsql ( DoCmd.RunSQL "UPDATE tblWithGroup SET End_Date = Date() WHERE me!group_ID = tblWithgroup.ID;"), but it doesn't accespt the VB of me!group_ID, and if I put it in, it says you are about to update 1 record. (got the code attached so you can see the whole thing)
How can I do a WHERE clause in VBA so that it will match group_id to tblwithGroup.ID and update the record?
I'm brand new to access and programming in general, so I am hoping I'm making sense!