I have an entry form that uses a auto number field to assign job numbers. There is a command button on the form to open another form. The forms are linked via the auto number field from the first form. I cannot get the auto number value from the first form to automatically be copied to the second form. Any thoughts on how to do this? I have tried a 'setvalue' marco, without any luck.

Just create a new field in the linked table and make the autonumber field on the other table its look up. You may have to modify the query if you are using the look up wizard and the autonumber field is not the PK of the other table. But anyway, if they are linked, it will automatically populate the linked table when a new record is created.

mmcdonal

To answer the question, you can put something like this on the second form when a new record is added (I'm using fake names):

Me.txtRecNo = Forms.OtherForm.Form.txtRecNo

But to caution you, you said, "I have an entry form that uses a auto number field to assign job numbers." You should NOT be doing that. Autonumbers have no meaning whatsoever except to relate data from one table to another as you're doing in your forms. If you want to crate a meaningful Job Number that people will actually see (NEVER show autonumbered fields to the users), you should put that its own field.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

You can use autonumber fields to create job numbers as you are doing, just don't make the job numbers the clustered index or Primary Key of the table. That can (and should) also be an autonumber field, but never shown to the user.

mmcdonal

mmcdonal & SerranoG

Thank you both for the information. I have found a solution to my question, since the post.

I do have another question. Both of you mentioned not using the primary key and/or an autonumber as the job number. Why? What are the pitfalls? This is my first dataBase to design. I have not implemented it yet. I plan to do so within the next couple of weeks. If there is something that I am missing I would like to know it now.

I look forward to your replies.

Autonumbered fields are used to uniquely identify a record in a table. Each table will have this field as a primary key. Subordinate tables will have a foreign key set to a long integer to match the value of the main table's autonumber. The foreign key will usually get assigned automatically via a parent/child relationship in a form & subform.

The sole purpose of these numbers is to keep track of related records throughout the database. These numbers are never seen by any user. They are used in the background by the computer in queries, SQL statements, and VBA code. Their actual values are meaningless and it doesn't matter if they're sequential or not, just as long as they're unique.

If you want to use meaningful ID numbers for something that users will see and use, you create your own field (not an autonumber) that has a format that makes sense to you. These fields usually DO matter if they're sequential or make some kind of sense to the user. These are seen and refered to by users in forms and reports.

The pitfalls of using autonumbers to actually have meaning is that these numbers usually have to be sequential to users. If you delete any records, Access does NOT renumber anything. You will have gaps in your numbering. If someone starts a record and then deletes it and then puts it back, the autonumber will not come back and be repeated; Access will choose the next autonumber. To Access, the number is not intended for "meaningful" use so it just assigns numbers... period.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

SerranoG

Thanks for the input. Let me explain what I'm doing in more detail. We perform annual calibrations on equipment we sell. When we receive an item for calibration, we assign it a 'Job #'. This number is unique & sequential. It follows the item while it is in-house, until it ships. In fact it becomes our calibration number that is listed on the calibration certificate. The system currently in place, for assigning 'Job #' & tracking the item in-house, is a manual, paper system. We use a log book to assign a unique & sequential 'Job #' when the item is received. I am moving our paper system to a database system, having a unique 'Job #' is critical. Using the autonumber appears to be the most simple way to handle this.

Is there another reason why I should not use the primary key from my receive table as the 'Job#'?

Thanks again for any help.

Yes, you said that job no. has to be sequential. If you delete a job or accidentally delete it and put it back manually, you WILL lose your sequential number. You will NOT be able to set the next number as one plus the previous.

The only way to renumber the autonumber field is to compact and repair the database, open a new copy of your table and data dump all your data into a new table and erase the old one. That would be a BAD idea because then you will lose your previous "job number" history and many job numbers may end up with NEW job numbers. It would be a big mess.

Leave the autonumber field to relate table records and never let any user see it. If you want to assign job numbers that people will see and use, create a new field that will hold that number. Then you increment the job number yourself, manually. Having that control will make sure you always have sequential numbers no matter who deletes what.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thank you for the advice. Is there another way to assign job #, without using an autonumber, that is automatic. It is more important that it is not manual than it being sequential. This number is assigned when the package is received. I do not want the people receiving to have to keep track of which number should be used.

Oh, sure. When I said "manual," I didn't mean that you couldn't program something that would increment automatically. I meant that it wasn't set automatically BY ACCESS like an autonumber.

You can create an incrementing job number very easily that will always set itself to one more than the maximum existing. I've even programmed ones that reset back to 1, say, at the beginning of the year.

There are discussions in various places about doing just this. I'm going to look them up and then post links for them for you to read in my next message.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division