Sequential numbering for each prefix?

[Sam Summers]

Hi,

I have a combobox (SubSection) and when it is updated it uses this code:-

Private Sub DROPSSubSection_Click()

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

Me.Item = [Prefix] & "" & [Number]

End Sub

which should create a unique number in the Item field which concatenates both the associated prefix and a generated number.

What I am trying to do with the code below is generate a number beginning from one for each prefix?

The prefix letters are uppercase and may contain from 2 to 4 letters.

Any idea where I am going wrong? Many thanks in advance!

Private Sub Form_Current()

    If Me.Ref = 0 Or IsNull(Me.Ref) Then
        'Me.Ref = Nz(DMax("Ref", "TOItem"), 0) + 1      'Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), 0) + 1
        Me.Ref = Nz(DMax("Ref", "TOItem", "Prefix = " & Chr(34) & Ref & Chr(34)), 0) + 1
    End If

End Sub

 

[NauticalGent]

Hi Sam,

I am a little unclear as to what you are trying to do. Do you have a lookup table that stores the latest or next sequential number for a corresponding prefix?

 

[Sam Summers]

Hi there,

What I have is this table as attached?

What I would like to do for each individual type as selected in the SubSection combobox, would be to number consecutively for each group of say CR.

So CR001, CR002 and or AF001 etc.

 

[NauticalGent]

Ok,

In my opinion, the best way to do this would be to add another column. It would be a number that you could format at the form level. This column would either hold the next number to use or last number used for that group.

When a new record is made, you could use the DLookup() or ELookUp() function to determine what the value would be and then increase the value in that field for the next record.

Here is an example I use for one of my form's OnOpen Event:

Private Sub Form_Open(Cancel As Integer)
Me.DataEntry = True
Me.DCN = "DCN" & Format(DLookup("LastDCN", "series"), "000")
Me.AllowAdditions = False
End Sub

After the user saves the record, I use a simple update query to increment the LastDCN field in my Series table.

Attached is a screen shot of the query. Not all that sophisticated, but it works...

 

[Sam Summers]

Ok, thank you!

I will have a go at that and see if I can get my head round it!

I will post the results

 

[NauticalGent]

You're welcome and please do. As always, don't hesitate to ask if you get stuck...

 

[Sam Summers]

OK, I created all that and tried it and i'm getting the error message as attached at the point in the code as also attached?

I'm going to try and figure it out?

 

[Sam Summers]

OK, I changed the fields DCN and LastDCN to text fields and that stopped the error message, however - a couple of things aren't quite right due to my misunderstanding and lack of knowledge.

In the textbox 'DCN' I get "DCN" displayed and it is obviously from this line of code

Me.DCN = "DCN" & Format(DLookup("LastDCN", "series"), "000")

so I am not sure what I am doing with this portion.

And also, I tried to run the query with the code below and it just freezes?
The data does not enter the combobox and nothing happens?

Private Sub DROPSSubSection_Click()

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

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

End Sub

 

[Minty]

You should really do these events in the after update rather than click events.
I would assume that your query is freezing - comment out the openqry command and I suspect the rest will work.

Me.DCN = "DCN" & Format(DLookup("LastDCN", "series"), "000")

The format for a DLookup is ("SearchField","Table or query Name", "Search Criteria")
Without criteria it si simply going to return the first "LastDCN" record it finds which is probably blank / null.

 

[Sam Summers]

OK, I moved the code to the AfterUpdate Event.

I'm still confused as to what i'm doing with this line of code:

Me.DCN = "DCN" & Format(DLookup("LastDCN", "series"), "000")

The highlighted portion displays exactly that (DCN) in the textbox 'DCN' which is not supposed to happen?

Getting lost in this one.....???? but slowly learning

 

[Minty]

The second part
Format( DLookup("LastDCN", "series"), "000")

I suspect is returning nothing because you aren't specifying a criteria in the DLookup
Read up here! : http://allenbrowne.com/casu-07.html

 

[NauticalGent]

Sam,

DCN is a literal that I want placed in my field which is why it is in Quotes: "DCN". Think of it as one of your prefixes, such as "AF" or "CR".

 

[Sam Summers]

So do I populate the series table with all my prefixes first?

Here is the SQL for the query as you gave me:

