Question about Autonum

[djmurphy58]

I am setting up a databse to track RFIs (Requests For Information). The first RFI I will type in shall be given the number 0001. Subsequent RFIs will be 0002, 0003, 0004, etc. I know that Autonum will do this, but here's my issue: (Keep in mind I'm a beginner Access user).

During the course of setting up my DB I have created several "trial" RFIs to make sure my DB is working properly. In fact I have created 57 "trial" ones so far. Therefore, when I go to add the next RFI, Access will assign it #58. But I don't want that to happen when I actually start using this DB for real. When I put my first real RFI in, I want it to automatically be given 0001. Is there a way I can do this.....I guess I'm looking to "reset" the Autonum feature so that it starts at 0001 again.

Also, along the same lines....I sometimes need to be able to assign an RFI a number such as 0001A, 0002B, etc. For example, if RFI #0007 comes back with a response that is not sufficient, I need to create a follow-up question and assign it #0007A. That way I know that #0007A is a follow-up to #0007. Otherwise, if I am not able to do this, a follow-up RFI to #0007 may for example be #0048 because that might be the next available number. That's not very logical.

Does anyone know how to do this.......be able to override the autonum feature and type in a number like #0007A???

Thanks in advance,
Dennis

 

[jbehrne]

Hi Dennis,

When you do finally deploy your database and you want it to start at 0001 then here is what you need to do:

Delete all the info in your table and then go to
"Tools" - "Database Utilities" - "Compact and Repair Database..."

Once this is done your db will restart and your autonumber will be back to 0001. As far as assigning it the letter value at the end... Try using the input mask value I believe that the character '?' is optional and it allows letter entries between A - Z. However, I don't know if it will work as you requested with an autonumber, but experimentation never hurt anyone! I have used the character input mask with an autonumber, but the characters were always displayed and the same... (I.E. autonumbers 1, 2, 3 ... would be displayed PWO - 1, PWO - 2, PWO - 3 ...)
Good luck,

jbehrne

 

[930driver]

You should not be using Autonumber for anything other than a primary key to be used in relationships with foreign keys in other tables. It should never be seen by your end users. You cannot have an autonumber that incorporates a letter. There is a way of inserting a specific autonumber using a query, but then you will skip all the intervening numbers.

What you probably need is a text field for your RFI number that you populate with VBA code. Something like:

