Form Processing in Access

seamus9909
seamus9909 used Ask the Experts™
on
I would like to add a a condition to this,


While the user is creating a new record, there is a field on the form called "ServiceType"  if they select 'Legal' I would like to append a "L" at the end of the FileNo, else
leave everything the same as below


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.FileNo = Year(Date) & "1" & Format(Me.InvoiceID, "00000")
End If
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Please embed code into [code] tags (the CODE button). You can edit your post.

E.g.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  
  If Me.NewRecord Then
    Me![FileNo] = Year(Date) & "1" & Format(Me![InvoiceID], "00000") & Left(Me![ServiceType], 1)
  End If

End Sub

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Generally it is not a good idea to store a value in a field which can be generate based upon other values in the same table record.

Instead, if you need a field with that value you can either create a computed column (don't recommend this either) or create this field in a query.

I strongly recommend the query technique:

SELECT InvoiceID, Year(InvoiceDate) & "1" & Format(me.invoiceID, "00000") & IIF(me.ServiceType = "Legal", "L", "") as FileNo
from yourtable

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial