Solved

Auto increment value

Posted on 2012-03-27
13
665 Views
Last Modified: 2012-03-28
Hello Experts,

I made a small database that has 7 fields:
Record_ID
Language
Dict_name
Word_ID
Video_name
Webpage link location
Webpage link video

If I have these values:
Record_Id = 1
Language = E
Dict_name= able
Word_ID = A290001
Video_name = A290001.mov
Webpage link location = www.mysite/index/dict/A290001.html
Webpage link video = www.mysite.com/index/dict/videos/A290001.mov

Now if I add a new record and a new name for the field Dict_name, how I can make the field Word_ID to automatically adjust a new number? If I add the word "car", then the Word_Id field should change to A290002. Same thing the field Video_name. The last 2 fields, webpage link location and webpage link video should change as well like www.mysite/index/dict/A290002.html and www.mysite.com/index/dict/videos/A290002.mov


Any help would be greatly appreciated.

Many thanks in advance.
0
Comment
Question by:currentdb
  • 6
  • 6
13 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37774982
You can do it with dmax function, for example, but why you need it? What is "A29" and how it will be incremented? You already have Record_ID and you can create your Word_ID ="A29" & format(Record_ID,"0000") when you need it.
As I see, you should not to store this fields in db (only if you like to be able manully modify them):
Word_ID
Video_name
Webpage link location
Webpage link video
0
 
LVL 1

Author Comment

by:currentdb
ID: 37774997
Hi als315,

"A29" is just a value. If it begins at A290001, it will be incremented to A290002 when a new entry is added in the database. If you think that Record_Id is not needed, then I have to define a different primary key.

As I explained, if I enter a new word (in the Dict_name field), the other 4 fields (Word_ID
Video_name, Webpage link location, Webpage link video) should automatically increment. I don't have to enter manually a new assigned value again and again. The database will have 800 words, so it will be a pain in the ass to just assign a value to them one by one.

Does dmax function do all this change ?

Thanks again.
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 50 total points
ID: 37775010
See if something like this works for you.  I employed a trigger - Note that I only did word_id and video_name - you should be able to expand it to include the others:

mysql> create table currentdb_table (record_id int not null auto_increment primary key, dict_name varchar(20), word_id varchar(20), video_name varchar(20));
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter |
mysql> create trigger currentdb_insert 
    -> before insert on currentdb_table 
    -> for each row begin   
    -> DECLARE temp_int integer; 
    -> DECLARE temp_word varchar(20); 
    -> set temp_int=(select ifnull(max(record_id),0)+1 from currentdb_table); 
    -> set temp_word=concat('A', 290000+temp_int); 
    -> set NEW.word_id=temp_word;  
    -> set NEW.video_name=concat(temp_word,'.mov'); 
    -> end;|
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
mysql> insert into currentdb_table (dict_name) values ('able');
Query OK, 1 row affected (0.00 sec)

mysql> insert into currentdb_table (dict_name) values ('car');
Query OK, 1 row affected (0.00 sec)

mysql> select * from currentdb_table;
+-----------+-----------+---------+-------------+
| record_id | dict_name | word_id | video_name  |
+-----------+-----------+---------+-------------+
|         1 | able      | A290001 | A290001.mov |
|         2 | car       | A290002 | A290002.mov |
+-----------+-----------+---------+-------------+
2 rows in set (0.00 sec)

Open in new window


Having done that, I would recommend that you reconsider your design, especially on storing the link and video location urls.  If they will all have the same base url, I suggest that you have that base url as a setting in your application instead and just store A290001.html.  This way, if you change locations later on, you only need to change the application setting instead of all records in the database.  Going further, you really only need to store word_id and just dynamically form the others, since they follow a single base format.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:als315
ID: 37775046
"If you think that Record_Id is not needed, then I have to define a different primary key."
I think it is the only field you need to construct all other fields.
Your data are stored in MySQL or in Access DB?
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775047
For now the db is stored in Access. Later on I will export it to MySQL.
0
 
LVL 40

Expert Comment

by:als315
ID: 37775064
You can't use same code for Access and MySQL. What kind of code you need? And you don't  answered: why you like to store this name in DB when you can construct it every time you need it?
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775074
Hi als315,

I know Access and MYSQL do not use the same code. For now I need the code that works under Access, the one for DMAX function.

>>why you like to store this name in DB when you can construct it every time you need it?
It's for tracking purposes. If we add a new name in this "dictionary", we don't need to figure an id number for this dictionary and its video. It is not needed to construct it every time, at least for now.

Thanks again for your patience.
0
 
LVL 40

Accepted Solution

by:
als315 earned 450 total points
ID: 37775127
OK, try this sample (form tblMovies)
DBIncr.mdb
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775205
I made a small change and added the field "Dict_name". The field Word_ID does not increment even if I modified the line in Module 1 (the line that refers to the proper form).

Attached is the sample.
DBIncr-v2.mdb
0
 
LVL 40

Expert Comment

by:als315
ID: 37775249
I am using field's "default value" for filling it from function. You can see it in my example. Check also table name in function Next_WordID (Module1)
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775276
I can see the default value for "Word_ID" in the form tblMovies as well as in Module1.

But here I added the field "Dict_name" before the field "Word_Id", and changed Module 1 accordingly to point to tblMovies1 instead of tblMovies2. Except there is a bug error and it points to the same line that I changed in Module 1, which is A = DMax("[Word_ID]", "tblMovies1")
0
 
LVL 40

Expert Comment

by:als315
ID: 37775293
In Dmax parameter: "tblmovies" is name of table, not a form, you shouldn't change it.
Default value is assigned to a new records in your table:
default value
0
 
LVL 1

Author Comment

by:currentdb
ID: 37775355
My bad, it was my fault. I mean when I created the second form to incorporate the "Dict_name" field, I forgot to set the default value as specified.

Now it works and thank you so much for your help :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
default combobox value 12 18
Where on a calculated field 1 21
error in For & Next statements 4 23
record saved form with no buttons or X 3 20
Read about achieving the basic levels of HRIS security in the workplace.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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