= DMax("FieldName","TableName&quot + 1

You could use this in the default value property of the control on your form.

This could then be over-ridden if you need to add the 'A'.

That all being said, if you delete all the records, then repair/compact your autonumber will be reset to zero.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[djmurphy58]

930Driver
I forgot to say that I DO want my RFI # visible to the user at all times. This is how it is identified. For example, it's much easier to tell an Architect that I am anxiously waiting a response to RFI #315 instead of saying I need the response to the RFI about the "window not fitting in the opening". I work for a general contractor.

Also, I tried writing an expression as you suggested, but I didn't get it to work. Of course I probably typed it in wrong....or in the wrong place. When I go to RFIID's properties, do I build the expression in "Control Source"?

My field name is "RFIID", which resides in "tblRFI". Currently I have RFIID set for autnumber, but based on what you said, I know that needs to be changed.

 

[930driver]

Of course you want your ID to show up. But you should not be using an autonumber for this purpose.

Leave the autonumber in place in the table and let it do it's own thing. Use it in relationships to other tables. Do not put it on any forms. You are using forms for data entry, right? The code snippet I gave you will not work in the default value property of a table, and your users should not be allowed to get at your tables in any case.

Then create a new field for your 'real' RFINumber. Now, in the form, you can cause it to get whatever value you need, and not be limited by an autonumber. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[djmurphy58]

930Driver:
You're the MAN!!!, or the WOMAN if you're female.

I got it to work!!! Awesome!!!

Thanks a bunch

Dennis

 

[djmurphy58]

930Driver-
I guess I was wrong. I thought it was working, but not quite. When I enter the first RFI into my form, I give it the number 0001. No problem there. I enter all the other info and hit the arrow key to add a second RFI. As expected, Access automatically assigns it number 2 (it added 1 to the previous RFI). No problem there.

The problem comes in when I try to override the number and try to type in 0001A. It all works fine until I try to add another RFI after the 0001A. Access tries to assign it a number but comes up with "#Error" instead. I guess that makes sense because it's trying to add 1 to the previous number, which is 0001A. So do you know how I take care of this? After I add an RFI with an "A", for example 0007A, how do I get Access to recognize that the next number should be 0008????

Thanks

 

[930driver]

Hmm, yes I see. Once you've introduced a character as opposed to a number it can no longer increment.

You will have to write a function to parse the characters out of the string and then increment it. You may have to store the last used 'number' in a table or database property rather than using the 'DMax + 1' construct.

I'm afraid that's going to go a little beyond this thread. If you need help with that you might do well to post in 'Microsoft Access Modules'.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[djmurphy58]

Ok, no problem. Thanks for your help anyway 930. I appreciate it.

 

[JeremyNYC]

Here's the deal: autonumbers, as others have pointed out here, are not going to work for you. Also, storing this information in one field is not really appropriate, as there are two bits of information, and two bits of information should _always_ be stored in two different fields. That would mean having an autonumber ID field as the primary key, a generated numeric as the ID that the users see, and a Suffix field, or something like that, to hold whatever letter (if any) needs to be appended to that generated numeric field. Also, I do not trust a simple dmax for this, as it's quite possible that two users will hit the Make Me a New Record button at the same time, and then you're in trouble. MichaelRed is one of the super smart guys around here, and he's got code posted in a FAQ for generating sequential ID numbers. I've never used it, so I can't vouch for it, but I see no reason it would be anything but excellent.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[CTAdmin]

djmurphy58,

OK. I am going to get a little analytical here. I think JeremyNYC is correct. These are two pieces of information.

I also agree with 930driver regarding the autonumber. Leave it to do its own thing. The RFI should not be an autonumber.

Here's how I would approach the situation. (You have an autoinrementing RFI field and an autonumber field used for queries, etc.)

1. Add an RFI2 (or RFI_Secondary) field to the table--you could make this a text field or a numeric value.

2. Add an input control on your input form (Personally, I would use an option group populated with "New"; "Followup 1"; "Followup 2", etc.) Set the values (text/numeric--whichever you decided) accordingly. "New" = 1; "Followup 1" = 2; etc. (If you are new to option groups, use the wizard. It will walk you through each step.)

3. Redesign your output (I am assuming a report). You need to edit the Control Source. =[RFI] & "." & [RFI2] produces "1.1" or "1.A" (depending upon whether you selected text/numeric). You may also replace the "." with "-" to produce "1-1" or "1-A".

The result of these modifications...

A user would enter a "New" request and it would be assigned "1.1" (RFI = 1 and RFI2 = 1). A followup is entered and it is assigned "1.2" (RFI = 1 and RFI2 = 2).

The architect receives a "New" request. The RFI is suffixed with ".1" or "-1" and he/she understands that this is the first request. A followup is suffixed with ".2" or "-2" and he/she understands this is the second request. Etc, etc.

I realize that this is a pretty different solution than you had wanted. However, I think this would be the most logical and would provide a much easier fix for you, as the programmer.

 

[djmurphy58]

CTAdmin,
Thanks alot for the input. I understand what your solution is and I like it. I tried to do what you said, but on my report print-out, I got "#Error" as the RFI number instead of 006-1, for example.

Here is the control source I used:

=[RealRFIID] & "-" & [RealRFIFollowup]

Is there something wrong with what I did?

 

[930driver]

You can't reference the underlying recordsource in an Access report. From the field list drop the fields onto your report and set their visible property to false. Generally, when you drop a bound text control onto a report it will have the same name as the field, so then your control source should work as you have it. But if not, just make sure you are referencing the names of the controls on the report. Make sense? "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[djmurphy58]

930Driver
Almost there......after setting RealRFIID and RealRFIFollowup to "invisible" on my report, I added the following expression back to my control source:

=[RealRFIID] & "-" & [RealRFIFollowup]

But now, even though I have multiple sample RFI's in my RFI table, only RFI "0001- " is showing up multiple time on my report.

 

[930driver]

The first thing that comes to mind is to make sure the controls got dropped into the detail section of the report and not a header "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[djmurphy58]

Yes, they are in the detail section, not the header.

I actually found an easier way to make it work.....probably not the most technically sound, but it works.

In my report, I just put my RealRFIID field and my RealRFIFollowup field real close together but with a "-" in between them. That gives it the look of one number in the form "XXXX-X", but it's really 3 separate fields just "smushed" close togther. Good enough for me.

Thanks for your help though. It's much appreciated.