Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Need help with VBA where SQL statement

Posted on 2013-01-08
3
Medium Priority
?
341 Views
Last Modified: 2013-01-08
How can I add "employee" (Me.cboEmp) to the delete statement?

I.E. I need to delete records by date & employee, in lieu of just dates because all users with the specified date will be deleted.


Private Sub Command103_Click()
Dim strSQL As String
Dim dtmNextDate As Date

If Me.cboEmp.ListIndex = -1 Then
    MsgBox " Please select employee"
    Me.cboEmp.SetFocus
    Exit Sub
End If

If Me.txtEnd & "" = "" Then
   MsgBox " Please select start date"
   Me.txtStart.SetFocus
   Exit Sub

End If

If Me.txtStart & "" = "" Then
   MsgBox " Please select end date"
   Me.txtEnd.SetFocus
   Exit Sub

End If

dtmNextDate = Me.txtStart
    Do Until dtmNextDate = Me.txtEnd + 1
    
        strSQL = "DELETE * FROM  tblEmpTimeOff WHERE etoDate BETWEEN #" & Me.txtStart & "# AND #" & Me.txtEnd & "#"
        CurrentDb.Execute strSQL, dbFailOnError
        dtmNextDate = dtmNextDate + 1
    Loop
    Me.cboEmp = ""
    Me.cboReason = ""
    Me.txtEnd = ""
    Me.txtStart = ""
    MsgBox "Records Deleted, Thank You"
End Sub

Open in new window

0
Comment
Question by:DJPr0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38755541
strSQL = "DELETE * FROM  tblEmpTimeOff WHERE (etoDate BETWEEN #" & Me.txtStart & "# AND #" & Me.txtEnd & "#) AND employee = " & chr(34) &  Me.cboEmp & chr(34)

Open in new window



Or if employee is a numeric ID:

strSQL = "DELETE * FROM  tblEmpTimeOff WHERE (etoDate BETWEEN #" & Me.txtStart & "# AND #" & Me.txtEnd & "#) AND employee = "   & Me.cboEmp

Open in new window

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 38755544
What is the field that holds the employee identifier in tblEmpTimeOff?

AW
0
 

Author Closing Comment

by:DJPr0
ID: 38755917
Thanks mbizup!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question