Solved

Auto increment value

Posted on 2012-03-27
13
660 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 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:als315
Comment Utility
"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
Comment Utility
For now the db is stored in Access. Later on I will export it to MySQL.
0
 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 1

Author Comment

by:currentdb
Comment Utility
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 39

Accepted Solution

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

Author Comment

by:currentdb
Comment Utility
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 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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