Sequential numbering for each prefix?

[Minty]

Split the function down into the separate parts and you will see what is wrong.

DMax(“[Sequence]”, ”TOItem”, [COLOR="Red"]”[Item] = '“ & Me.[Prefix]& “’”[/COLOR]),0)+1

Your criteria isn't likely to ever return something - you are trying to find a Item that matches your Prefix, which I don't think will ever happen?

 

[Sam Summers]

I'll come back to this another time and keep looking at other examples as I haven't a clue how this is put together or works?!

Its the icing on the cake if I can find a way to do it but its holding up the whole thing so if all else fails I will just revert to a basic 3 figure numbering system and attach the prefix to the front of the string.

It just means that the numbers wont start from 001 for each prefix but hey ho?

 

[Minty]

Did you create the table I suggested? If so use the following;

=DLookUp("LastValue","tblPrefixes","[Prefix]= '" & me.Prefix & "'")+1

And it might be worth formatting your number prefix with 4 or 5 leading zero's 0000 to accommodate bigger numbers?

 

[Sam Summers]

I did and I shall have a go at that.

As usual I have got bits of ideas all over the place and getting myself in a mess.

Thanks for your patience

 

[Sam Summers]

Ok, did that which compiled but when run, nothing happened so I have got something wrong?

Incidentally, how will this tie in with it now (when the user selects a sub-section)?

Private Sub DROPSSubSection_AfterUpdate()

Me.Prefix = Me.DROPSSubSection.Column(2)

Me.Item = UCase([Prefix] & "" & [TxtNumber])

End Sub

 

[gemma-the-husky]

Sam.

the logic of what you are trying to do is this.(I assume)

Given a prefix, find the highest number currently attributed to this prefix. Issue the next number.

I would do this

 function nextnumber(prefix as string) as long
 dim lastnumber as long
  
 lastnumber = nz(dmax("sequencenumber","thetable","prefix = " & chr(34) & prefix & chr(34)),0)
   'you need the nz() in case this is the first time you have used this prefix
  
 nextnumber = lastnumber+1
 'increment it, and return the new number
 end function

now, when you want a number just call

mynumber = nextnumber("BCT") 'or whatever

This is a slight variation of what posters describe as "use dmax to get the next sequence number"
note that you need to be careful when using this, as 2 users might get the same "nextnumber". Your nextnumber will not be seen by other users until you save the record. Therefore, the idea is to do this at the last possible stage, which should therefore generally be at the "beforeupdate" stage for the record.

 

[Sam Summers]

Ok, I will see what I can do with that?

Basically each subsection has its own prefix e.g. BD, TDS, UD, TPFE, CR etc. so the user selects from a combobox say Crown (prefix CR) then I was adding a 3 digit (Text) number for example 001.

What would be ideal (and is the current numbering method) is to consecutively number each prefix starting from 001.

So CR001, BD001 and so on - if possible

Many thanks guys.

I will let you know how I get on

 

[Sam Summers]

Well, created the function like so:

Option Compare Database
Option Explicit

 Function nextnumber(Prefix As String) As Long
 Dim lastnumber As Long
  
 lastnumber = Nz(DMax("Sequence", "TOItem", "Prefix = " & Chr(34) & Prefix & Chr(34)), 0)
   'you need the nz() in case this is the first time you have used this prefix
  
 nextnumber = lastnumber + 1
 'increment it, and return the new number
 End Function

and now I am getting the error message as attached after using this:

mynumber = nextnumber("Me.DROPSSubSection.Column(2)")

I'm fumbling around here???

 

[Minty]

Get rid of the quotes around the

mynumber = nextnumber(Me.DROPSSubSection.Column(2))

It will pass it as a string

 

[Sam Summers]

Well guys, I think I have to give up on this one?

I cant get it to work at all and I really haven't a clue what i'm doing

 

[gemma-the-husky]

sam. you must be nearly there.

maybe the problem is the combo box.

combobox.column(2) is actually the 3RD column in the combo box., since like nearly all access objects, a combo box column count is zero based.

perhaps you need combobox.column(1)

----

If you want to see what is going on, either
a) stick some temporary message boxes in your code.
add at the top
const showme as Boolean =true

then insert code
if showme then msgbox "some variable = " & myvar


just set showme to false when you don't need it any more.

----
or b)
add a breakpoint. click in the left margin of your code window. you will see a brown blob. when your code runs it will stop at that point. you can then hover over variables to see their values. Press F8 to advance one instruction at a time. It's really useful, and not so obvious.

 

[Sam Summers]

Hi Dave and thank you once again.

Its annoying not to beat this because it MUST be possible and would be helpful for others looking at this thread.

It will be something I am missing or not understanding at this stage.

The Combobox has 3 columns and it is the last one I was referring to.
They are:

0 - the subsection reference
1 - SubSection which is the subsection description
2 - the prefix

I will try the break points as you suggested.

There is something wrong with the whole function thing?
I keep getting the error as in the previous screenshot so I must be doing something wrong?

 

[Sam Summers]

Been racking my brains on how to solve this?

How about if I made a table or maybe a lookup table for each associated Prefix which would then have their own numbers sequential to the prefix?

Just an idea?

 

[Minty]

Could you post up a stripped down version of the db with where you are with this ? Zip it if necessary.

 

[Sam Summers]

Hi Minty,

I have removed all the reports.

The form where it all happens is the EnterItem forms I was mainly working on EnterItemDROPS

 

[gemma-the-husky]

Sam.

can you clarify

which table are you using to store the details
which field is the prefix
which field you are trying to populate

 

[Sam Summers]

Thanks Dave,

The main table is 'TOItem'
The two Lookup tables - 'TOSubSectionLookup' and 'DROPSSubSectionLookup' are were the actual Prefixes are which are referred to in the SubSection Comboboxes (SubSection and DROPSSubSection).

In the table 'TOItem' I am managing to store 'ItemNo' and 'Prefix'.

The fields 'DCN' and 'Sequence' are not currently functioning.

The tables - TblPrefixes and Prefix are currently also not being used.

Its not currently neat because I have been trying so many different things to achieve what I want.

Hope this helps to clarify it a bit?

 

[Sam Summers]

I may be on to something?

I have created a 'sequence' field in each of the SubSection Lookup tables (TOSubSectionLookup and DROPSSubSectionLookup).

And on the after Update Event of the corresponding SubSection Comboboxes I have put this line of code in but it is giving me errors at the moment?

Me.ItemTxt = Nz(DMax([Sequence], [TOSubSectionLookup], Me.[Prefix]), 0) + 1

Any ideas?

 

[gemma-the-husky]

Sam try this. I hope it helps you see what is going on.

I added a new form1 - there are some notes on the form
The form is bound to ToItems

It uses a query based on tblprefixes for the dropdown, which is also new.

You pick a prefix, and then it reads the next DNC for that prefix.


Everything else is locked.


The prefixes table is useful, as it prevents users adding their own incorrect prefixes.

 

[Sam Summers]

Hi Dave,

Thanks for your efforts.

Had a look at that and the problem is that the user wont know what the prefix is.
They select the actual subsection and the prefix is just an identifier that is added to the item number to separate the individual sub-sections.

That is why I was trying to do something with the columns of the subsection comboboxes as I could include the prefix in one of these columns.

Don't know what to do now?