Updating a "Hire Count" with a Macro

[GrandMasterTuck]

[SOLVED] Updating a "Hire Count" with a Macro

Hi all... I have a database with two tables. I want to have a macro run when I set a certain value on a new record in one of the tables, and I want the macro to update a value in the other table by increasing it one. The tables are linked via EmployeeName, and referential integrity is enabled.

tblEmployees: [EmployeeName][Count]
tblAssignments: [EmployeeName][AssignmentDate]

I have a query that shows me the employees, but sorted by Count (low to high), and I use a continuous form to display them. When I double-click a record on this form (one of the names), it adds a record to tblAssignments for that person, and writes today's date to the AssignmentDate column.  I then pick a Code from an embedded drop-down list.  Here's what i want to do:

Every time a code "QR" is set in tblAssignments, that runs a macro to go to tblEmployees and add 1 to [Count] for that employee. But if any other code is selected, it does nothing.

I can't just use a query to count the number of records for various reasons too complicated to bore you with.  I need to have that table column update when that code is selected, so that the records in tblAssignments keep updating the [Count] in tblEmployees over time, and so I can run a separate Macro to 'reset' the [Count] field to zeros without losing the records in tblAssignments.  

Can anyone give me an idea on how to do it?  I've tried several things, and none of them work (either that, or I've done each of the several things incorrectly).

Thanks a million!

 

[plog]

I can't just use a query to count the number of records for various reasons too complicated to bore you with.

Bore me with it.

 

[GrandMasterTuck]

Bore me with it.

[Edit] Long-winded explanation as to why this trick is needed was removed [/Edit]

UPDATE! I ALMOST got it to work using the following SQL statement:

UPDATE tblEmployees SET tblEmployees.Count=tblEmployees.Count+1 Where tblEmployees.EmployeeName = sbfmScheduledAssignments.Name

But I get a parameter value prompt that's asking me to supply "Name", so it's not getting it from the sbfmScheduledAssignments [Name] field. sbfmScheduledAssignments is a subform on frmMainForm... did I get the syntax wrong? Can I compare those two things, the column in a table with a field on a form like that?

 

[plog]

I apologize for doubting you. I've been numbed by people on this board who insist that the correct way is impossible and thus need a hack. You actually do.

You are going about it the right way by attaching it to a form. That SQL you posted, is it in VBA or is that from an actual query object? If its in VBA then you need to open and close the SQL string and put the value of the form in and then reopen the string. Like this:

strSQL="UPDATE tblEmployees Set [Count]=([Count] + 1) WHERE EmployeeName='" & sbfmScheduledAssignments.Name & "'"

Then when it gets evaluated it has the value of the field you want instead of the actual string of data that you are using to reference it.

Another issue is your field names. 'Count' and 'Name' are both reserved words and screws things up when you try and write queries or code with them. I recommend renaming both of them by prefixing it with what the data is for (i.e. QRCount, EmployeeName).

 

[GrandMasterTuck]

Plog, you are the man, sir! Thanks so very much! I apologize for those that might read his reply and wonder what he meant by his reference to 'needing a hack.' I edited the reply he's referring to, and removed that bit where I explain why, and therein is where he found that I really did need the hack.

And he solved the problem. Thanks again!