report printing

[accessissofrustratin]

Hello, could someone help please. i need to have a unique number printed on a report that i have created from a form. i cannot insert an auto number as the form already has one (address id). in short, what i need to happen is every time i press print the report has the next number in the sequence.
Thanks very much

 

[vbaInet]

What about using a Date/Time stamp?

 

[accessissofrustratin]

Thanks for the reply but i already use that. i need to then create a unique job number. i.e 4110 then 4111 and so on
Thanks

 

[vbaInet]

How many users use your db?

 

[accessissofrustratin]

Only three users

 

[vbaInet]

You need a table that you will use for generating the unique IDs. An Autonumber field will do.

 

[accessissofrustratin]

thanks for the reply but i have tried that, but in my report i can only add stuff from the table it was built off.

 

[vbaInet]

You can use the DMax() function to get the most recent ID from your table.

Have a look on www.techonthenet.com for the DMax function.

 

[accessissofrustratin]

Thanks for that vba, but i don't really think the DMax is my answer, unless i am misunderstanding. i just need my report to have Job No. "1001" printed on it then the next job would be "1002" and so on. I may have a customer in the address book with ID 21 for example i don't want to use the id as a "JOB No" as he may be back in the future and the new job would require a new number.
Thanks for helping

 

[vbaInet]

The Job No has to come from a table right? In order for you to get the next available job number you need to first, insert a new record into the table that holds only auto generated numbers, then use the DMax() function to grab that number and use in your report.

 

[accessissofrustratin]

the dmax function does'nt seem to do what i need. i have a form built from a table, customer name, reg, chassis number etc. once they are on the system i would like to just to be able to print my report with a newly generated autonumber on it. when i created my table the address ID was the primary key and is was also set to autonumber so i cant add another autonumber field.
Thanks

 

[vbaInet]

You need an ENTIRELY new table for just generating the IDs. I didn't say you should add a new field to your original table.

 

[accessissofrustratin]

i have created a new table with an auto number but when i am in the form i can only add fields from the table i created the form from.

 

[vbaInet]

That's why I said you should use the DMax() function. Plus I gave you a link for reference.

 

[accessissofrustratin]

tried it. didnt work. give up now. thanks anyway

 

[vbaInet]

Let me see what you tried.

 

[accessissofrustratin]

autonumber..........aarrggghhh

Hi, i have an on going issue i need to resolve before we can use this database.
i have created a table with customer details, name, address, car reg, etc. within this table is a field called addressID which is the primary key and is an auto number. i then created a form off of this table. i have also created a report to be printed. The form has a print button which prints out the record you are on and only this record by using the addressID so as not to print out all the records. so if i now add a customer into the form it will have an automatically assigned number, from the addressid field, not a problem. But i would like my report to have a unique number (called job no.) printed on it. i cant use the addressid as customers come back and have other jobs done. i currently print out the job card and manually write the job number in and keep a running sheet of numbers until i can resolve this. i have tried creating a separate table with just one field (job no) and set this to auto number and inserting this into the report, but cant see this in the reports field list as this report is built from my original table. i dont think the dmax option will help me, i tried to use it but dont understand it. maybe i am asking access to do something it is not capable of or i have built it wrong from the start. so to sum up when i click print from my form print button i need the printed report to be the next in the sequence, it may be the same customer who was in last week, year, month it just needs to be a new job number and i dont care if it starts at 1 or 1000. thank you in advance.

 

[Anakardian]

Re: autonumber..........aarrggghhh

If you have a form where you create the jobs, you can use the DMax function to create an automatically increasing number.
You need toset up an extra fiedl to contain the job number.
In the forms BeforeInsert vent, enter the following:
MyField = Nz(DMax("MyFiled", "MyTable")) + 1

Replace MyField and MyTable with the correct names.
Make sure you are putting it on the vent for the for, therwise it won't work.
You can change the +1 to another value if you want to increease by another number.

 

[accessissofrustratin]

Re: autonumber..........aarrggghhh

thanks for the reply, i dont have a form to create the jobs. the form is just there to hold the details i mentioned earlier. so for example if you come in to me today i will open my form add "Anakardian" your car details and the print my report which produces a job sheet. you may come back six weeks later with another problem, so all i do is open your details and click print. i do not want to have any numbers the same, as a "job no" is unique. as for the above, do i need to add a field in my table, then add it to the form in a text box. i have tried this and can not find BeforeInsert Vent option.
Thanks

 

[accessissofrustratin]

Re: autonumber..........aarrggghhh

hello, i have done this JobNo = Nz(DMax("JobNo", "Addresses")) + 1 and its not what i need. in short i just need every time i print a job sheet it has a new number that has never and will never be used again. i only need this number to print on the report. having it any where else is irrelevant as we do not keep electronic copies of job sheets. it is just like a number stamped on the job card once it has printed.
Thanks