New sequential # (1 Viewer)

[omer123456]

Hi,

I am using Access 2010

We have 3 plants that use the same access D/B. The plants are 22, 33, 44. We have a generic form that launches where operators from each plant can input their samples daily.

Example:
operator from plant 22 will log in their samples as 22-101, 22-102, 22-103, 22-104..... and the sequesnce continues for that plant (Notice the operator keeps a track of next sequential #)
operator from plant 33 will log in their samples as 33-101, 33-102, 33-103, 33-104..... and the sequesnce continues for that plant (Notice the operator keeps a track of next sequential #)
operator from plant 22 will log in their samples as 44-101, 44-102, 44-103, 44-104..... and the sequesnce continues for that plant (Notice the operator keeps a track of next sequential #)

All this data is currently being saved into a table as text entry.

What I would like when the form loads is to have ACCESS assign the next sequential #, rather than the opertor. Since this table is currently saving all 3 plants records How will access determine next sequential # for plant 22?
We could setup a drop down menu, so when the operator chooses the plant...... Access knows it from that plant and assign the next sequential #

Any help would be appreciated. If you have an example, please load it.

 

[plog]

You need to store data correctly. You're doing it incorrectly in 2 ways. First, every discrete piece of data needs its own field. That means this field needs to be 2: one for the plant and one for the sample. Second, since the sample data is numeric and you want to do numeric operations on it (determine next number), the field it is stored in needs to be numeric.

Once the table is set up like that you can implement your solution. Create a drop down for the plant, then you pick an event for that drop down (i.e. AfterUpdate, OnDirty, OnChange) and you write code to occur when that event happens.

In general, your code would take the value of the plant, then use a DMax (http://www.techonthenet.com/access/functions/domain/dmax.php) to get the highest sequence number for that plant. It would then add 1 to that number and place it in the input box for the sequence number.

 

[omer123456]

Since I am not familiar with VBN Code, can you do a smalll example and post??

Thx

 

[plog]

Here's some code that will return the highest sequence number in the table:

Sub  getNextSequence()
    ' shows highest value in [Sequence] field in YourTableName

ret = DMax("[Sequence]", "YourTableName")

MsgBox(ret)

End Sub

 

[omer123456]

Do you know if in ACCESS 2010, this can be done without VBN?

I dont know how to manipulate VBN, hence I was wondering if under "properties" we could edit it?

Thanks

 

[plog]

This is going to take more than setting some properties, you actually have to get the highest sequence number, add 1 to it and then put that value in a form. VBA is the way to do this.

 

[omer123456]

Firstly, thanks for all the replies.

Do you the time / patience to write everything you want me to do...... since I am not sure where to srat, but if I had instructions I am sure I can follow.

OR

I can send you the D/B and you do it, then I will be able to follow. I wont send you actual D/B but rather one I created just to see how to do it

 

[plog]

I don't think you could afford my rate. How about you start to give it a shot and I'll help you when you get stuck.