Solved

MS Access Need help with VBA where SQL statement

Posted on 2013-01-08
3
298 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
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now