Victoria,
Set the control's ControlSource to the UserID (text) field in your table, and make the
DefaultValue for UserID...
=Environ("username")
"Find a job that you love, and you'll never work a day in your life."
Victoria,
I'm glad that you're using the autonumber as the "real" key to relate your data, and
the new (ex. SalesID) for just for "your" use.
Two fields should do it... ex... SalesCount (Long), and UserID(Text)
The DefaultValue for SalesID would be...
= NZ(DMax("[SalesID]", "tblSales")) +1
The DefaultValue for UserID would be...
= [Currentuser]
SalesID would be a calculated unbound field...
= SalesID & UserID
Since you've saved the SalesID and the UserID, there is no need to save the
SalesID...
just re-concatenate those values and display it whenever need in any form. query, or
report.
Never save a value can be always be re-derived from other data that you have saved.
Ex. Qty * Price = LineTotal
You would not save Line Total... just recalc it when needed from Price and Qty.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your life."
Hello,
I am building an order entry database for sales representatives at my
work to be used at conventions to enter customer orders. Since each
database when finished will be installed on each of their computers I
would like to use username on the computer and d(max) function to
create a unique order number to be imprinted on the order report and
stored in the order table. Could somebody please help me. Currently I
have autonumber set as primary key in my orders table which is used to
create all the relationships and the whole order database is pretty
much complete except for this part.
Could someone please help me on this.
Thanks
Victoria- Hide quoted text -
- Show quoted text -
Hello,
Thanks for your reply. I was actually trying to get the Windows user
id and combined it with Dmax function to create a unique order
number. I found how to pull userid using =Environ("username") in the
control source of an unbound text box but I don't know how to store
that id in the table?
Any ideas?
Thanks,
Victoria- Hide quoted text -
Hi,
Thank you very much, that worked but I still have a problem with Dmax
function. After reading every newsgroup thread I could find and still
couldn't figure out why my dmax function isn't working. Please help.
I created and Order# field in my Oders table. I set field size to long
integer and index to yes(no duplicates). I set the default value to =
NZ(DMax("[Order#]", "orders")) +1. When I close the table i get the
following error :Unknown function <name> in validation expression or
default value on <name>. (Error 3388). I only do DMax, i stil get the
same error. I then set the default value to 0 and closed the table.
Then I went to my orders form. I created a text box with control
source set to Order# and defaulat value to = NZ(DMax("[Order#]",
"orders")) +1. When I add a new record, i get the following message
The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. (Error 3022). I cleared the default value field and
closed the box. It seems that having index in the order# in my orders
table be set to yes(no duplicates) creates this error. So I removed
it an set the index to no.
Now it seems to work. However, I thought that unlike the autonumber
which can have gaps if a record was deleted, the max would give me a
sequential number with no gaps. When I add a new order, it gives me
the next number, however, I have undo command button created using
wizard which clears any entries on the current form and deletes the
record. If the udo button is clicked, the order # field is not longer
in sequence. Any ideas as to how I can get sequential order number
even when the user deleted current reocord and decided to start over.
Looking forward to you response.
Victoria.- Hide quoted text -
- Show quoted text -
