Hendra,
1. in my databases, tbl_db_Parameters, only contains a single record, but
contains fields where I want to store information that I want to be able to
change, but which applies to all of the users. An example of that may be the
email address of the help desk. I also generally have a Yes/No field in that
table called Maintenance, that allows me to force users off, and prevent them
from logging on when I want to perform database maintenance.
In your case, I would give the field a name that is similar to the one where
you will actually be storing the data. So, if you are going to call it
Emp_ID, then I'd call it Emp_ID_Auto in tbl_db_parameters.
2. The code I put in my first message would go in a code module, so that it
could be called from any form in your application. Then in the Current event
of the form, I would have some code that looks like:
Private Sub Form_Current
if me.NewRecord then
me.Emp_ID = fnNextValue("Emp_ID_Auto")
endif
End Sub
What this would do, is check to see whether you were on a new record, and if
so, it would set the [Emp_ID] field to the value stored in [Emp_ID_Auto] it
tbl_db_Parameters. It would also increment that value, so that the next user
to open up the form, doesn't get the same value (as mentioned by Rick in a
later post).
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
terranauro via AccessMonster.com said:
Man, I can believe this.
Everytime I post a question to this forum, than you guys will answer in less
than
1 hour. FANTASTIC SITE !!!!
Dale said:
If you are working in a single user environment, then it is easy, and looks
something like:
NextID = NZ(DMAX("FieldName", "TableName"), 1999) + 1
If you are working in a multi-user environment, it becomes a little more
complicated, because this value will not get written to the table until you
actually save the current record. So, if you and another person both open
the same form, and the 1st person has not saved his record before the 2nd
person creates his, then the above code would generate the same number for
each of you (this is not good).
In a multi-user environment, I usually create a table (tbl_db_Parameters)
that sits in the backend, and contains data that applies for all users of the
database.
What is the data that should be contained in the tbl_db_Parameters ? Could
you give some example ?
When I need the functionality that you mention, I will usually add
a field to that table for each of the tables that I want a self-generated
number field.
What field you add to the table that I want have a self generated number
field ?
Then, to prevent the same problem mentioned above, I have a
little function that gets the value from that field, and increments it.
Public Function fnNextValue(Fieldname as string, _
Optional Increment as integer = 1)
as long
Dim strSQL as string
Dim rs as dao.recordset
strSQL = "SELECT [" & FieldName & "] FROM tbl_db_Parameters"
set rs = currentdb.recordset strsql
fnNextValue = 0
if not rs.eof then
fnNextValue = rs(FieldName)
rs.edit
rs(FieldName) = rs(FieldName) + Increment
rs.update
endif
rs.close
set rs = nothing
End Function
Where I should place the code ?
Thank you very much for your help sir. Sorry for your incovenience answering
my question.
Hendra Himawan
[quoted text clipped - 16 lines]
