Re: Custom ID Field

There is definitely something wrong. I suggest pressing Ctrl+G to bring up a
code window, and in the Immediate window, paste:

? "TS-" & Format(Date(), "yy") & Format(GetNextJobNumber, "00000")

You should get the correct number. If not, check your references (Tools >>>
References from any code window) and recheck the code in GetNextJobNumber to
make sure that you accurately copied and pasted it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"DBenedict" <DBenedict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:53FECE8A-3C48-4EC5-A10F-A30BCABED07C@xxxxxxxxxxxxxxxx
Still no luck I'm afraid.

Yes, my previous post contained a typo. I missed the & after the ="TS-"
but
not in Access. But I copied and pasted like you said in the form's
JobNumber textbox Format field. And Access updates it every time I exit
the
field in design view. Tried with and without the = sign.

= "TS-" & Format(Date(), "yy") & Format(GetNextJobNumber, "00000") becomes
"= TS- "&" Format(Date(), yy) "&" Format(GetNextJobNumber, 00000)".

When I exit the format box, it auto corrects me EVERY time. I even tried
removing the leading text "TS-" in case there were too many quotations.

= Format(Date(), "yy") & Format(GetNextJobNumber, "00000") becomes
"= For"m"at("d"ate(), yy) & For"m"at(Get"n"extJob"n\um"ber, 00000)".

Why? Maybe its my version of Access or some setting I don't know of in
the
database options. I'm at a loss.

Yes, unfortunately the function updates the table tblNextNumber even
without
creating a new Record in the form/table. No typing, no nothing.

When you open the form you see all 1209 records in the table. When you
are
at the end looking at 1209th record and move the mouse wheel or even click
the next arrow to the blank record, the function runs. The entire form is
blank except for the Job Number which has advanced by 1.

The 1210th record appears in the Job Number field and (AutoNumber) appears
in the original Project Number field that I haven't deleted yet. I
haven't
typed a single keystroke or moved the cursor in or out of a field. Just
rolled the wheel to the end.

Even happens in Data*** View when you exit the last record and enter the
blank line.

When I close the Form, its Table is completely unaffecfted but the data in
table tblNextNumber has already advanced.

My Form is set to Allow Filters, Edits, Deletions, and Additions. Data
Entry = No, Dynaset, No Locks, Fetch Defaults = "Yes".

Because the Fetch Defaults is set to Yes, the function runs and a new
number
is wasted IF you don't continue to enter data.

Set the Fetch Defaults to "No" and the function doesn't run just by
entering
the blank record because the Default Values are not "Fetched". But then
the
GetNextJobNumber() function does nothing and the Job Number is left blank
when you actually start typing new data. Getting Default Values are not
turned on.

Not sure how to solve any of this. Yet. I think I'll start with a fresh
database and build up a small table, then a form, and go from there.
Maybe
it is something in the background from an older version? These could be
converted databases starting from Access '97, or 2000, then to 2002-2003.

Again, Thank You for your patience. I know this is possible and I will
keep trying!!!!!!!





"Arvin Meyer [MVP]" wrote:

"DBenedict" <DBenedict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:027CB40D-5988-4177-B090-CE2861D89E86@xxxxxxxxxxxxxxxx
Thank You for the assistance, again. Very much appreciated as I am
learning.

First major problem. Formatting...I can't get the format thing to work
right. The GetNextJobNumber() function works fine by the way. From
the
immediate window and from the control's DefaultValue on the form.

But I don't know how to format it in the function itself so its result
is
outputted correctly to my table. In the format of TS-0800002. 08
being
2008 and 00002 being the next number.

The Format property in the form and table just affects how data is
displayed. It doesn't store it how I need it. Access also adds its
own
"
" when I type this into the Format field (form or table).

="TS-" Format(Date, "yy") & Format(GetNextJobNumber, "00000") changes
to
"=TS- "&" Format(Date(), yy) "&" Format(GetNextJobNumber, 00000)".

Same results with the field's data type set to Text or Number.

The field's datatype MUST be text. The exact expression, copy and paste
it,
should be:

= "TS-" & Format(Date(), "yy") & Format(GetNextJobNumber, "00000")

Pay close attention where the quotes and ampersands are, because both of
your examples above were wrong.


Second minor glitch. The form displays the contents of the table to
let
the user apply standard filters and scroll through records. Since the
GetNext function is used in a field on the form, when the user gets to
the
end of the records, the function automatically runs. Even though they
have
not entered any data or created a new record, the tblNextNumber is
already
updated and that number is wasted. Tips?

The number should only be wasted with the first keystroke in the form.

I could change the Form's Recordset Type or the Record Locks or make
them
query a record but this design is what they are used too.

Couple other issues I ran across.
In the table, I had the JobNumber field's DefaultValue set to
=GetNextJobNumber() and would get errors. Unknown Function...and
things
about exceeding columns, locks and duplicates. So I couldn't save the
function at the table level.

Use the textbox's DefaultValue in the form, not the table. Users should
never be allowed to work in tables, especially in databases which cannot
prevent them from editing data in a table's field. In practice, I hide
the
database window from users, and if I see that any of them are unhiding
it, I
write code to prevent them from seeing it.

I thought I wanted it in the table so that only records "added" to the
table
would trigger the GetNextJobNumber function. This would avoid the
problem
stated above with the form. Access didn't think that was a good idea.
Why
not?

Access has no Triggers and cannot run user designed functions at the
table
level. If you import records, you'll need to run the function from a
query
and update the table. Users should never even see a table.

I can use two fields called Date and JobNumber and string them together
but
this is only displayed, not stored to the table or queryable. The
calculated
control displays it correctly as TS-071213. Not exactly what I'm
after.

Can the GetNextJobNumber function output in my format? I tried a few
things but nothing..

Can the GetNextJobNumber function only run when a Record is created in
the
table? Call it from the "Add Record" button?

Once again, forget using tables. If you need a data***, build one in a
form. Tables are for storing data only, not for adding, editing, or
deleting
data. That should be done through code, queries and forms.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com





.

Loading