Auto Numbering

[xyzlook]

I have created a database for my DVD collections. Since I have DVDs in different languages, there is a "Language" table with a LanguageID, Language fields.

I have a Detail form where I would like to add a serial number field which will have both text and number. For example, if the DVD is in English, the serial number will ENG####.

How can I make this Serial Number be automated that when I enter English or Hindi in the language field, the serial number will be automatically updated? Any suggestions? Please keep in mind I am new in Access and don't know a whole lot. However, I am willing to try and learn as much as I can.

Any help from anyone will be much appreciated. Thanks a lot. Best regards.

 

[MajP]

Does the number need to be sequential or in any pattern?
Does it have to be a primary key?
Does it have to have a set amount of characters?

Depending on these answer will depend how simple it would be to do.

 

[xyzlook]

1. Does the number need to be sequential or in any pattern?
Yes. I would like it to keep track of all DVDs plus a sequential number for a given language. For example, let's assume I have a total of 10 DVDs 3 English 7 Hindi. I would like this serial number to ENG0001-10, HIN0001-10 etc. The moment I enter a new DVD which is an English DVD and fill out the language field, the serial number field should automatically populate as ENG0004-11. Can this be done?

2. Does it have to be a primary key?
NO

3. Does it have to have a set amount of characters?
I prefer first 3 to be letters followed by 3 or 4 numbers.

Thanks for your prompt response. I appreciate.

 

[abaldwin]

My suggestion is this.

I would simply use two fields.
Your feeder table of languages should contain another field called nextSerial (or whatever you want)

You data table would contain (in addition to whatever other kind of data)

One field for the Language
One field for the serial number When entering a new DVD you would DLOOKUP the nextserial where the language is what you chose, Add 1 to it and insert the result into the data field and update the feeder table.

Any counts on reports would actually be retruns from a Count function in a query.

The actual serial number you want would only be visible in outputs by combining (concantenating) the language with the serial field on labels or reports that you print.

If you try to get into autoincrements you will start to loose numbers on deletes and all.

Just my $.02



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[xyzlook]

Dear Andy:

Because of my limited knowledge, your $.02 is worth a million for me. Thank you.

Can you elaborate the DLOOKUP function with an illustration, please.

Thanks.

 

[abaldwin]

What version of access. I may get a moment this evening and create you a little example for ya.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[PHV]

I'd personally play with DMax instead of DLookUp ...

 

[xyzlook]

Dear Andy, Thank you.

I use MS Access 2003.

 

[abaldwin]

Yea, DMAX not DLOOKUP what was I thinking? Dont know dont care its 5:00 here.

See ya tomorrow.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[ProgramError]

a table called adminTBL
lang/seed
eng 20
fre 13
ger 4

On a form you need a hidden textbox bound to the recordID field of you main table, a combobox and another textbox (unbound)

The combobox is based on a query of above table.
"SELECT admintbl.language, admintbl.seed FROM admintbl ORDER BY [language]"

the control source property of textbox =([nameofcombobox].[column](1))+1

When the form is closed you need to combine the information in the two boxes and put it in the hidden textbox, which will inturn save it in your main table.
Me.hidden = Me.Combo & Me.Textbox
AND
copy the value in the unbound textbox into the correct seed record of the language table. Then goto a new record.

checked and works.






Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[xyzlook]

Dear Ian(UK):

Here is what I have.

Table 1 called, tblLanguage
Fields in this table:
1. LanguageID (Auto)
2. Language (Text): Description of language, ie., English, German, French etc.

Table 2 called, tblDVD
Fields in this table:
1. ProgramID (Auto)
2. DVDTitle (text): Name of the DVD
3. ProgramTypeID (Number): Type of DVD, eg., Action, Drama, Musical etc. (tied to another table called tblProgram)
4. Director
5. Language
6. Format (DVD, VHS etc)

And several other fields.

My objective is to add a box to the frmDVD (that is based on tblDVD) that will automatically populate the serial number of each DVD sequentially.


As I read your instructions, I have the following questions:
1. Am I supposed to create a new table called adminTBL with the fields you mentioned?
2. Where Language field will have the seed identity? (what is a seed identity? I am a novice and don’t know anything about it)
3. What do those numbers next to language represent? Eng 20, fre 13, ger 4?
4. On a form you need a hidden textbox bound to the recordID field of you main table, a combobox and another textbox (unbound)
Are you referring to frmDVD or I need to create yet another new form?
5. When the form is closed you need to combine the information in the two boxes and put it in the hidden textbox, which will inturn save it in your main table.
Me.hidden = Me.Combo & Me.Textbox
AND
copy the value in the unbound textbox into the correct seed record of the language table. Then goto a new record.
This is rather too advanced for my expertise. Can you explain this with an illustration?


Thank you for your kind assistance. Much appreciated.

 

[ProgramError]

Q1 no, just add another number field to your tbllanguage table. This is to store the last recorded number for that particular language.
Q2 I didn't know what to call the seed field, see above question for explanation of its use.
Q3 ENG is short for english, fre is short for french, ger is short for french, etc and the numbers are .. well, see the answer to question 1.

5:30pm here. Home time.... I'll finish off this when I get home. Hope you can wait an hour.




Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[xyzlook]

Dear Ian,
Thanks for writing.

Q1 no, just add another number field to your tbllanguage table. This is to store the last recorded number for that particular language.

OK. Sorry being a novice. I will add a field to tbllanguage. The type of field will be “seed”? Couldn’t find such option (Auto, Text, Number, Date/Time, yes/No). Please explain with an illustration.

Q3 ENG is short for english, fre is short for french, ger is short for french, etc and the numbers are .. well, see the answer to question 1.

I understood the abbreviation for languages, what I didn’t understand is the numeric expression after the language (ENG 20, FRE13, Ger 9) etc.

