CODEWITHSUNDEEP
sqlms-accessvba
Elaglo
Elaglo

Reputation: 23

Access VBA SendKeys alternative

I just inherited a personnel database at work that has not been touched in a few years and they want me to make it functional. I've been able to update most of the things but I've run into an issue with a macro that was input that uses the SendKeys action:

Private Sub Command3_Click() 
On Error GoTo Err_Command3_Click 

  DoCmd.OpenTable "tblEmployees", acNormal, acEdit
  DoCmd.GoToControl [employee]
  DoCmd.FindRecord Forms![EmployeeRemoval]![cboEmpSelect], 
  DoCmd.GoToControl [pastemployee]
  SendKeys "1~", True
  DoCmd.Close acTable, "tblEmployees"

Exit_Command3_Click: 
Exit Sub 

Err_Command3_Click: 
  MsgBox Err.Description 
  Resume Exit_Command3_Click

At this point with Access 2010 it just sends the program into a loop opening and closing the table while also turning Num Lock on and off. I figured the best replacement would be an Update statement, but am not sure what the Where criteria would be to update the single cell that needs updating (in this case only the PastEmployee cell for the one employee in question at any given time would need to update to True instead of False). In the macro it was referencing (through the FindRecord command) a ComboBox in which the employee is selected that queried Last Name, First Name, and employee number from the employee table.

db.Execute Update tblEmployees Set [PastEmployee] = 1 Where XXXXXXXX

Any help in either a way to make the SendKeys command just work or to get the update statement working would be helpful.

Upvotes: 2

Views: 1701

Answers (1)

June7
June7

Reputation: 21379

The posted code is not a macro, it is VBA. Macros in Access are very different.

Users should not interact with tables and queries, just forms and reports.

In Access Yes/No field, 0 is False and -1 is True.

Consider:

Private Sub Command3_Click() 
    On Error GoTo Err_Command3_Click 

    CurrentDb.Execute "UPDATE tblEmployees SET PastEmployee=True WHERE employee=" & Me.cboEmpSelect

Exit_Command3_Click: 
    Exit Sub 

Err_Command3_Click: 
    MsgBox Err.Description 
    Resume Exit_Command3_Click
End Sub

If the form were bound to tblEmployees and focus on the record to edit, instead of the UPDATE action, simply: Me!PastEmployee = True. Although, if the record is viewed on form, user just needs to click checkbox bound to PastEmployee and no code would be needed.

Upvotes: 1

Related Questions