Elaborate serial numbering project... 1

[cbk10]

Hello world,

I have a database in the works in need of an inventory serial numbering system, and I've having difficulty deciding how to best design three or four elements of it.

First, I have several prefixes that I'll need to begin with (LC, APW, etc). I've called this field Device Type Abbreviation.
Second, I need to incrementally generate values for each subsequent entry of each Device Type so that I can have numbers generated like LC-1, LC-2, AMW-1, AMW-2, etc, and I cannot have any duplicates among all of these generated numbers.
Third, I need be certain that this two-part generated number will be output to a field in my form and table so that I can search later for certain serial numbers and field the customer associated with each item.

So, that's the idea, and this is what I've come up with so far. I have created a table and produced a form from it both titled New Inventory Item. In both the table and form I have three fields in the following order: Device Type Abbreviation, Device Type Number, and Auto-Assigned Number. To recap, the first field is from a list of prefixes, the second field should be generated as the next incremental number for that prefix, and the third is simply the concatenation of the first two fields that I then need to make sure is updated on my original table.

To be clear, I haven't touched programming in about 10 years, but I can make my way around in it fair enough at present. Any help and questions for clarity are welcome. I look forward to any replies.

 

[MajP]

I will change the names because you should never use spaces.
You really only need two fields
DT_Abbrev - this is the device type abbreviation key pulled from the DTA table
DT_Number - this is the incremented value for that specific DTA

Now I would not include in my table the concatenated serial number. You can always show and search based on the other two fields. And you can show the concatenated value at anytime.
So your records would be
DT_Abbrev DT_Number
LC 1
LC 2
AMW 1
AMW 2

Look in the FAQs (I think in the Tables forum) about generating your own incrementing number. But basically if I am adding a new record and I pull down LC to assign the type, it will create DT_Number by something like
nz(dmax("DTA_Value","yourTable","DTA_ID_FK = 'LC'")) + 1
This says look in the data table and return the largest DT number where the type is LC. Then add 1.

I will now have a new record
LC 3

In a query or form/report
... [DT_Abbrev] & "-" & [DT_Number] as SerialNumber
this would make LC-3

Also if you index the two fields together it will ensure that the combination of the two are unique.

 

[cbk10]

I appreciate your reply.

I had success with your suggestions for about 15 minutes, but after I modified the index properties, things got messed up. Any suggestions concerning that?

Also, the "DTA_ID_FK='LC'" code, do I need to code it LC or leave it blank or _'d so that it will search for any one of the prefix values or does it matter?

 

[MajP]

I had success with your suggestions for about 15 minutes, but after I modified the index properties, things got messed up. Any suggestions concerning that?

you get an error message, nothing happens, the world stops spinning? Help me out.

It would be more like

assume you have a combo box "cmboType" where you assign the type and it is bound to DT_Abbrev.

Then your real code would be something like

dim abb as string
abb = nz(me.cmboType,"")
if not abb = "" then
me.DT_Number = dmax("DT_Number","YourTableName","DT_Abbrev = '" & abb & "'")+1
end if

 

[cbk10]

Yeah, this is weird. For a while, I was getting error messages, but now nothing is happening at all. The error messages were saying that the table name was incorrect/object couldn't be found.

This is the code in the event builder for Device Type Number text box right now.

Private Sub Device_Type_Number_BeforeUpdate(Cancel As Integer)

Nz ((DMax("Device_Type_Number", "New Inventory Item", "Device_Type_Abbreviation = ''")) + 1)

End Sub

I think I might have just been staring at this stuff so long that I'm starting to make stupid mistakes...

 

[MajP]

In my mind the Device-Type_Number is uneditable and assigned by the system. I would not even show it on a form. I would have a pull down to pick the new type: "LC, AMW, etc.". Once I pick it would behind the scenes set the Device_type_number. If I am showing anything on the form is an uneditable control with text like "LC-3".

So if my combo is cmboType and I select "LC" then in my combos after update

Private Sub CmboType_AfterUpdate()
dim abb as string
abb = nz(me.cmboType,"")
if not abb = "" then
me.DT_Number = nz(dmax("DT_Number","YourTableName","DT_Abbrev = '" & abb & "'"),0)+1
end if
End Sub

The code you showed does nothing.

 

[cbk10]

Ok, I need to make sure I'm on the same page as you.
All of the code we are editing is at the form level yes? That's what I've been editing all along, and I should mention also that I have coded a concatenation for the prefix and assigned number at the form level, so all I need to get finalized is this system generated number that doesn't become duplicated as far as its assignment to a specific prefix; I think we've been on the same page there.

Ok, so I added and edited the code you supplied to my combo box and still nothing, not even an error.

I believe I understand you suggesting the following look to the process: 1) the user selects the prefix, 2) this automatically assigns the next number in sequence for that prefix 3) displays it in a locked text box and 4) my concatenation formula combines that prefix value in the first box with the generated number in the second box to show a final serial number in another locked text box. My form presently looks this way, and it will even get to that fourth step if I have a number value supplied, but of course I want the system to generate that lol.

I hope I'm not making this more difficult than it is, though I'm sure I am. I should also mention that my prefix values are sourcing from a field list in another table; it isn't simply a lookup column because I may need to add more prefix values at a later date.

Please let me know what you think. You have been quite helpful with all of this, and I really appreciate it.

 

[MajP]

I think we jive.
Your prefix combos rowsource comes from a table of prefixes. To add more choices to your combo you have to add more records to your prefix table. I think you mean to say it is not a value list (but it is a lookup). I assumed this.

