AutoNumber and Input Mask question

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?

re: AutoNumber and Input Mask question

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.

re: AutoNumber and Input Mask question

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

re: AutoNumber and Input Mask question

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.


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.

re: AutoNumber and Input Mask question

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

re: AutoNumber and Input Mask question

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

re: AutoNumber and Input Mask question

That's because strID is a string and lngNum is a number.
You will need to strip the number part off strID.
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 where [strProdCode] is the control you enter the AAA part into

re: AutoNumber and Input Mask question

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

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.

re: AutoNumber and Input Mask question

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...

re: AutoNumber and Input Mask question

Ok I made some changes and got it working.

First I made a query called ProjectCode;
Then I changed the code on the form to;
This solved the problem for me.

re: AutoNumber and Input Mask question

One quick question I hope you can help with.


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.

re: AutoNumber and Input Mask question

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.

re: AutoNumber and Input Mask question

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.

re: AutoNumber and Input Mask question

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
If you move it from line 10 and put it in after line 7.
You can then use it in the DMax() function.
I've not tested this, but I will try and keep up with you today

re: AutoNumber and Input Mask question

Sorry, I mistyped the code. instead of strID, you should use lngNum
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()

re: AutoNumber and Input Mask question

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: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?