Calculating text in tables

[access_novice1234567]

I need help. I want a field in my table that generates an Log number.

I already have fields:
I.D. number (sequential number)
Project Name (Text field)
Milestone (Number field)

The Log number should be made up of the first two letters from the Project Name, then the Milestone, then the ID number. The three parts of the Log number should be separated by hyphens.
So if:
I.D. number = 01
Project Name = Nice Light
Milestone = 03

Then Log number would = NI_03_01

On my form I have managed to get this to happen using an unbound calculated text box, but I need the log number displayed in the table, and this seems impossible.

Any help? Important things to note are I am not very clever when it comes to computers, or programming.

Cheers

 

[Rabbie]

You would need to create a field in your table for the log number and then populate it with an update query. Access help would be a good place to look for information about this. Since you have already worked out how to format the field on your form the query should not be too difficult.

 

[neileg]

As a general rule it's bad practice to store anything in a table that can be calculated when you need it. As Rabbie points out, you can do this in a query. Now I wouldn't use this to update the table but, instead, use the query as the dataset instead of the table, so no need to store anything.

 

[access_novice1234567]

Sorry Rabbie, have no idea how to do this, and I am finding "help" to be no help at all. Would appreciate further guidance.

 

[Rabbie]

Sorry Rabbie, have no idea how to do this, and I am finding "help" to be no help at all. Would appreciate further guidance.

I am not sure I can explain things better than the help file but here goes.

Firstly I agree with Neil that you should not store the Log number in the table anyway since you can always calculate it in a query. Just use the Query design grid to extract the data you need from your table and then in the next empty column of the grid put something like

LogNumber: followed by the formula you used to populate the field on your form.

Where's your problem? If you can be more specific then we can help you.

 

[access_novice1234567]

Right. Log number needs to be stored in the table.
I have two things that users are going to look at. A form where they input description of concerns. And then a table where they can search all the concerns that have been recorded. So data will only be entered through the form. However, when they are searching the table they need to see the log number so they can use another system where the log numbers are used.
Any clearer?
In message #5 I have selected the data I need as fields, but "in the next empty column" I can't type "log number" as it is not a table field.

 

[Rabbie]

Right. Log number needs to be stored in the table.
I have two things that users are going to look at. A form where they input description of concerns. And then a table where they can search all the concerns that have been recorded. So data will only be entered through the form. However, when they are searching the table they need to see the log number so they can use another system where the log numbers are used.
Any clearer?

Firstly Users should never be working in tables. That way data corruption lies. They should only see the data on a form or in a report. That way you keep control. If you do let them see the concerns via a report or form you can calculate the log number on the fly.

In message #5 I have selected the data I need as fields, but "in the next empty column" I can't type "log number" as it is not a table field.

Thats strange because it works fine for me. You are using the design grid aren't you. Just type the name you want followed by a colon and then the formula for the value.

 

[access_novice1234567]

OK, got the query working now. Just got to get the query results back into my table. I take your point about data corruption, letting users touch the table, but got to trust people in the organisation not to fiddle with things.

 

[Rabbie]

OK, got the query working now. Just got to get the query results back into my table. I take your point about data corruption, letting users touch the table, but got to trust people in the organisation not to fiddle with things.

Giving users access to a table is asking for trouble. It is so easy that an accidental change can be made to your data.

If you use a form to display the results of your query in Datasheet view then it will look like a table but you can have proper validation of any changes. You can also make it easier for users to access the data.

 

[access_novice1234567]

OK. So I now have my main table with lots of information, and my log number table which contains the result of my query, as a make table query. I now need to display fields from both tables in the one form, in datasheet view. I can't link the tables together to do this.
Any more advice?

 

[Rabbie]

If you can post your table layouts we may be able to help. I find it difficult to believe that there isn't a connection you could make between your tables. In that case your design would be seriously flawed.

 

[Sum Guy]

Try this one, its from a post on another forum:

Quote

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeInsert
Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. Its much easier to store and group them when they're stored separately.

Its also easier to format them in a query.

End quote

You'll have to do a little "tweaking"...
TheYear would become TheProject with a coding change, TheSequence would become the IDnumber with a coding change and you already have TheMilestone

 

[neileg]

Let me give you some advice. Whether you listen to it is up to you. I'm not a trained programmer, my experience is based on real world applications development.

You don't store data that can easilly be calculated. Firstly this wastes space but secondly if the underlying data changes then the calculated result should change.

You don't let users work in tables because you have no control over what they do. The users may be perfectly trustworthy but everyone makes mistakes.

When you are asking for advice in these forums, give as much information about the business process as you can. If you focus in on what you think is the issue, you can't get advice on what may be a better approach. A lot of the guys and gals on here go to extensive lengths to help novices but the better the base information, the better the advice.

When you try and follow some advice and it doesn't work, explain how it doesn't work, e.g. You get an error message and what that message is, you get no results returned, you don't get the results you expect, and so on.

Good luck!