UPDATE Series SET Series.LastDCN = [Series].[LastDCN]+1;

 

[Sam Summers]

Further to my last post.

Seeing as I am totally confused, here is the code for all of the major functions on the form so you can see what I am trying to achieve:

Private Sub DROPSSubSection_AfterUpdate()

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

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

End Sub

Private Sub Form_Current()

    'If Me.Ref = 0 Or IsNull(Me.Ref) Then
        'Me.Ref = Nz(DMax("Ref", "TOItem"), 0) + 1      'Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), 0) + 1
        Me.Ref = Nz(DMax("Ref", "TOItem", "Prefix = " & Chr(34) & Ref & Chr(34)), 0) + 1
    'End If

End Sub

Private Sub Form_Open(Cancel As Integer)

 Me.DataEntry = True
 Me.DCN = Me.Prefix & Format(DLookup("LastDCN", "series"), "000")
 'Me.AllowAdditions = False

End Sub

Private Sub ItemSaveBtn_Click()

    On Error Resume Next
    
    If IsNull(Description) Or IsNull(Frequency) Or IsNull(Photograph) Or IsNull(Condition) Then
        DoCmd.Beep
        MsgBox "All required fields must be completed before you can save a record.", vbCritical, "Invalid Save"
        End
    End If

    Dim Msg, Style, Title, Response, MyString
    DoCmd.Beep
    Msg = "Are you sure these details are correct?"
    Style = vbYesNo + vbQuestion + vbDefaultButton1
    Title = "Confirm Details"  ' Define title.
    Response = MsgBox(Msg, Style, Title)
    
    If Response = vbYes Then       ' User chose Yes.
    'If Me.Ref = 0 Or IsNull(Me.Ref) Then
    'If IsNull(Ref) Then
    '    Me.Ref = Nz(DMax("Ref", "TOItem"), 0) + 1
    Me.Ref = Nz(DMax("Ref", "TOItem"), 0) + 1
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close acForm, "EnterItemDROPS", acSaveYes
    DoCmd.OpenForm "EnterItemDROPS", acNormal
    Me.Refresh
    Else
    
End If

 

[NauticalGent]

Sent you a PM

 

[Sam Summers]

I have been doing some thinking and have this idea -

If I create a table for each Prefix like my screenshot attached then is there a way to maybe lookup or reference the prefix column of my combobox (SubSection) and therefore as another item with the same Prefix is selected then the Prefix table will sequentially add a number under the prefix, hence achieving separate ranges of numbers for each prefix?

 

[Minty]

If you are going to do this, and it's not a perfect solution, I would create a table the other way around called tblPrefixes with two fields PrefixTxt, LastValue.
Make PrefixTxt the Primary key, so you can't enter a duplicate Prefix, then you can add as many Prefixes as you need and l not need to hard code anything. You can then lookup the next prefix value either by building a function or using a DLookup().

 

[Sam Summers]

But each prefix wouldn't start at 001 then?

At the moment I am using this:

Private Sub Form_Current()

    If Me.Ref = 0 Or IsNull(Me.Ref) Then
        Me.Ref = Nz(DMax("Ref", "TOItem"), 0) + 1
    End If

End Sub

But it seems to have stopped at 10 for some reason? Almost as if it cant go past single digits?

 

[Minty]

But each prefix wouldn't start at 001 then?

No because you set the initial value in the table when you set it up.

Private Sub Form_Current()

    If Me.Ref = 0 Or IsNull(Me.Ref) Then
        Me.Ref = Nz(DMax("Ref", "TOItem"), 0) + 1
    End If

End Sub

But it seems to have stopped at 10 for some reason? Almost as if it cant go past single digits?

I suspect you have stored Ref as text ?The Max() value of a text field won't calculate correctly as you want it to.

 

[Sam Summers]

I will get this eventually and when I do it will help others but right now its way over my head.

I will explain what I have:

In my Table 'TOItem' I have:

Item - text field displaying the whole number I want like TDS002
Prefix - text field which displays the essential prefix like TDS
Sequence - Numeric field

On my form I have:

Prefix which is bound to Prefix in the Table.

I have just been trying to get this to work which is from this article https://scottgem.wordpress.com/2009/11/25/sequential-numbering/

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

But it is not working and I just cant get my head round it?