5:30pm here. Home time.... I'll finish off this when I get home. Hope you can wait an hour.

Yes, of course, I can wait until I get help to do this.

Appreciate your time.

 

[ProgramError]

continuing with Q3... this information would be added to the tbllanguage and another field needs adding for the seed (last serial number used as you call it).
Q4 If frmDVD is your input form then that's the one to put the hidden textbox. I'm assuming the language field in the tblDVD table is a lookup field related to the language field in the tbllanguage table.... If so, then the tblDVD/language field would be bound to the combobox by the sql statement I gave you (put it in the row source property or use the wizard.
Q5 Me.hidden = Me.Combo & Me.Textbox
this need to go in a VBA proceedure. To do this ,in design view and using the wizard, put a command button (a button to click on) on the form and using the wizard select the categories/record operations and actions/save record. click next and the text option "save record", click next again. Name the control something like cmdSaveRecord and finish.
Double click the newly created button to get its properties, select the events tab and the on click property. you will see three dots appear to the right , click on them and the VBA window will open with the following

Private Sub nameofbutton_Click()
On Error GoTo Err_nameofbutton_Click

*** Me.nameofhiddentextbox = Me.nameoftheCombobox & Me.nameoftheTextbox
*** 'save the new seed number back to the languages table
*** 'let me think about this bit
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

*** docmd.gotorecord ,,acnewrec

Exit_nameofbutton_Click:
    Exit Sub

Err_nameofbutton_Click:
    MsgBox Err.Description
    Resume Exit_nameofbutton_Click
    
End Sub

Add the following lines starting with *** but dont include *** when you type it in

change the names accordingly.

save the proceedure by clicking on the floppydisc icon on the toolbar.
close the vba window.

You may want to leave the hidding textbox visible just so you can see what is happening to it.


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[ProgramError]

part 3
q1 - number
q3 - the number are just examples. These are the starting points for the serial numbers (normall zero) but i just called them seeds. one of these will be automatically increased when you save a record depending on what language you chose in the frmDVD language combobox.


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[xyzlook]

Dear Ian:

Here is what I gathered.

On my tblLanguage, I need to add another field with number property to record the serial number for each dvd.
So far this is how tblLanguage looks

1. LanguageID (Auto)
2. Language (Text)
3. SerialNo (Number)


frmDVD is based on tblDVD. This form now has

1. One hidden txtbox bound to the ProgramID from the tblDVD
2. One combobox called cboSerial based on this query

SELECT Languages.*, Languages.Language, Languages.SerialNo FROM Languages ORDER BY Languages.Language;

3. Another txtbox [Unbound] called txtSerial has =([cboSerial].[column](1)+1 in its control source property

4. A Command Button called; cmdSaveRecord with this VBA procedure

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Me.ProgramID = Me.cboSerial & Me.txtSerial
'save the new seed number back to the languages table
'let me think about this bit


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub


When I open the form frmDVD, I can select a language from cboSerial. But I see #Error in the txtSerial box when I select a Language. When I click on cmdSaveRecord, I get this error message: Type mismatch.

Please help. Thank Ian

 

[ProgramError]

try this in the VBA proceedure

Me.programID = (cboserial.Column(0)) & Me.txtserial

you may need to change the column number from 0 to 1 or 2

I thought your programID was based on a autonumber field. Am I mistaken?

=(cboserial.column(1))+1

txtserial's control source needs to have the above. change the column number depending what position the language appears in your combo drop down list.
You will have to look at the column count, column widths and bound column properties of the combo box to get your results. If the combo works then don't alter any of its settings.

I have a simple demo working if i can store it anywhere



Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[ProgramError]

11pm here, will continue tomorrow.

 

[xyzlook]

Ian,
Thanks again.

Well, I was able to make the txtbox and cbobox work. But the cmd button was another story. As I click on it to “save”, I am told, “you cannot assign a number to this object” Whatever that means.
Two other issues:
Q1.
=(cboserial.column(1))+1

txtserial's control source needs to have the above. change the column number depending what position the language appears in your combo drop down list.
How does this relate to my language table? This is my table looks
tblLanguage looks like this
Auto Language Serial
Col1 Col2 Col3
1 Bangla 0
2 English 0
3 Hindi 0

Q2.
I was hoping that when I will click on the Save Record button, I will have a serial number for each DVD as this: BAN-001-3000 or ENG-001-3000, Where First 3 alpha refers to the language of a DVD, next 3 digits refers to its serial number for that language group and the last 4 digits refers to the total number of DVDs in the database. How may I accomplish this?

Thank you for all your help.

 

[xyzlook]

NOTE to Andy Baldwin:

Did you get a chance to send me an example of DLookup function as you wanted to? Here is what I have:

A Language Table (tblLanguage) with 3 fields
1. LanguageID (Col 1) Auto
2. Languages (Col 2) Text
3. NextSerial (Col 3) Text

I have a Data form called frmDVD which is based on a table called tblDVDInformation

In addition to various other data, I have the following text boxes on frmDVD:

1. txtLanguage (to identify Language)
2. txtSerial (to record sequential number of DVDs)


From your earlier note I understand I need to DLookup the txtSerial. Here is where I need your help. I am having difficulty understanding the expr, domain and criteria of DLooup function. I could use an illustration.

Also, you mentioned that the actual number I want would only be visible in outputs by concantenting the language with the serial field on labels or reports that I print. Can you please give an example.

In addition, I am difficulty in setting default values for language and format. eg., I would like the default value of my data table to be "English" for language and "DVD" as format. How can I accomplish this? I succeeded getting this done on my forms for frmLanguage and frmFormat. But unsuccessful to have displayed in frmDVD while entering a new DVD information.

Thanks for your time and help.