Please help - Auto Inventory ID Generator

[IlluminEssence]

Hi! I am working on a database and got stuck. I would appreciate any help..here is my problem.

I have a text box called "txtinvID" and another text box called "txtitemType". When the user presses ADD RECORD, he has to enter data on Item Type, Item Description, Retail Cost, Wholesale Cost, etc.. He doesnt have to enter anything in the "txtinvID". When the user presses SAVE RECORD, the "txtinvID" has to be generated from "txtitemtype"'s first letter and add 001 at the end to the "txtinvid"....so like...
Lets say that I enter Gift in the "txtitemType", I wanna take the first letter "G" and add 001 to the txtinvid. So the txtinvID would say "G001" and so on...I have tried programming it and here is what I have:

in the CMDSAVE:
On Error GoTo Err_cmdSave_Click
Dim strNewInventoryID As String
' turning off warning messages
DoCmd.SetWarnings (False)
' an if Statement to prompt the user to save
if (MsgBox("Are you sure you want to save this record?", vbYesNo, "Attention User!")) = vbYes Then
strNewInventoryID = CalculateNewInventoryID
If strNewInventoryID <> "" Then
txtinvID.Value = strNewInventoryID
End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "The record that you have edited has been saved", vbInformation, "Attention User"
End If
' turning back the warning message
DoCmd.SetWarnings (True)

FUNTION CalculateNewInventoryID:
Private Function CalculateNewInventoryID()
Dim strCID As String, intCountNum As Integer, strCountNum As String
lstItemType.Requery
If IsNull(txtinvID) Then
intCountNum = lstItemType.ItemData(0)
strCountNum = Trim(Str(intCountNum + 1))
strCountNum = "000" & strCountNum
strCountNum = Right(strCountNum, 3)
strCID = UCase(Mid(txtinvType.Value, 1, 1))
strCID = strCID & strCountNum
CalculateNewInventoryID = strCID
Else
CalculateNewInventoryID = ""
End If
End Function

When I run this code, i get an error saying that there is something wrong with my source code, etc... PLEASE HELP...

Thank you

 

[pcs]

check the response in the Reports forum..

 

[Rich]

 

[pcs]

 

[IlluminEssence]

what response are you talking about??

 

[mrabrams]

Rich & pcs:

I don't have any smileys or emoticons so I'll do this the old fashioned way (with words!).

That was very funny !!!! :}}

Michael

 

[IlluminEssence]

can you guys help me please?

 

[Pat Hartman]

I am arbitrarily choosing this instance of your message to respond to. However, in the future, please do not scatter blast your questions all over this forum. Choose the relevant topic and post there or if you can't decide, then post in the general section.

I strongly suggest that you reconsider your plan to make "meaningful" Ids. I don't know what the potential scope of your data is but your plan will limit you to 999 instances of any one type and violates First, Second, and Third normal form. Use one column to store the item type and a second column to contain an autonumber. Then you can trash all your code and have a less problematic primary key for your table. If for reporting and display purposes you want to show an Id that starts with a letter and is followed by a number with high order zeros, do it with the format function -

txtItemType & Format(txtInvID,"000000")

 

[IlluminEssence]

thank you pat