Unique auto ID

[meekychunky2004]

Hi all

I need help!!! This is only my second database, but its a big one!!!!

Im building a book loan database and need some help ...

Problem 1
I have a table for languages and would like the id to be auto-generated from the first three letters of the language, ie FRE for French.

Problem 2
Once i have the language id i need to create a book id auto-generated from language id plus 5 digits, ie FRE00001

All help appreciated, thanks in advance!!!

 

[dcx693]

I would suggest you keep the language ID in a separate field, then use a DMax function to determine how many other books have already been added to your table with that same language. Then just assign it a number.

Use a form form the data input. Have the user select the language of the book from a combo box. Then use DMax like this:
Dmax("[ID]","BookLoans","[Language]=" & Me.cboLanguage
to assign the value to the control. Then set the controlsource to a field name in the table.

I know, I've spouted out a lot and you might not understand all of it. No problem. Just post back with questions.

If you wind up giving your books ID numbers like FRE00001, you'll have to first determine the language, then get the numeric portion, then determine it's value. It's much easier if you have two fields with "FRE" and the number 1.

 

[meekychunky2004]

auto id

Thank you ... and yes, I am completely baffled with your reply.

I have a table called Books and a table called Language, are you saying create a field in the book table called language then create a form for the books?

I would suggest you keep the language ID in a separate field, then use a DMax function to determine how many other books have already been added to your table with that same language. Then just assign it a number.

Use a form form the data input. Have the user select the language of the book from a combo box. Then use DMax like this:
Dmax("[ID]","BookLoans","[Language]=" & Me.cboLanguage
to assign the value to the control. Then set the controlsource to a field name in the table.

I know, I've spouted out a lot and you might not understand all of it. No problem. Just post back with questions.

If you wind up giving your books ID numbers like FRE00001, you'll have to first determine the language, then get the numeric portion, then determine it's value. It's much easier if you have two fields with "FRE" and the number 1.

 

[dcx693]

Yes, I am saying to add a field to your book table for keeping the language code. It's something that describes the book itself, so it belongs in the book table along with the other book titles.

Yes, I am also saying that you'll need to create a form for data entry, so that when a user enters a book, then chooses a language for it, the form will find out the last highest numbered book assigned to books with the same language. It should then add 1 to that number and assign that number to your new book.

Why do I say to look for the highest previously numbered book? Instead of just counting the previously numbered books in the same language? What if you delete a book from the database? Let's say you had 3 books previously numbered 1, 2, and 3, all in French. Let's say you delete one of the first 2. You don't want to number the next book 3. You need number 4. If you delete the 3rd one, then the highest number left in the database is 2, and you will assign the new book number 3.

 

[meekychunky2004]

Ok, sounds quite straight forward, however i cant get my language id to generate from the input, any clues?

Yes, I am saying to add a field to your book table for keeping the language code. It's something that describes the book itself, so it belongs in the book table along with the other book titles.

Yes, I am also saying that you'll need to create a form for data entry, so that when a user enters a book, then chooses a language for it, the form will find out the last highest numbered book assigned to books with the same language. It should then add 1 to that number and assign that number to your new book.

Why do I say to look for the highest previously numbered book? Instead of just counting the previously numbered books in the same language? What if you delete a book from the database? Let's say you had 3 books previously numbered 1, 2, and 3, all in French. Let's say you delete one of the first 2. You don't want to number the next book 3. You need number 4. If you delete the 3rd one, then the highest number left in the database is 2, and you will assign the new book number 3.

 

[dcx693]

Post more about how you have things set up. Do you have the form set up?

 

[meekychunky2004]

Yes I have the form setup. Please see attached file.

 

[dcx693]

Attached file? Where?

 

[meekychunky2004]

Sorry, thought Id attached a file (huh)

I have 2 tables: tblBooks and tblLanguage

tblBooks:
BookID - autonumber
BookCode - (this is the field i want to fill)
LanguageID - (look-up field from tblLanguage which returns the full language)

tblLanguage:
LanguageID - autonumber
LanguageCode - first 3 letters of language
Language - full language

I have a main form with a subform called fsubBooks - this form was made with the wizard from the books table. The subform has cboLanguage (a lookup) and txtBookCode (the field i want to fill).

Hope this helps
Thanks

 

[dcx693]

I have a main form with a subform called fsubBooks

If the book detail is in your subform, what's in the mainform? Language info?

 

[meekychunky2004]

If the book detail is in your subform, what's in the mainform? Language info?

The main form has no data attached to it. I have set up a tab control on the main form so that each set of data can be displayed on each page.

 

[dcx693]

OK, so you've now got the LanguageID field in your book table. Good.

Now for this part:

I am also saying that you'll need to create a form for data entry, so that when a user enters a book, then chooses a language for it, the form will find out the last highest numbered book assigned to books with the same language. It should then add 1 to that number and assign that number to your new book.

Were you able to accomplish the part about finding the next highest numbered book for the same language? Here's how:

Have the user select the language of the book from a combo box. Then use DMax like this: Dmax("[ID]","BookLoans","[Language]=" & Me.cboLanguage
to assign the value to the control.

 

[Mile-O]

There's a thread started by BoroLee (to which I got involvd) which does this: DMax with letter conditions. I believe it's in the Forms forum.