AutoNumber and Input Mask question

[damonreid]

Is it possible to make a field that is not a number field into an auto number?
I shall try to explain better.

I have a field with the mask AAA-00-000 where AAA is a category code, the first set of numbers is the year (e.g. 07 for this year) and the final set of numbers is a auto number relating to both the category code and year (you should be able to have AAA-06-001, AAA-07-001 and BAA-07-001) but instead of having people look up the next number themselves it would be to nice to have it auto populate once they pick the category code.

Is this possible?

[kepston]

On your form, in the AfterUpdate event of your category control, set the DefaultValue for your AAA-00-000 field.
You will probably need to use the Dmax() function to get the highest value in use.

[damonreid]

Thank you very much for this pointer.
I will get around to this early next week and will report back on how it goes.

[hyperpau]

Thank you very much for this pointer.
I will get around to this early next week and will report back on how it goes.

I have done an access file like this.
I have a form populate an autonumber in this format:

HPES 07070001

where the first four numbers are the year and month respectively
then the last four are autonumbers incrementing by one.

If this is what you want, I can give you pointers.

on your tabel, make sure you have an autonumber field. But you would
not use this field for your customized autonumber. It's just there
to be as an index (primary key) of your records.

in your table, add two additional fields aside from the autonumber field.
1st field should be a number with long integer as the format.
2nd field should be String. This is where we would record the customized
autonumber.

Now, I would assume that your form would be opened from a switchboard
in add mode. (meaning, you cannot go to other records and you you go to a new record as soon as the form loads)

let's say you name the 1st field (the number field) " lngNum " and the second
field (string field) as " strID ".

Add this on your form_load() event.

Private Sub Form_Load( )
 
If Me.NewRecord Then
 
     ' Increment the lngNum field by One, if field is Null, set as 0 then add 1
     Me!lngNum = Nz(Dmax("[strID]", "NameOfTable"), 0) + 1
 
     'Customize the ID with Year, then attach the incremented lngNum
     Me!StrID = "AAA-" & Format(Date, "yy") & Format(Me!lngNum, "0000")
 
Else
 
     Me!StrID = Me!StrID
 
End If
 
End Sub

Now as soon as you open that form in add mode, it automaticaly generates that customized ID you want, and increments it by 1. :)

Hope it works for you.

[missinglinq]

As stated, you should use Dmax() to generate your own incrementing number. You should really never use an Autonumber datatype for any field that will be manipulated by the user or thru code. Autonumbers are meant to be used for "housekeeping" by Access! They're too many things that can mess up their progression!

Linq

[damonreid]

Private Sub Form_Load( )
 
If Me.NewRecord Then
 
     ' Increment the lngNum field by One, if field is Null, set as 0 then add 1
     Me!lngNum = Nz(Dmax("[strID]", "NameOfTable"), 0) + 1
 
     'Customize the ID with Year, then attach the incremented lngNum
     Me!StrID = "AAA-" & Format(Date, "yy") & Format(Me!lngNum, "0000")
 
Else
 
     Me!StrID = Me!StrID
 
End If
 
End Sub

