Solved

MS Access VBA Questions

Posted on 2013-01-08
8
226 Views
Last Modified: 2013-01-08
The code below inserts start and end dates for a employee with a reason to produce a report that lists events.

How do I add the following features to the code below?

If cboemp (employee is not selected) display message " Please select employee"
If cboreason (reason is not selected) display message " Please select reason"
If txtEnd (end date is not selected) display message " Please select end date"
If txtStart (start date is not selected) display message " Please select start date"

I.E. Don't perform any action until all fields are filled in.

I would also like to add a delete button to delete records according to start and end date.


Private Sub btnUpdate_Click()
Dim strSQL As String
Dim dtmNextDate As Date
dtmNextDate = Me.txtStart
    Do Until dtmNextDate = Me.txtEnd + 1
        strSQL = "INSERT INTO tblEmpTimeOff (etoDate,eto_EmpID,etoReasonCode) VALUES(" & "#" & dtmNextDate & "#" & ", " & Me.cboEmp & ", " & Me.cboReason & ")"
        CurrentDb.Execute strSQL, dbFailOnError
        dtmNextDate = dtmNextDate + 1
    Loop
    Me.cboEmp = ""
    Me.cboReason = ""
    Me.txtEnd = ""
    Me.txtStart = ""
    MsgBox "Input Recieved, Thank You"
End Sub

Open in new window

0
Comment
Question by:DJPr0
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 400 total points
ID: 38754745
try this

Private Sub btnUpdate_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.cboReason.listindex=-1 then
	msgbox " Please select reason"
	me.cboReason.setfocus
	exit sub
end if

if me.txtEnd & ""="" then
   msgbox " Please select end date"
   me.txtend.setfocus
   exit sub

end if

if me.txtstart & ""="" then
   msgbox " Please select start date"
   me.txtstart.setfocus
   exit sub

end if

dtmNextDate = Me.txtStart
    Do Until dtmNextDate = Me.txtEnd + 1
        strSQL = "INSERT INTO tblEmpTimeOff (etoDate,eto_EmpID,etoReasonCode) VALUES(" & "#" & dtmNextDate & "#" & ", " & Me.cboEmp & ", " & Me.cboReason & ")"
        CurrentDb.Execute strSQL, dbFailOnError
        dtmNextDate = dtmNextDate + 1
    Loop
    Me.cboEmp = ""
    Me.cboReason = ""
    Me.txtEnd = ""
    Me.txtStart = ""
    MsgBox "Input Recieved, Thank You"
End Sub

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38754756
Try this...

Private Sub btnUpdate_Click()
Dim strSQL As String
Dim dtmNextDate As Date
dtmNextDate = Me.txtStart

If cboemp & "" = "" then

     msgbox  " Please select employee"
     me.cboemployee.setfocus
      exit sub
end if

If cboreason & "" = "" then

     msgbox  " Please select reason"
     me.cboreason.setfocus
     exit sub

end if

If txtEnd & "" = "" then

     msgbox  " Please select end date"
     me.txtEnd.setfocus
      exit sub

end if

If txtStart & "" = "" then

     msgbox  " Please select start date"
     me.txtStart.setfocus
      exit sub

end if



    Do Until dtmNextDate = Me.txtEnd + 1
        strSQL = "INSERT INTO tblEmpTimeOff (etoDate,eto_EmpID,etoReasonCode) VALUES(" & "#" & dtmNextDate & "#" & ", " & Me.cboEmp & ", " & Me.cboReason & ")"
        CurrentDb.Execute strSQL, dbFailOnError
        dtmNextDate = dtmNextDate + 1
    Loop
    Me.cboEmp = ""
    Me.cboReason = ""
    Me.txtEnd = ""
    Me.txtStart = ""
    MsgBox "Input Recieved, Thank You"
End Sub 

Open in new window

0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 50 total points
ID: 38754794
<<I would also like to add a delete button to delete records according to start and end date.>>

The Delete button code would be the same overall.  You'd just need to use a different SQL String:


       
strSQL = "DELETE * FROM  tblEmpTimeOff WHERE etoDate BETWEEN #"  & me.txtStart & "# AND #" & me.txtEnd & "#"  

Open in new window

0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 50 total points
ID: 38754815
Giving users such a powerful delete button is asking for trouble.  I'd think twice about about how you deal with that situation.  

In general, I don't allow users to delete anything and simply mark records as 'deleted' and exclude them from all data sources.
0
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)

 

Author Comment

by:DJPr0
ID: 38754898
Is mark records as deleted different from this statement:
strSQL = "DELETE * FROM  tblEmpTimeOff WHERE etoDate BETWEEN #"  & me.txtStart & "# AND #" & me.txtEnd & "#"  

Open in new window


Does mark records as deleted - have a restoring option?
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 400 total points
ID: 38754922
<Does mark records as deleted - have a restoring option? >

Yes,

But to mark records, you will use an update query, something like this

strSql="Update tblEmpTimeOff set [nameoffield]='InActive' WHERE etoDate BETWEEN #"  & me.txtStart & "# AND #" & me.txtEnd & "#"
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 400 total points
ID: 38754933
or use a Yes/No field named [InActive]

strSql="Update tblEmpTimeOff set [InActive]= -1 WHERE etoDate BETWEEN #"  & me.txtStart & "# AND #" & me.txtEnd & "#"

to restore the records, just reverse the process

strSql="Update tblEmpTimeOff set [InActive]= 0 WHERE etoDate BETWEEN #"  & me.txtStart & "# AND #" & me.txtEnd & "#"
0
 

Author Closing Comment

by:DJPr0
ID: 38754995
Thanks capricorn1!
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)

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

16 Experts available now in Live!

Get 1:1 Help Now