Autonumbering

[Ytzchak Buyanovsky]

Hi everybody!
The question is:
I have two tables:
Family Table:
Fields:FamilyID (Number)
FamilyName (Text)

Product Table:
FieldsroductID (AutoNumber)
FamilyID (Number)
PartNumber (Text)
This last field is composed by:
3first digits corresponding to the family, 4 digits and 2 letters.
Example:345 0001 AA
In a form "FormName" I want to pick a family from a combo box and run a procedure for adding a new PartNumber on Products table.
I think part of the process is:
SELECT (MAX(PartNumber)+1) FROM Products WHERE FamilyID=Me![FormName].[FamilyID]
THis is correct?
Can anybody help to me?
Thanks.


[This message has been edited by Ytzchak Buyanovsky (edited 08-05-2001).]

 

[shacket]

There are a couple of things to talk about here. Without knowing a little more information, it is tough to give you a complete answer.

The first thing I would say is that the best database designs do not repeat information. What I mean by that is, rather than include the FamilyID as part of the PartNumber, you may want to have the PartNumber as just the 4 digits and 2 letters without the FamilyID. On a form or report, you could concatenate the FamilyID with the PartNumber to get the final product you want without duplicating the FamilyID in two fields.

The only reason you would want to do that is if there is some place you need the part number where the FamilyID is not available. But as they are both in this same table, it should always be accessible.

The questions I have are this:

1. Is the 4 digit number (0001 in your example) an autonumber? Do you simply want that to incrementally increase?

2. Where do the last two letters come from?

I would leave the FamilyID field as it is, and create two more fields: an autonumber for the 4 digit number (assuming an autonumber is what you need) and another field for the letters. How you populate that field will depend on where the letters come from. Then you can bring the 3 fields together wherever you need them.

I hope that helps.

Dave

 

[Ytzchak Buyanovsky]

Dave, first of all thanks a lot.
The question is like this

The four digits 0001 must increase according to the family that belongs to.
Ex: 375 0001 AB next 375 0002 AB
412 0001 AB next 412 0002 AB
Family Numbers Letters

The user input the letters freely

 

[shacket]

I think SQL (as you are trying to do) is the way to go. Unfortunately, I don't know SQL too well and am not exactly sure how to implement it like you are talking about.

What I think you would do is, when you add a record, use the SQL statement to find the greatest number according to the family:

Dim NextNumber As Long
NextNumber = SELECT (MAX(PartNumber)) FROM Products WHERE FamilyID=Me![FormName].[FamilyID]

(notice: I took the +1 out)

Then add 1 to that number and put it in the field you want:

NextNumber = NextNumber + 1
Me!txtNumber = NextNumber

You may need to play around with the SQL statement and getting SQL to retrieve that number and assign it to a variable.

Or is that what you were asking about in the first place?!

I hope that helps. Let me know if you get it working or if you want me to play around with SQL for you.

Dave

 

[Ytzchak Buyanovsky]

I think there is a way like this:
make a temporary table with the PartNumber that correspond to the family inputed.
Then make a variable with the "numeric part" of the middle, mid()?. Ex:a=0001 convert to numeric and add 1: a+1=0002.
Afterwards bring the string family+number+letters all together.
I tried with the SQL but didn't get anything yet.

 

[shacket]

If you do that, don't bother with a temporary table. A variable in VBA can handle a number.

To get the SQL, simply create a query with the MAX function and look at the SQL that the query creates (in SQL view).

However, while SQL is the best way, if you can't get it to work, you can cycle through with VBA. PLEASE NOTE: this is not the "correct" way, but it works if you absolutely can't figure out the SQL.

It would look something like this:

Dim db As Database
Dim rs As Recordset
Dim id As Long
Set db = CurrentDb()
Set rs = db.OpenRecordset("ProductTable",dbOpenDynaset)

rs.FindFirst "[FamilyID] = " & Me.FamilyID
If rs.NoMatch Then Exit Sub 'You may want to do something more here
Set id = Mid(rs!PartNumber,5,4)
'NOTE: This will only work if you have a 3 digit FamilyID for everyone. Once you have a 4 digit FamilyID, creating this statement becomes MUCH more complicated.


NextID:
rs.FindNext "[FamilyID] = " & Me.FamilyID
If rs.EOF Then GoTo AddNumber
If Mid(rs!PartNumber,5,4) > id Then
Set id = Mid(rs!PartNumber,5,4)
End If
GoTo NextID

AddNumber:
Let id = id + 1

rs!PartNumber = rs!FamilyID & " " & id & " " & [LetterInput]


It is not the perfect scenario or is my code perfect - just use it as an example. Again, it is much better to use SQL.

 

[Ytzchak Buyanovsky]

I was working with the SQL to retrieve the PartNumber filtered by FamilyID.
I wrote this codepiece in get focus event in a form "New Products" that has 3 fields.
Field1roductID (Autonumber)
Field2:FamilyID(Number)combo box with an SQL where i see the family names)
Field3artNumber (Text)
I pick one family name (number)and i want to set the rsPartNumber value.

Dim db As Database
Dim rsPartNumber As Recordset
Dim i As Integer

Set db = CurrentDb()
Set rsPartNumber = db.OpenRecordset("SELECT * FROM Products WHERE PartNumber Like " & "Me![FamilyID] & "*"")
PartNumber = rsPartNumber!PartNumber
I don't know where is the problem with this SQL but I get "FamilyID+*" not "FamilyID+numbers+letters"

 

[shacket]

Your SQL statement (in the Set rsPartNumber statement) will retrieve more than one record, right? You want to find only the single family record with the highest part number. I don't see in your code where that would happen (either in SQL or VBA).

 

[joeyreyma]

i've been reading this post since it started, maybe i can make some help. here's what i will do:

1. i will make a saved query that will contain the maximum for every family, eg:

SELECT Left$([FamilyId],3) AS FamilyName, Max(CLng(Mid$([FamilyId],4,4))) AS LastMaxEntry
FROM Table2
GROUP BY Left$([FamilyId],3);

let say i call this qryMaxFamilyId

2. on after update code of adding part number, i can say:

Forms!PartNumberFormName!NewFamilyId = Me!FamilyId & " " & Format(Nz(Dlookup("LastMaxEntry","qryMaxFamilyId","FamilyName ='" & Left$(Me!FamilyId,3) & "'"),0) + 1,"0000") & " " & LetterInput

am not very sure of the name of the objects you use, so just play around with it. anyway, my point here is that whenever you have to retrieve a max or min or count, aggregates etc... for a specific Id, it is always better to do it in a saved query so that access can optimize the operation. Dlookup is not the best function to use here but since you already have an optimized query as a source, the delays using DLookUp won't be much of a problem.

HTH

[This message has been edited by joeyreyma (edited 08-14-2001).]

[This message has been edited by joeyreyma (edited 08-14-2001).]