visual basic within Access If...Then

[arabella79]

I have a form that's based on a query. I'm trying to make the form
only accept hours worked if there is a pay rate already entered in
another form. If not the form will return a message box notifiying the
user that there is no corresponding pay rate for the hours entered,
then return the fields to null values. The problem is that my code
only works for the A rate and not for the C rate. I think my structure
or my thinking about how to do this wrong. Any help you can provide is
appreciated. Below is the code that I have so far:

Private Sub Form_AfterUpdate()
If ACheck = "bad" Then
MsgBox "You must first enter an A rate in the employee's edit form
before entering hours worked"
Me.asmonday = Null
Me.aomonday = Null
Me.admonday = Null
Me.astuesday = Null
Me.aotuesday = Null
Me.adtuesday = Null
Me.aswednesday = Null
Me.aowednesday = Null
Me.adwednesday = Null
Me.asthursday = Null
Me.aothursday = Null
Me.adthursday = Null
Me.asfriday = Null
Me.aofriday = Null
Me.adfriday = Null
Me.assaturday = Null
Me.aosaturday = Null
Me.adsaturday = Null
Me.assunday = Null
Me.aosunday = Null
Me.adsunday = Null
Me.asx = Null
Me.aox = Null
Me.adx = Null
End If
If CCheck = "bad" Then
MsgBox "You must first enter a C rate in the employee's edit form
before entering hours worked"
Me.csmonday = Null
Me.comonday = Null
Me.cdmonday = Null
Me.cstuesday = Null
Me.cotuesday = Null
Me.cdtuesday = Null
Me.cswednesday = Null
Me.cowednesday = Null
Me.cdwednesday = Null
Me.csthursday = Null
Me.cothursday = Null
Me.cdthursday = Null
Me.csfriday = Null
Me.cofriday = Null
Me.cdfriday = Null
Me.cssaturday = Null
Me.cosaturday = Null
Me.cdsaturday = Null
Me.cssunday = Null
Me.cosunday = Null
Me.cdsunday = Null
Me.csx = Null
Me.cox = Null
Me.cdx = Null
End If
End Sub

 

[Marshall Barton]

I have a form that's based on a query. I'm trying to make the form
only accept hours worked if there is a pay rate already entered in
another form. If not the form will return a message box notifiying the
user that there is no corresponding pay rate for the hours entered,
then return the fields to null values. The problem is that my code
only works for the A rate and not for the C rate. I think my structure
or my thinking about how to do this wrong. Any help you can provide is
appreciated. Below is the code that I have so far:

Private Sub Form_AfterUpdate()
If ACheck = "bad" Then
MsgBox "You must first enter an A rate in the employee's edit form
before entering hours worked"
Me.asmonday = Null
Me.aomonday = Null [ . . . ]
End If
If CCheck = "bad" Then
MsgBox "You must first enter a C rate in the employee's edit form
before entering hours worked"
Me.csmonday = Null
Me.comonday = Null [ . . . ]
End If
End Sub

The form's AfterUpdate event is too late, the record has
already been saved. Besides, if you let uses fill in all
those values and then erase then, they might tar and feather
you after only a couple of these mistakes.

Better to disable the controls that you don't wnnt them to
use and then enable them after the required field is filled
in.

This is fairly easy to do if you set each of those control's
Tag property to some indicator string such as A and/or C so
you can use a procedure like:

Sub SetEnabled(RateGrp As String, OnOff As Boolean)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag Like "*" & RateGrp & "*" Then
ctl.Enabled = OnOff
End If
Next ctl
End Sub

Then use the form's Current event to disable the controls
for new records:

If Me.NewRecord Then
SetEnabled "A", False
SetEnabled "C", False
End If

And use the each rate text box's AfterUpdate event to check
if the rate is valid and if it is, enable the controls:

If txtRateA < xx And txtRateA > yy Then
SetEnabled "A", True
End If

 

[arabella79]

That sounds like a good idea, I'm pretty new to VB so I was wondering
if you could take a look at the database. It's a zipped file posted
at: http://www.wikiupload.com/comment.php?id=22090. I'm sure there
are a milion ways to improve this, but I'm just trying to clean up a
small part of a massive payroll that's currently being done manually
and with excel spreadsheets Thanks in advance.

 

[Marshall Barton]

Sorry, I don't have time to provide personal consulting
services. Take a shot at getting my suggestion to work and
post back with any specific questions that you can not find
an answer to in VBA Help.