Form box autofilling with Date and number?

[Tezcatlipoca]

Hi all, and apologies for starting a fresh thread about this, but I can't find exactly what I'm trying to achieve via the search function, although I'm hoping it's just something simple I'm missing.

I have a small database that allows the user to click on a button to go an Add New Records form, which they then fill out. At the moment, the record number gets autofilled and they start entering details. However, one of the details they must enter is a unique record number, consisting of the month, the year, and a number between 01 and 99 (so the first new record created today, for example, would be record 090601, the second 090602, etc. The first next month would be 100601, and so on).
At the moment, I have to rely on people to manually enter this number, which requires them to view the current records first to ascertain whether there have been any previous records created that day (to prevent duplications).

What I'd really like is to have the this records box autofill when entering the form with the current month, current year, and then the number of the record for that day. However, it should automatically look at the last record created, and, if there's a match on the month and year, add one to the last 2 digits to ensure a new, unique record. So, for example, I have three records this month, listed as numbers 090601, 090602, and 090603 respectively. I go to create a new record, the box should autofill with the number 090604 and create a new record accordingly.

How do I acheive this?

 

[MStef]

Look at "DemoMYNautofillingA2000.mdb", I think it is what you need.

 

[MStef]

I'm sorry, it don't work for 10 month, I'll try to solve this.

 

[Tezcatlipoca]

Ah, I see what you mean. If I alter my system date to October, the new autofill still works, but still adds 1 to the number of the last record, rather than starting at 1 again for the new month.

Thank you very much for looking at this for me, incidentally, it is greatly appreciated!

 

[MStef]

Hello Tezcatlipoca!

Look at a new one, I think it is OK now.

 

[Tezcatlipoca]

Thank you, MStef! That is exactly what I need!

 

[MStef]

Hello Tezcatlipoca!

I have found your message.
Here it is a new one with 3 digit number.
Take a look and tell me if it is OK.

 

[MStef]

Oh, I forgot!
In ....& Format(Right(wf1ID,2)+1,"000")
put
......& Format(Right(wf1ID,3)+1,"000")

 

[Tezcatlipoca]

Oh, I forgot!
In ....& Format(Right(wf1ID,2)+1,"000")
put
......& Format(Right(wf1ID,3)+1,"000")

um...your end format doesn't match mine. I have:

......& Format(RightNz(DMax("[rmanumber]","maindata")+1,1),2),"000")

Obviously, 'rmanumber' is the field from my table, 'maindata', but I seem to have extra bits in mine. What am I changing? The last '2'?

 

[MStef]

If my mdb work with 100 or more records, then adapt your VBA like mine.

 

[Tezcatlipoca]

If my mdb work with 100 or more records, then adapt your VBA like mine.

Sorry, MStef, but I just get an #Error when using the vb you quoted. I can see why; it's because it just appears to reference the cell in which the autofill number should appear, and doesn't seem to make provision for checking the table for the last number.

Unfortunately, the 'Default Value' section of your posted database just seems to be filled out with '0000000', rather than the '=Format...' argument.

Can you please post the complete 'Default Value' line for me?

 

[MStef]

I don't understand what's the problem.
When I try, everything is OK.

 

[Tezcatlipoca]

The 'Default Value' of my box is currently:

=Format(DatePart("m",Date(),"00")) & Right(Format(DatePart("yyyy",Date(),"00")),2) & Format(Right(Nz(DMax("[rmanumber]","maindata")+1,1),2),"000")

This works, but chops the first 0 off the number (as I mentioned before). If I change the last part as per your instruction so the value reads:

=Format(DatePart("m",Date(),"00")) & Right(Format(DatePart("yyyy",Date(),"00")),2) & Format(Right(rmanumber,3)+1,"000")

I get an '#Error' message in the box. I'm assuming this is because I'm not telling the format to check the last value of the field 'rmanumber' in the table, 'maindata'?

 

[MStef]

It seems to me that you try to work with OLD table, where FORMAT is
"000000". You must to make a new table with format "0000000" (mmyy000).

 

[Tezcatlipoca]

My table format is 0000000. I have two forms, one for viewing and one for adding. the viewing form displays the 7 digit RMA numbers correctly; it's the adding form that is having problems since it autofills the box with the last RMA number + 1 (correct), but is only 6 digits long (incorrect) having chopped off the first 0. the only difference between the 'rmanumber' box on my two forms is that the viewing one is jsut a straight lookup to the number in the table, whilst the adding one has your code for autofilling after checking the table and adding 1 to the last number found.

It's almost as though Access thinks the first 0 isn't necessary (which it is), so I'm guessing a formatting problem somewhere, but don't know where. My column in the data table has the format 0000000, so does the format in my 'rmanumber' box on the adding form so the problem must be the 'Default Value' code I'm using. My problem is that I don't understand your code well enough to know what controls what, and if I use the code ending you've posted, I just get the box filling out with '#Error' every time! Please help! What do I change?

 

[MStef]

Did it work with 2 digits before ?????

 

[MStef]

Maybe the problem is in your adding form, look at Format in form for this field.

 

[Tezcatlipoca]

Ok, rather than us just bouncing posts back and forth, it may be easier for you to take a look at my actual code and see if you can tell where I'm going wrong.

Here is a copy of the complete database - minus all the big images and private data - with a couple of test records setup to show how it should work.

If you look at the viewing form (button 1 from switchboard), you'll see the rmanumber box I'm talking about. This displays whatever is in the relevant column from the table.

Now look at the adding box (second button from switchboard), this also has an rmanumber box, but it should populate with the last rma number + 1, and keep to 7 digits in length even if the first digit is a 0.

(Note that I've only included the database itself, so the button/function for generating forms from a template obviously won't work)

PS: If you alter my database and repost it, could you please also tell me what you changed. I'd rather know so I can increase my own knowledge of these things, as opposed to just having somebody else doing it for me...thanks!

 

[MStef]

Hello Tezcatlipoca!

Here it is, Look at 3. record

 

[Tezcatlipoca]

Ah, thanks MStef. Got it working perfectly now. Cheers for sticking with this; it's greatly appreciated.