Automatic Numeric Problem

solty89

Registered User.
Local time
Yesterday, 23:38
Joined
May 20, 2013
Messages
32
Hi everyone,
I'm new into doing queries for MS Access and I don't have an idea how to program the following issue.
So for example I have quantity of products into column A.They are sorted good, but i need to give automatic number (into column B) per every product, depend on the sorting as follow:
A.... B
1.... 1
1.... 2
3.... 3,4,5
1.... 6
2.... 7,8
1.... 9
So I can do that in Excel, becouse i can say whats in the previous cell, but i dont know how to doit in Access.
 
I don't get why you would be doing this.
Surely every row should have one autonumber (primary key) then you can have another column for quantity.

For example:
Autonum Product Quantity
1 Product 1 2
2 Product 2 1
 
look more deep into my ask...i dont need Id per row, but ID per product,and the products can be more than 1 into a row
 
look more deep into my ask...i dont need Id per row, but ID per product,and the products can be more than 1 into a row
That's why I don't get it, why not only have one product per row? Then you can have a column for quantity?
 
becouse this is just example...they are not really products...i will describe u exatly my problem.I work in a factory which produce many products.I need to give to the employes a list with products from the same group.Every product have quantity and depend on this quantity i have query which say "how many boxes they need for this product".The products are sorted and depend on the sorts, I need to give unique srial Number to the boxes.So if product1 needs 2 boxes, product1 has to go into boxNo:1 and boxNo:2, and if poruduct2 needs 3 boxes, product2 has to go into boxNo:3, boxNo:4 and boxNo:5.
So, how to give this serial unique number to the boxes?
 
Last edited:
Ok if you must do it this way, I would create a function to create a serial number and use the function in a make table query to add a new column for the serial.
 
no.look at my 1st post.A is quantity,B is serial number of the boxes.
 
yes I did a seral function.but if i take my first post for example now it looks like this:
A.... B
1.... 1
1.... 2
3.... 3
1.... 4
2.... 5
1.... 6
but it should looks like on the 1st post
 
Lol, then you didn't do the serial function correct!
I'm not sure how to do it myself, I'll have a quick look.

p.s. when I say create a serial function, I'm talking about creating a module specific to your problem!

Edit: Ok, created a function which works although I had to cheat to find the last filled box so it's useless right now!

Here's the function:

Code:
'Function used to create unique serial number for solty89
Public Function getSerial(CurrentNumber As Integer, Quantity As Integer) As String
'....................................................................
' Name:     getSerial
' Inputs:   CurrentNumber As Integer
'           Quantity As Integer
' Returns:  String
' Author:   pwbrown @ [URL]http://www.access-programmers.co.uk/[/URL]
' Date:     May 20, 2013
' Comments: Current number will be the last box filled
'....................................................................
On Error GoTo Err_getSerial
 
Dim serial As String
 
Do While Quantity > 0
 
    serial = serial & CurrentNumber + 1
    CurrentNumber = CurrentNumber + 1
 
    Quantity = Quantity - 1
 
    If Quantity > 0 Then
        serial = serial & ", "
    End If
 
Loop
 
getSerial = serial
 
Exit_getSerial:
Exit Function
 
Err_getSerial:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_getSerial
End Select
 
End Function

If you can pass through the last used box it would be perfect so for example:
ID Product Quantity LastboxUsed
1 Prod1 1 0
2 Prod2 3 1
3 Prod3 2 4
4 Prod4 4 6

Then the output would be:
1
2, 3, 4
5, 6
7, 8, 9, 10
 
Last edited:
Solty did you get this issue sorted?

hey man,
im really new into vba codinng.So these days i was reserching and reading tutorials about VBA coding.Can you help me a bit with it couse i cant understand everything , but i need to finalise it soon.
 
Unfortunately I deleted the test program I created because I thought you weren't coming back after not responding.
Anyway maybe someone else can help because I'm unsure on how to pass the past box used through. May need another function and use count in a query.
 

Users who are viewing this thread

Back
Top Bottom