Autopopulate field

[mtagliaferri]

I am looking to populate automatically one field in the same record with a value as per picture attached:
the username has to be the same as the staff number but beginning with a letter...

 

[bob fitz]

Set Control Source property of Username to
= "A" & [Staff Number]

 

[plog]

You shouldn't store calculated values. If Staff Number will never change, or if Username will change if the Staff Number changes, then you do not need the Username field.

 

[mtagliaferri]

You shouldn't store calculated values. If Staff Number will never change, or if Username will change if the Staff Number changes, then you do not need the Username field.

I think I got your point...

I have a logon form that is based on the field username, although I could change it to read StaffNumber!

but I would rather store the data in the field.
I used this code

Private Sub StaffNumber_AfterUpdate()
    Me.Username = "A" & [StaffNumber]
End Sub

On the form the username will be displayed correctly, but the result is not stored in the table, how can I store the result in the username field in the table?

 

[Minty]

As Plog said - don't bother.
Simply display the staff number with an A before it. It's completely redundant data, and if anyone ever changes the staff number anywhere other than on your form it won't match.

 

[ashleedawg]

It made me chuckle that 100 people have read your post but nobody will tell you how to add a calculated field... on the other had, they might have your best interests in mind! Now, I might get shunned for telling you how, but I'll try to make up for it by sharing my understanding of why!

Here's how you add a calculated field (pic attached). I just tried for the first time and found it's finicky - it has to be added as a new field -- not an existing field -- and some tables would not let me add one at all.


Access's job is to store data and manipulate it later. The most 'broken down' your data is stored, the more you will be able to do with it later (often in ways we can't predict when initially designing a database). It's a bad practice to store duplicated or derived data.

From some pro's...
This and this and this and to quote The_Doc_Man in this old post:

There are situations for convenience, I suppose, but in something like 30+ years of experience with database systems (starting Digital Equipment's DATATrieve, going through Borland's DOS version of Paradox, and switching to Ac97 in about 1998), I have NEVER EVER found a case where using stored computations was truly necessary. Convenient, yes. Necessary, no.

The reason is that a query can be used as a data source in place of a table, but can contain your computation as one of its fields. Therefore, build your table, build a query on top of it and include computations based on the appropriate fields. Then build your forms or reports on the query, not the table. Problem solved.

...I guess that should now read "35+ years"... I imagine he didn't change his opinion.


On the other hand: if you are adding these Usernames as a default for your existing records, but in the future some staff might have different Usernames... well, you still don't want a calculated field (since once you set that rule it will apply to all new records). If that's the case you could use an update query to create the usernames for the current records.

Being self-taught and stubborn, I don't always follow standards "just because that's how it's done"... but over time I'll realize, "ohhhh THAT'S why everyone said to do it that way!"

 

[Minty]

@ashleedawg - Excellent post.
There are always times when you sometimes need to do things that go against convention.

I store a list of payments per month based on a another table that has the End date, number of payments and total amount.
I can calculate this out, but when you want a monthly breakdown of total payments and there are 4000 + main records, all with different starting and ending dates and different numbers of payments, it just gets darn cumbersome and slow. So I calculate and store the individual monthly payments when new records are added or edited for ease of getting at. Very against best practice but "Suits me (and our business) sir."

 

[ashleedawg]

@ashleedawg - Excellent post
... Very against best practice but "Suits me (and our business) sir."

Thanks! But honestly I had to do some Googling to remind myself why it's "bad"!

I'm stubborn in my own ways, for no particular reason (ie., I'll indent the way I want, dammit! ) but as you pointed out, every rule has an exception, and I figure that, at the end of the day, the right way to do something is the way that does completes the required task!

 

[mtagliaferri]

Ok, I got the message