Solved

File value being changed upon changing another field via a form

Posted on 2014-01-04
3
316 Views
Last Modified: 2014-01-04
I have a a field called  "FileNo"  this number is generated automatically via

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.FileNo = Year(Date) & "1" & Format(Me.InvoiceID, "00000")
End Sub.


The problem is that I have a record that was created last year as example

FileNo =  201312345677

when  a user opens the record via the form and changes one of the field values the FileNo is being recreated and saved as :

FileNo = 201412345677


ONce the FileNo is originally created I do not want it changed no matter what other changes to the record are being made subsequent to the intial creation of the FileNo.


Please help.
0
Comment
Question by:seamus9909
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39756174
Use Before_Insert instead of Before_Update.

Before_Update runs anytime a user edits a record, or adds a new record.

Before_Insert runs only when a new record is added.

Or you can check to see if the user is adding a new record in your Before_Update:
Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.NewRecord Then
    Me.FileNo = Year(Date) & "1" & Format(Me.InvoiceID, "00000")
  End If
End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39756177
You can use:

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

/gustav
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39756239
try this


Private Sub Form_BeforeUpdate(Cancel As Integer)

if me.newrecord then

          Me.FileNo = Year(Date) & "1" & Format(Me.InvoiceID, "00000")

end if
End Sub
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now