One field to update based on two other fields data

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229
I want one field to update based on two other fields information

User selects "status" and inserts "date" request was made, automated due date populates "due date" field in form. 14 and 1 are days...not months or years.

Code:
Private Sub Date_AfterUpdate(Cancel As Integer)
If Me.[Priority] = "Standard Research Request" Then
Me.[DueDate] = Me.[Date] + 14
If Me.[Priority] = "Pricing Dispute" Then
Me.[DueDate] = Me.[Date] + 1
End If

End Sub
Thanks for any help...
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
You could either use ElseIf or a Case statement.
Examples:
Code:
Private Sub Date_AfterUpdate(Cancel As Integer)
    If Me.[Priority] = "Standard Research Request" Then
        Me.[DueDate] = Me.[Date] + 14
    ElseIf Me.[Priority] = "Pricing Dispute" Then
        Me.[DueDate] = Me.[Date] + 1
    'more ElseIf as needed
    End If
End Sub

Private Sub Date_AfterUpdate(Cancel As Integer)
    Select Case Me.[Priority]
    Case Is = "Standard Research Request"
        Me.[DueDate] = Me.[Date] + 14
    Case Is = "Pricing Dispute"
        Me.[DueDate] = Me.[Date] + 1
    'other cases as needed
    End Select
End Sub
You can also use the DateAdd function if +1 or +14 don't do the job. Check out the syntax in Help.

Denis
 

drmingle

Board Regular
Joined
Oct 5, 2009
Messages
229
Thanks SydneyGeek for the help...

I tried both snippets without success.

I'm getting the following:

Error: User-defined type not defined

Can you offer additional help?

Also, I already have "Microsoft DAO 3.6 Object Library" installed...
 
Last edited:

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
If could be that Access is choking on your field name. Date, Month, Year, Day, Name ... are all reserved words (either part of VBA, or names of functions in Access) and they can cause you problems. Try renaming the control from Date to RecDate (for example) and try that instead.

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,100,205
Messages
5,473,152
Members
406,849
Latest member
AndreasMs

This Week's Hot Topics

Top