Need help with AutoNumbering a field (NOT with Autonumber)

[BigG]

Okay here is the situation as best as I can describe it so far:
I need a database for recording incoming raw material (steel).
What this database will need to do is allow me to enter and keep trac
of things like: (per item)

Supplier
Date
Part#
Description
PO#
Quantity
Inspected By
Comments
....and most important (this will be the first field, I made it m
Primary Key) is a field called LTN. This is a traceability number tha
is assigned to everything that comes in the door. Now, this number ha
to be:

1. Unique for each item entry and sequential (starting at around 170
to pick up where our current numbers leave off) and has to be bumped u
one per entry
2. Automatically generated BY the database when I start entering a ne
item.
3. I must be able to retrieve information by this field at a later dat
if need be.

I also need to print each individual item entry in the database when
enter it (a hard copy of the "Material Recieved Report" for that ite
is place in it's file)


*What I have figured out so far*

I made a database with all my fields that I need including all of th
above and I made LTN my Primary Key field.

I formated a report that suits my needs (sort of). The main proble
with the report is I can get it to show only one item per page by usin
the Force New Page from the group header properties, but when I go t
print, it will print out one per sheet but also it prints ALL record
in the database, so I end up with a ton of sheets with one item pe
page this will be a TON of sheets.
And I don't want to have to adjust the print options to "current page
every time if I can avoid it, because forgetting to do this could ja
us up.

One other thing is now that I made my LTN field the Primary Key, when
go to open my report it asks me to enter a parameter value everytime.
am assuming it is asking this because I have not set a AutoNumbe
option to this field (which I am to understand I should NOT if I wan
to this field to mean anything.)


SO I need to know how to set that number up correctly and also if I ca
do anything about the printing situation.

I believe access can do this, I am hoping anyway for sh!ts sake I a
doing the exact same thing right now on a program that was written i
DOS on a cheapo program like back in 1960 or something (called Q&A) o
a PC that is about to fall over and die, and it is working perfectly
The only problem is el-cheapo program is out of business and we hav
all new PC's that have to work together and this program is so outdate
it can't be installed. Plus we were told we need to try and ge
everything on office based programs.

So thanks in advance for any help.
I can send anyone who wants to help all my files to look at ro whateve
you need.

BTW, I just learned everything I just did since yesterday, I am ver
new at this but grasping it quick, and I no ZERO about VB or coding o
anything like this so I will have to be walked through if that is th
case.

Thanks again

G

 

[dandgard]

I don't understand why you say you don't want to use the autonumbe
scheme present in Access since this is just what you described.

However you could create a new record everytime that adds one to th
last record number by....

In the default value property of the control on the form tha
corresponds to LTN enter

=DMax("LTN","tableName") + 1 Where tableName is the name of the tabl
you are using to keep track of your material.

This should give you the value you want and not update unless yo
actually save the record.

Your problem with the report is that you are not filtering th
recordsource for the report to an individual record

 

[BigG]

Well from reading about 1000 post about AutoNumber everyone says it'
pretty much worthless to use if you need to number to represen
something.

"In the default value property of the control on the form tha
corresponds to LTN enter

=DMax("LTN","tableName") + 1 Where tableName is the name of the tabl
you are using to keep track of your material."


I have not generated a form for this database, only a report. Will
need to create one for this or can I do it through the report only?


G

 

[dandgard]

The number as you described it is meaningless anyway, the number yo
give it only has meaning because you have designed it that way.
Normally a primary key would ideally be a piece of data that uniquel
describes the transaction you are performing. Sometimes that i
difficult to do. (In the case of a your transaction the keyfields migh
be the company you are buying from and the date - or possibly thei
invoice number - which was probably created using an autonumber schem
-)

Anyway, getting back to your DB. How are you getting the new data int
the database if you don't have a form. A report only generates dat
for records that are contained within the database, it will not allo
you to create new records. If you are importing the records fro
another source then the other source is where you want to deal with th
numbering, or when you are importing

 

[BigG]

I will get back with you on that tomorrow with more info, thanks a lot!

 

[BigG]

Okay I have it pretty much completed.
I am using AutoNumber to number that field, I even figured out how t
make it start at the number I needed (let me tell you that was a b!tc
to complete from only reading about it in the help section of access!
But anyway that's done.
I just want to be sure AGAIN before I launch this thing that IT IS OKA
TO USE AUTONUMBER TO ASSIGN MY ENTRIES A UNIQUE (HOPFULLY INCREASING
NUMBER?

I will be the only one using this database, so there is no risk o
multi users or anything. But what I cant have is repeating numbers.


I guess that just leaves me with one more question (which I will as
here and post in a different post since it is a different topic). An
that is can I get my report pages to print only ONE at a time. I ca
get them to pull up and view one at a time just fine but when I go t
print ONE out, it wants to print them ALL out. Is there an easier wa
to do this rather than having to remember to switch my print setting
to "current page" every time I print?


Thanks again

 

[dandgard]

I Guarantee you will never get a duplicate number using autonumber. A
far as your report. Are you sure you are talking about a report an
not a form. A form when printed will print all records of th
underlying table that is populating the form.

If you have a report you have to have a recordsource for the report
You also will have a filter property for the report. You can selec
individual records or groups of records by using this property.

You can select invidivual records by changing the Recordsource an
using SQL statements with a Where clause to filter your records.

You can start your report using a Docmd.OpenReport statement in a VB
class module that will allow you to filter what data is displayed an
printed.

There are many ways to do this.

How do you initiate printing the report

 

[BigG]

Right now I am (from your suggestion) using a Form to input m
information (items.

Okay lets say I get 1 bar of steel in from a supplier.
I go to access and open my database. Then I click on Forms on the lef
hand side and bring up my Form where I input these fields:

Customer
Part#
Description
Date
Quantity
Inspected By
Suppplier
LTN (This is the Auto-Numbered Field)

Then I close the form out and go into my Report.
I pull up last generated entry (the one I just entered from the form
and hit Print. Then I get the print screen which always defaults t
print ALL under page range.

See, how I made each item appear on it's own page was to use th
properties under the group header and activated the ForceNewPag
(before selection). That made only one record show up at a time, BU
the report is still considering ALL the entries to be in the report
think. So when I click print, it wants to print every page.


I hope this clarifies what I am doing. I may be going at this 100
wrong, thats why i stepped it out for you to see.

Thanks again for the help

 

[dandgard]

Try opening your report from your form or setting up a new form to prin
with.

Create a button (lets call it btnLTN) on your form that says "Prin
Current LTN"

On that buttons OnClick event (in the properties sheet) create an even
procedure

Private Sub btnLTN_Click()
Docmd.OpenReport "ReportName",Print/Preview,,"LTN = "
LTNControlName
End Sub

Print/Preview means that you can select either to print the report o
preview the report here. Once you have this piece of code, put you
cursor on the word openreport and hit F1 this should explain th
different parameters that this method uses

 

[John Vinson]

I just want to be sure AGAIN before I launch this thing that IT IS OKAY
TO USE AUTONUMBER TO ASSIGN MY ENTRIES A UNIQUE (HOPFULLY INCREASING)
NUMBER?

I will be the only one using this database, so there is no risk of
multi users or anything. But what I cant have is repeating numbers.

Unless you have an unpatched copy of one of the versions of Access
which had the "repeating autonumber" bug, your only problem will be
gaps. Any record you delete will leave a gap in the autonumber
sequence; any record you *start* to enter, and then hit <Esc> to
cancel the entry, will leave a gap; any Append query is likely to
leave a gap, possibly a large gap. If you don't mine your numbers
running 1, 2, 3, 4, 6, 7, 43, 44, 45... you should be ok.

If you want to maintain your own number sequence, it's really not too
difficult. It does require that you use a Form to do your data entry,
but it takes only one line of vba code. If you're interested post
back.

I guess that just leaves me with one more question (which I will ask
here and post in a different post since it is a different topic). And
that is can I get my report pages to print only ONE at a time. I can
get them to pull up and view one at a time just fine but when I go to
print ONE out, it wants to print them ALL out. Is there an easier way
to do this rather than having to remember to switch my print settings
to "current page" every time I print?

Base the Report ON A QUERY which selects the record you print, or
launch the report from a command button on a Form to print the record
displayed on that form. Could you describe the context? How do you
launch the report, and how do you know which record should be printed?
If you have a command button to launch the report, could you post the
code from its Event Procedure?

 

[BigG]

Okay, after reading the last two entries and testing this out I see no
that I have a few more problems to deal with. The first is, how do
pull up (or even FIND a specific record). I thought if I kept th
autonumber there to coincide with the amount of PAGES in the report
could just match the two up and find what I am looking for (if tha
makes any sence).
Now I see that this will not happen, so I need to be able to pull up
specific entry at any time based on searching from ANY of the liste
catagories on the form (PO, LTN, Date, Supplier, ect..)and be able t
print (only) that specific entry.


As for creating a button, I have no idea how to do this yet (But I wil
be looking into it.

I told you I was new

Thanks again

 

[BigG]

Man I am really getting somewhere now!
I figured out the command button stuff and that is making lif
wonderfull!
I can't express enough how gratefull I am for all of the help!

Here is where I am at now.
I figured out how I can search by any field (using the "find" whil
clicked into a field on the form).

And I have my button printing Only one form at a time by using a Macr
but the thing is I made the command button to print the report, bu
when I assigned the macro it now prints the Form.
No biggie I guess, I just like the look of my report better. I guess
will just have to do some design changes on my form unless anyone know
what I did wrong.

 

[John Vinson]

On Mon, 24 May 2004 09:31:40 -0500, BigG

One clarification: YOU ARE NOT PRINTING A FORM. You're printing *data*
stored in a table! The data isn't in the form; the form is just a tool
to put it into the table.

Here is where I am at now.
I figured out how I can search by any field (using the "find" while
clicked into a field on the form).

And I have my button printing Only one form at a time by using a Macro
but the thing is I made the command button to print the report, but
when I assigned the macro it now prints the Form.
No biggie I guess, I just like the look of my report better. I guess I
will just have to do some design changes on my form unless anyone knows
what I did wrong.

Without knowing what's in the macro I can't say, but it sounds like
you used the macro wizard to print the Form rather than launching the
Report. There's a different wizard, one which will have the command
button launch a Report. It creates VBA code, not a macro. Give that a
try (I'd say delete the Macro, any code associated with this command
button, delete the command button itself, and rebuild the button). If
this doesn't give you what you want post back, posting the VBA code in
the button's [Event Procedure] - it's not hard to twiddle it to print
just what you want.

 

[å¿ÕýÔÆ]

Okay, after reading the last two entries and testing this out I see now
that I have a few more problems to deal with. The first is, how do I
pull up (or even FIND a specific record). I thought if I kept the
autonumber there to coincide with the amount of PAGES in the report I
could just match the two up and find what I am looking for (if that
makes any sence).
Now I see that this will not happen, so I need to be able to pull up a
specific entry at any time based on searching from ANY of the listed
catagories on the form (PO, LTN, Date, Supplier, ect..)and be able to
print (only) that specific entry.


As for creating a button, I have no idea how to do this yet (But I will
be looking into it.

I told you I was new

Thanks again

G