Automate a field

[Maclain]

Hi all

I have another question.

Our user use a form to work a table.

One of the fields is the Job No field, with consists of the last two digits of the year followed by a 5 digit number. IE 08/10001

At the moment users have to input this field on every new record they create. What I would like to do is automate this entry.

so far I have the following:

I have created a Qry with the following SQL and called it qryMaxJobno:

    SELECT Max(Val(Right([job no],5))) AS [Max] FROM [Job Register and Report Log];

then set the default value in the form that updates to record to the following:

    =Right(Date(),2) & "/" & DLookUp("MAX","qryMaxJobno")+1

This works great.... until we hit 2009. The problem I have is i need to value after the / to reset to 00001 at the beginning of the new year.


Any clues how I might go about this?


TIA!

 

[pbaldy]

You'd add a criteria to the query that restricted it to looking at the current year. A shortcut to a query and then a DLookup is simply a DMax. You're also going to have to allow for the query or DMax not finding anything for that first record of the new year, so the typical setup is:

YourValue = Nz(DMax(...), 0) + 1

 

[Maclain]

Hi pbaldy,


excuse my ignorance, but you you elaborate on your solution a little more?

I'm a fairly novice access user

ta!

 

[pbaldy]

Sorry, I lost track of this. This kind of question comes up a lot. If you search here on "dmax year" you should find lots of threads doing the same type of thing. If you get stuck, post back and we'll sort it out.

 

[Maclain]

Ok, i've searched and found something very similar to what I want.

RACData

I think this is what you were after...

Code:
=Right(Date(),2) & Right(NZ(DMax("[EventNo]","[tbl_EmgCalls]","Left([EventNo],2)='" & Right(Date(),2) & "'"),"9900000")+1,5)
-What this does is takes the last two digits of the current year (Right(Date(),2) ) and appends the last 5 digits of the largest number found for the year, plus one (Dmax(...)). For the first entry of the new year, there will not be a "Max" value, so the NZ function provides a value for the null - "9900000".
-Hope this makes sense...

Place this code as the EventNo Default property, so that it only calculates a value for new records.

I would also change the format of the field to @@-@@@@@ and set Enabled to False (to stop users trying to change it!)

Hope this helps
Regards
Rod
(ex. Bulawayo)

Now, of the code:

=Right(Date(),2) & Right(NZ(DMax("[EventNo]","[tbl_EmgCalls]","Left([EventNo],2)='" & Right(Date(),2) & "'"),"9900000")+1,5)

so, the table involved is tblJobregister and the field is Job No set out as last 2 digits of the year and a sequential 5 digit number xx/xxxxx

i would alter to:

=Right(Date(),2) & "/" & right(NZ(DMax("[Job No]","[tlbJobRegister]","Left([Job No],2)="' & Right(Date(),2) & "'"))+1,5)

I'm planning on disabling this field in the form and making this the default value as the field in the table is required.


my only issue in the example is I don't know what the 9900000 refers to in the code...

edit:

have entered the code in the form and it doesn't work..... help!

 

[Maclain]

bump? sorry if this is not allowed

 

[pbaldy]

The 9900000 is the value being supplied as the default if the DMax returns Null (typically a zero is used there). Can you post a sample db?

 

[Maclain]

i'm struggling to get it to a size that can be posted...

ok, this might help...

I've set up a qry with the following sql

SELECT Max(Val(Right([job no],5))) AS [Max] FROM [Job Register and Report Log];

I called it qryMaxJobNo

Then set the Default value of the JobNo Form to

=Right(Date(),2) & "/" & DLookUp("MAX","qryMaxJobno")+1


this works fine for updating the last 5 digits, but i can't figure out how to get it to reset when a new year comes around.


Can I email you the database?

 

[pbaldy]

Sure, but I'm about to leave for a little bit:

pbaldy72
yahoo
com

 

[Maclain]

email sent, i;ve gone home for the evening but will check in later on from home

Thanks for the help on this one, looks like I really need some more experience with MS Access.

 

[pbaldy]

Gone home?!? It's only 10am!

Personally I would not store the year portion of the job number, presuming it can be derived from the date rec field. In that case I'd use this:

Nz(DMax("[job no]", "[Job Register and Report Log]", "Year([Date Rec]) = " & Year(Date)), 0) + 1

And I would also just store it as a number, so the first one was simply 1 rather than 00001. Then you'd present it to the user like:

Format([Date rec], "yy") & "/" & Format(JobNo, "00000")

I would also drop all the spaces in your object names. They are not worth the bother in the long run.

 

[Maclain]

Home time is 5pm Gotta love the time difference.

As you have probably notice my access knowledge is somewhat limited.

I don't really want to start messing about with things I dont really need to.

The reason I'm doing what i'm doing is thatthe Job No field used to be the primary key, and all the relationships were built upon that.

as it is now, the entry was manually entered by the user on every new record. At the time the field was fixed length to 7 characters only having 4 digits after the /.

Some numb nutz missed out a series of 3000 records and by the time anyone noticed we were getting close to number 9999 and the end of possible unique entries for this year.

So, I had to update the Job No field to allow for 5 digits after the / which ment changing all the relationships.

That done, I want to elimate the risk of us maxing out the numbers again, and this would be done by taking that responsibilty from the user.

Also the number formatting is used in a number of places and some of them drag the data directly from that table. So I dont really want to change the way that field is set out.

I understand what your getting at, and someday I may sit down and redesign and rebuild the database, however at the moment I need whatis basically a quick fix.

I hate being a pain ;-)

 

[georgedwilkinson]

Maclain,

You're not being a pain...we're all here to help. It's what we like to do.

And Paul is one of the very best (in the world).

 

[Maclain]

Maclain,

You're not being a pain...we're all here to help. It's what we like to do.

And Paul is one of the very best (in the world).

Noo dont get me wrong!

it was intended more as a sarcastic funny remark.

The responses are sharp and the information given is always on a very helpful and well explained way.

I know where to come for my access issues from now on!

I appreciate all the help you give

 

[pbaldy]

And Paul is one of the very best (in the world).

I don't have a pair of boots big enough to slog through the layer of you-know-what that you're laying down, my friend.

But I appreciate the thought.

Maclain, I'll take another look at the db after I get logged out of a PC in a remote city.

 

[Maclain]

Thanks alot:

it's 11pm now, heroes has just finished so i'm off to bed.

I'll check back in tomorrow around lunch, when your just getting up

Thanks again!

 

[Maclain]

don't suppose you had chance to take a look did you?

I have staff back in on Tuesday and I'd like to get this one in place.

Ta!

 

[pbaldy]

Sorry, got sidetracked. Does this work?

Right(Year(Date), 2) & "/" & Format(Nz(DMax("Right([job no], 5)", "[Job Register and Report Log]", "Year([Date Rec]) = " & Year(Date)), 0) + 1, "00000")

It assumes I can count on that Date Rec field.

 

[Maclain]

No worries

I've copy / pasted that code in to the default value of Job No field in the form and i get #error in the Job No field...

The Date Rec field is set to default value date() so it will always contain the date the job was booked in.

 

[pbaldy]

I tested in VBA and it worked fine. As the default value on a form control it would need an equals sign at the beginning. Try with that.