Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

File value being changed upon changing another field via a form

Posted on 2014-01-04
3
Medium Priority
?
330 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 52

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

580 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