complex(?) code?

[Matizo]

Hello,

I'm looking for a code which will be checking the date of birth entered into the DOB field (i use dd/mm/yyyy format). If the person is over 18 years old the value in the 'Permission' field should change to 'No Needed' BUT if a person is under 18's the msg box should appear eg 'Please check the 'Permission'.

It would be even better if the dialogue box would appear instead of msg box asking 'Does the person have the permission?' and three buttons YES, NO and CANCEL. if user would chose YES the value in the Permission field would change to YES if user would chose NO the value in the Permission field would change to NO. If user would chose CANCEL the event would be cancelled and the value entered at the beginning to the DOB field would be deleted.

Any ideas would be appreciated.

Regards

 

[modest]

I have a problem understanding what you want because of the terminology you are using. 'Field' generally refers to a column (field) in a table, but I'm assuming you're using it to reference a textbox on a form.

Public Sub Age()
    Dim strDate     As String
    
    
  'Initialize Variables
    strDate = Me.txtDOB & ""
    
    If strDate & "" = "" Then
        Exit Sub                            'If textbox is empty, leave
    End If
    
    Select Case Round(DateDiff("d", strDate, Now) / 365, 0)
        Case Is < 18
            Select Case MsgBox("Does the person have permission", vbYesNoCancel)
                Case vbYes
                    Me.txtPermission = "YES"
                Case vbNo
                    Me.txtPermission = "NO"
                Case vbCancel
                    Me.txtPermission = Null
                    Me.txtDOB = Null
                    Exit Sub
            End Select
        Case Is >= 18
            Me.txtPermission = "No Needed"
        Case Else
            MsgBox "Date is Null"
    End Select

End Sub

 

[Matizo]

Yes, sorry, by saying field I ment textbox

Thanks a lot for a code I will try use it,
I belevie I should enter something between "" but I'm affraid I don't know what. Could you write exactly how that should looks like or give an example?

Thanks ,
matt

 

[Bat17]

I belevie I should enter something between ""

Nope
They are just there to make sure there is somthing in the field

Peter

 

[Matizo]

Well, I tried to use it but nothing happens

I attached my database if you could take a look.

Thanks,

 

[modest]

Almost there, but you have to call the subroutine somehow. I suggest either making a button to call it, calling it after you update the birthday, or calling it when you click on the permissions combo box. I have it so that it is called after you update the birthday. Your form's code should look similar to the following:

Public Sub Age()
On Error GoTo Err_Handler
    Dim strDate     As String
    
    
  'Initialize Variables
    strDate = Me.DOB & ""
    
    If strDate & "" = "" Then
        Exit Sub                            'If textbox is empty, leave
    End If
    
    Select Case Round(DateDiff("d", strDate, Now) / 365, 0)
        Case Is < 18
            Select Case MsgBox("Does the person have permission", vbYesNoCancel)
                Case vbYes
                    Me.Permission = "YES"
                Case vbNo
                    Me.Permission = "NO"
                Case vbCancel
                    Me.Permission = Null
                    Me.DOB = Null
                    Exit Sub
            End Select
        Case Is >= 18
            Me.Permission = "Not Needed"
        Case Else
            MsgBox "Date is Null"
    End Select

Ext_Procedure:
    Exit Sub
    
Err_Handler:
  'Basic error handler
    MsgBox "Error #: " & Err.Number & vbNewLine & _
           "Description: " & Err.Description, vbOKOnly
    GoTo Ext_Procedure
End Sub

Private Sub DOB_AfterUpdate()
    Call Age
End Sub

Make sure you change the "No Needed" in your code to "Not Needed" ... otherwise you'll get an error. ALSO, if you choose "Cancel" it will not set the textboxes to null because it is a required field in your table. I'd suggest you make it just a Yes/No messagebox and get rid of the "Cancel" actions, or you'll have to change the Required property for both fields in the table.

 

[Matizo]

I have made changes as you said and everything works well

Thanks a lot,
Matt