What did not make sense was this

Private Sub Device_Type_Number_BeforeUpdate(Cancel As Integer)
  Nz ((DMax("Device_Type_Number", "New Inventory Item", "Device_Type_Abbreviation = ''")) + 1)
End Sub

This event would happen after the user edits the Device_type_number control. The user does not edit this because it is a value generated in code. Also the code as written would not do anything because there is no left side assignement
some variable = some code
and the criteria in the dmax is hardwired anyways

 

[cbk10]

Glad to hear we are on the same page.

Ok, I've tried and re-tried all that we've discussed, and I'm still not getting a number value to generate at all. I'm sure my syntax is just off, so I'm going to past the code from that form page and see what you think. We're still binding the Device Type Number textbox to itself or the Abbreviation one now?



Option Compare Database

Private Sub Command12_Click()

End Sub

Private Sub Detail_Click()
End Sub

Private Sub CmboType_AfterUpdate()
Dim abb As String
abb = Nz(Me.cmboType, "")
If Not abb = "" Then
Me.Device_Type_Number = Nz(DMax("Device_Type_Number", "New Inventory Item", "Device_Type_Abbreviation = '" & abb & "'"), 0) + 1
End If

End Sub

Private Sub Device_Type_Abbreviation_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Device_Type_Number_BeforeUpdate(Cancel As Integer)

End Sub

Maybe we're getting closer...

 

[MajP]

The number is bound to itself.

Is your combo called cmboType? My guess it is not. To test if the event is firing I put in a messagebox. Delete that line if it works.

Private Sub YOUR_COMBO_NAME_HERE_AfterUpdate()
Dim abb As String
abb = Nz(Me.cmboType, "")
If Not abb = "" Then
msgbox "You selected" & abb
Me.Device_Type_Number = Nz(DMax("Device_Type_Number", "New Inventory Item", "Device_Type_Abbreviation = '" & abb & "'"), 0) + 1
End If

End Sub

 

[cbk10]

Ah, now we're making progress.

I'm getting generated numbers in my Device Type Number textbox, but they aren't incrementally increasing.

Present code is:

Option Compare Database

Private Sub Command12_Click()

End Sub

Private Sub Detail_Click()

End Sub


Private Sub Device_Type_Abbreviation_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Device_Type_Abbreviation_AfterUpdate()
Dim abb As String
abb = Nz(Me.Device_Type_Abbreviation, "")
If Not abb = "" Then
MsgBox "You selected" & abb
Me.Device_Type_Number = Nz(DMax("Device_Type_Number", "New Inventory Item", "Device_Type_Abbreviation = '" & abb & "'"), 0) + 1
End If

End Sub

Private Sub Device_Type_Number_BeforeUpdate(Cancel As Integer)

End Sub


I also set the primary key for the table to both Device Type Abbreviation and Device Type Number; let me know if this should be changed. Again, you have been most helpful.

 

[MajP]

Yes it is a composite primary key consisting of two fields that together uniquely identify the record. If you want (and I would) you can add an autonumber field and make that the only PK. You will still want to put a unique index on the combined other two keys.

It probably is not incrementing because it is not finding a matching record.
Does the messagbox show the correct selection? What does the message box show?

 

[cbk10]

Will do concerning the indexes and primary keys.

The message box is working. It says You selected ___, So far I'm selecting AMW, so it's You selected AMW, so that is working. I like that function, so I'll probably keep it in the DB lol.

I've also noticed if I create a record and get AMW-1, then a second record, it will genenerate AMW-1 again, but if I select AMW on the second record a second time, then the number goes up by 1. What to do next...

 

[MajP]

For a test
1)Create a record
2)from the menu bar choose "Record" "Save"
3) Create new record

if this works post back.

 

[cbk10]

Tested, and still same as before. Plan B?

 

[MajP]

What "is not happening" problems are real hard to debug without seeing. Can you post a demo and i will look. I use 4shared.com as a free file sharing site.

 

[cbk10]

I understand. I used the site you suggested and have the file available at this link:

http://www.4shared.com/file/4dEHprln/REMEDY_Inventory_Web.html

I hope it isn't too convoluted to figure out...

 

[MajP]

Here is the problem.

NEVER, EVER, EVER, EVER use spaces in names unless you enjoy sticking pins in your eyes.
I think I said this already.

if you use spaces then all code requires [] around the name.

Private Sub Device_Type_Abbreviation_AfterUpdate()
Dim abb As String
Dim strWhere As String
abb = Nz(Me.Device_Type_Abbreviation, "")
If Not abb = "" Then
MsgBox "You selected " & abb
strWhere = "Device_Type_Abbreviation = '" & abb & "'"
Me.Device_Type_Number = Nz(DMax("[Device Type Number]", "[New Inventory Item]", strWhere), 0) + 1
End If
End Sub


The fact that it did not throw an error when it did not find this field Device Type Number suprises me. Use under_scores or camelBackNotation.

 

[cbk10]

Ah, that does make a big difference doesn't it? Considering this is my first Access project, I won't forget that next time for sure.

It seems to work fine now except for one problem: I can select AMW-1 for my first record, and then AMW-2 is genereated for my second record, but when I tested a third record and selected APW, I got APW-3 rather than APW-1. I hate to ask for even more help, but what could be causing that?

 

[MajP]

When I get a chance I will take a look. It sounds like a problem in the criteria. If the criteria is incorrect possibly a name issue it finds the max number not the max number where the abbreviation is equal to some value.