Validation that only allows Weekdays!

[atiq]

I am trying to restrict the user to only enter date which is weekdays. so, it shouldn't allow a date that is weekend such as 08/04/07 (Sunday). Can someone help me with this issue. In short i want the user to enter a date which is Monday, Tuesday, Wednesday, thursday and Friday!

Additionally, i'm new to Databases so, could you tell me whether the validation rule given should be used during table design or on the form date field. Any help would be much appreciated.

[pks00]

ok, assuming this all done via a form
say u had a field called txtDate, this is where the user enters a date
each control have a number of event handlers, kinda like triggers in oracle

in access, txtboxes have a beforeupdate event handler, this allows u to validate and abort the update
to get the weekday, u can use the WeekDay function


so for example

private sub txtDate_BeforeUpdate(Cancel as Integer)
 
    Dim iWeekNo as integer
 
    iWeekNo = WeekDay(txtDate) 
    if iWeekNo = 1 or iWeekNo = 7 then  '1=sunday,7=saturday
        cancel = true       'set to true to abort update
        msgbox "Please enter mon-fri date"
    end if
end sub
 

[atiq]

It's you again! again u've sorted the problem, ur awesum a natural genueis u r! this was probably a piece of cake for u.

another thing i wanted to know is, how to automatically open a form based on a selected value in a combo box. In short, if da user selects 'Contact Parent' then i want it to automatically open another form.

Once again thanks for helping me out.

[pks00]

Hello again, happy to help out

Regarding your other question, u need to make use of the event handler called AfterUpdate. This is called each time a change is made

eg u have a combobox called cboMyCombo (it could be a textbox also, doesnt matter)

private sub cboMyCombo_AfterUpdate()
if Me.cboMyCombo = "Contact Parent" then
DoCmd.OpenForm "new form"
end if
end sub

[atiq]

Hello again, happy to help out

Regarding your other question, u need to make use of the event handler called AfterUpdate. This is called each time a change is made

eg u have a combobox called cboMyCombo (it could be a textbox also, doesnt matter)

private sub cboMyCombo_AfterUpdate()
if Me.cboMyCombo = "Contact Parent" then
DoCmd.OpenForm "new form"
end if
end sub

I tried using this code but nothing seems to happen.it doesn't even display any error message! the name of the combo box is cmbAction and i even tried using this code:

Private Sub cmbAction_AfterUpdate()

If Me.cmbAction = "Contact Parents" Then
DoCmd.OpenForm "frmSendEmail", acNormal, acFormEdit, acWindowNormal
End If

End Sub

In addition to this, for the same combo box i want the command button named cmdPrintDetentionSlip to be visible if the value Detention is selected.

Hope u can make my day today as well!

[pks00]

How many columns do u have in your combo?

try this to see what is selected

Private Sub cmbAction_AfterUpdate()

msgbox "Selected is >" & Me.cmbAction & "<"

If Me.cmbAction = "Contact Parents" Then
DoCmd.OpenForm "frmSendEmail", acNormal, acFormEdit, acWindowNormal
End If

End Sub


when u run it, what is displayed in the message box

[atiq]

How many columns do u have in your combo?

try this to see what is selected

Private Sub cmbAction_AfterUpdate()

msgbox "Selected is >" & Me.cmbAction & "<"

If Me.cmbAction = "Contact Parents" Then
DoCmd.OpenForm "frmSendEmail", acNormal, acFormEdit, acWindowNormal
End If

End Sub

when u run it, what is displayed in the message box

The message displayed is Selected is >4<

[pks00]

this means that u are using multiple columns in your combo
what is the sql for the combo

if the description is the 2nd field in the combo, then try this

if cmbAction.Column(1) = "Contact Parents" then


cmbAction.Column(0) refers to first column, in this case, most likely 4
cmbAction.Column(1) refers to second column
cmbAction.Column(2) refers to third column etc

eg
select f1, f2, f3
from mytable

column(0) = f1
column(1) = f2
etc

[pks00]

Assuming its the 2nd column

Private Sub cmbAction_AfterUpdate()
 
    cmdPrintDetentionSlip.Visible = False
    If Me.cmbAction.Column(1) = "Contact Parents" Then
        DoCmd.OpenForm "frmSendEmail", acNormal, acFormEdit, acWindowNormal
    Elseif Me.cmbAction.Column(1) = "Detention" then
        cmdPrintDetentionSlip.Visible = True
    End If
 
End Sub
 

Normal practice, its not good to use hardcoding of description names as they are subject to change, better to check against the numbers