This is great, however after the first addition I get an error (I can enter AAA-07-0001 but when I go to add another number, AAA-07-0002 I get the following error:

Run-time error '13':

Type mismatch


Relating to the following code

Me!lngNum = Nz(Dmax("[strID]", "NameOfTable"), 0) + 1

[kepston]

This is great, however after the first addition I get an error (I can enter AAA-07-0001 but when I go to add another number, AAA-07-0002 I get the following error:

Run-time error '13':

Type mismatch


Relating to the following code

Me!lngNum = Nz(Dmax("[strID]", "NameOfTable"), 0) + 1

That's because strID is a string and lngNum is a number.
You will need to strip the number part off strID.

Me!lngNum = Nz(Dmax("Right(strID,4)","NameOfTable"),0) + 1

When you have multiple product codes, you should also include a criteria for the left part of strID, so that you are only looking for the max value for the current product and year i.e. the AAA-00 part
Something like

strCriteria="Left([strID],6)=[strProdCode] & '-' & Format(Date,'yy')"

where [strProdCode] is the control you enter the AAA part into

[damonreid]

Ok so I have changed the code so there is a Combo Box called PrgCodeID and when you select an ID it should pick a project number based on that ID.

AAA-07-0001
Where;
AAA is the Project Code ID
07 is the year
001 is the number that increases

Private Sub PrgCodeId_AfterUpdate()
Dim strProdCode As String
 
    If Me.NewRecord Then
    strProdCode = Me.PrgCodeId.Value
 
         ' Increment the lngNum field by One, if field is Null, set as 0 then add 1
         'Me!lngNum = Nz(DMax("[strID]", "Project Details"), 0) + 1
         Me!lngNum = Nz(DMax("Right(strID,4)", "Project Details"), 0) + 1
         'Customize the ID with Year, then attach the incremented lngNum
         strCriteria = "Left([strID],6)=[strProdCode] & '-' & Format(Date,'yy')"
         Me!strID = Me.PrgCodeId.Value & "-" & Format(Date, "yy") & "-" & Format(Me!lngNum, "000")
 
    Else
 
         Me!strID = Me!strID
 
    End If
End Sub

I have tried to change the code to reflect this but what happens now is every code returns XXX-07-000 regardless of how many times I implement it.

I have;
ABC-07-000
ABD-07-000
ABC-07-000
ABC-07-000
ABD-07-000
in the table now.

[damonreid]

Ok I am an idiot... changed the 4 to a 3.

I am still getting the next number for all codes and not just the particular one now.


ABC-07-002
ABC-07-003
ABB-07-004
ABC-07-005
ABB-07-006
ect...

[damonreid]

Ok I made some changes and got it working.

First I made a query called ProjectCode;

SELECT [Project Details].autonumber, [Project Details].lngNum, [Project Details].strID
FROM [Project Details]
WHERE ((([Project Details].strID) Like [Forms]![AddNewProject]![PrgCodeId] & "*"));

Then I changed the code on the form to;

Private Sub PrgCodeId_AfterUpdate()
Dim strProdCode As String
 
    If Me.NewRecord Then
    strProdCode = Me.PrgCodeId.Value
 
         ' Increment the lngNum field by One, if field is Null, set as 0 then add 1
         Me!lngNum = Nz(DMax("Right(strID,3)", "ProjectCode"), 0) + 1
         'Customize the ID with Year, then attach the incremented lngNum
         strCriteria = "Left([strID],3)=[strProdCode] & '-' & Format(Date,'yy')"
         Me!strID = Me.PrgCodeId.Value & "-" & Format(Date, "yy") & "-" & Format(Me!lngNum, "000")
 
    Else
 
         Me!strID = Me!strID
 
    End If
End Sub

This solved the problem for me.

[damonreid]

One quick question I hope you can help with.

Me!lngNum = Nz(DMax("Right(strID,3)", "ProjectCode"), 0) + 1

If strID had a space in it say str ID how would I put that in this line? I have tried and _ and single and double quotes around it but none of the appear to work.

[JKing]

If your field name has spaces in it use square brackets. i.e. [Field With Many Spaces] Otherwise the compiler will see a variable for each word in your fieldname.

[damonreid]

The [] didn't work either... strange.

What I have tried to do is get the string using the strID field and then enter that information into my Project Number field. This is a little cumbersome but appears to work.

Private Sub PrgCodeId_AfterUpdate()
Dim strProdCode As String
 
    If Me.NewRecord Then
    strProdCode = Me.PrgCodeId.Value
 
        ' Increment the lngNum field by One, if field is Null, set as 0 then add 1
        Me!lngNum = Nz(DMax("Right(strID,3)", "ProjectCode"), 0) + 1
        'Customize the ID with Year, then attach the incremented lngNum
        strCriteria = "Left([strID],3)=[strProdCode] & '-' & Format(Date,'yy')"
        Me!strID = Me.PrgCodeId.Value & "-" & Format(Date, "yy") & "-" & Format(Me!lngNum, "000")
 
 
 
    Else
 
        Me!strID = Me!strID
 
    End If
        Me.Project_Number = strID.Value
End Sub

[kepston]

The [] didn't work either... strange.

What I have tried to do is get the string using the strID field and then enter that information into my Project Number field. This is a little cumbersome but appears to work.

Private Sub PrgCodeId_AfterUpdate()
Dim strProdCode As String
 
    If Me.NewRecord Then
    strProdCode = Me.PrgCodeId.Value
 
        ' Increment the lngNum field by One, if field is Null, set as 0 then add 1
        Me!lngNum = Nz(DMax("Right(strID,3)", "ProjectCode"), 0) + 1
        'Customize the ID with Year, then attach the incremented lngNum
        strCriteria = "Left([strID],3)=[strProdCode] & '-' & Format(Date,'yy')"
        Me!strID = Me.PrgCodeId.Value & "-" & Format(Date, "yy") & "-" & Format(Me!lngNum, "000")
 
 
 
    Else
 
        Me!strID = Me!strID
 
    End If
        Me.Project_Number = strID.Value
End Sub

Let me apologise for the error in post #7, which you corrected by post #9.
(You were getting 4 characters because it was appending the '1', and not increasing by 1)
Anyway, continuing...
The strCriteria isn't currently being used anywhere.
Change it to

strCriteria = "Left([strID],3)='" & [strProdCode] & "-' & Format(Date,'yy')"

If you move it from line 10 and put it in after line 7.
You can then use it in the DMax() function.

DMax("Right(strID,3)", "ProjectCode", strCriteria)

I've not tested this, but I will try and keep up with you today

[hyperpau]

Private Sub Form_Load( )
 
If Me.NewRecord Then
 
     ' Increment the lngNum field by One, if field is Null, set as 0 then add 1
     Me!lngNum = Nz(Dmax("[strID]", "NameOfTable"), 0) + 1
 
     'Customize the ID with Year, then attach the incremented lngNum
     Me!StrID = "AAA-" & Format(Date, "yy") & Format(Me!lngNum, "0000")
 
Else
 
     Me!StrID = Me!StrID
 
End If
 
 
 
 
 
End Sub

This is great, however after the first addition I get an error (I can enter AAA-07-0001 but when I go to add another number, AAA-07-0002 I get the following error:

Run-time error '13':

Type mismatch


Relating to the following code

Me!lngNum = Nz(Dmax("[strID]", "NameOfTable"), 0) + 1

Sorry, I mistyped the code. instead of strID, you should use lngNum

Me!lngNum = Nz(Dmax("[lngNum]", "NameOfTable"), 0) + 1

you will just make it so complicated if you use the Left or Right methods.

Again, this code will only work when the form loads in add entry mode. If you would like to add a new entry, you must close the form and reopen it in add entry mode.
If you want to add a new record without closing the form, try changing the Form_load() to Form_current()

[kepston]

you will just make it so complicated if you use the Left or Right methods.

Quite right! And presumably less efficient.

The criteria is still required though, otherwise you will have the same situation as in post #9 with all project codes running in one sequence rather than each having it's own.

Aside:

Again, this code will only work when the form loads in add entry mode. If you would like to add a new entry, you must close the form and reopen it in add entry mode.
If you want to add a new record without closing the form, try changing the Form_load() to Form_current()

Would OnCurrent increase the number on every visit to the record?
Equally, if the Form was opened to Edit, would the first record be altered?

[emparardpooft]

Lama ani lo yahol liktov beivrit?






כושר