The correct name of the Sub must be this:
Private Sub Form_Current
Form must be in the name; you do not use the actual name of the form.
Change the sub's name, then you will need to "connect" the procedure to
the form's Current event property. Go to design view of the form, open
the Properties window, click on Event tab, go to textbox next to On
Current property, select [Event Procedure] from the dropdown box there,
then click on the three-dot button at far right of textbox. That will
make the connection.
A tab control is "invisible" with respect to referencing controls on it
in code. The controls on a tab page are "seen" by VBA just the same as
if they were on the form itself.
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
I might have an idea what the cause of it is.. (I did not think of this
before..)
the "Algemeen_boekingsformulier_Current" is my main form.
However, I do not enter data on the main form, but on tabpages, which
are
one the main form..
The database is almost 55 MB of size..
If this is too big, I can ectract the dataform for you..
Just let me know, and I will send it..
Arjan
The Netherlands
"Ken Snell (MVP)" <
[email protected]> schreef in bericht
Are you running the VBA code in the form's Current event procedure? I
don't recognize the "name" (Algemeen_boekingsformulier_Current) of
the Sub that you posted.
I am perplexed by why the code would work the first time and then
produce a zero after that. The code cannot generate a zero based on my
understanding of your setup and based on the fact it's using the year
as part of the string. There must be something else about your setup
that is still not correct.
It appears you're using a non-US version of ACCESS, so I don't think I
can look at a copy of your database to see more details?
--
Ken Snell
<MS ACCESS MVP>
Yes, I do
Almost every data is stored in one (small) table..
"Ken Snell (MVP)" <
[email protected]> schreef in
bericht Are you storing the generated reservation number into the table as
part of the data entry process on the form?
--
Ken Snell
<MS ACCESS MVP>
Dear Ken,
I hope I on't start to bother you..thanks for the help again..
however, when I inputted the code it did it once, and from then one
it produces a '0' for every record.
this is the code I use:
Private Sub Algemeen_boekingsformulier_Current()
'Hier wordt het reserveringsnummer bepaalt
If Me.NewRecord = True Then
Dim strYear As String, strReserNum As String
strYear = Year(Date)
strYear = Left(strYear, 1) & Right(strYear, 2)
strReserNum = strYear & Format(CLng(Nz( _
DMax("Reserveringsnummer", _
"boekingsformuliertabel", "Reserveringsnummer Like '" & _
strYear & "*'"), 0) + 1), "000")
Reserveringsnummer = strReserNum
End If
End Sub
do you have any idea?
Once again, thanks for help and effort!!
Arjan
the Netherlands
"Ken Snell (MVP)" <
[email protected]> schreef in
bericht Where you put the code depends upon when you want the number to be
generated.
If, for example, you want the number to be generated whenever a
new record is being created, you could use the form's Current
event and test for a new record:
Private Sub Form_Current()
If Me.NewRecord = True Then
' put that code here
End If
End Sub
If you want to generate the number whenever another value of some
type has been entered on the form, you could use the AfterUpdate
event of the control where that initial value is entered.
Or if you want to generate the number just before the form saves
its data to the table, you could use the form's BeforeUpdate
event.
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
thanks for you help... again.
what do you suggest? where should (can) I put the code? without
the problem where you are referring to?
Arjan
the Netherlands
"Ken Snell (MVP)" <
[email protected]> schreef in
bericht This code that I gave you assumes that the Reserveringsnummer is
either a long integer (number) field or a text field -- it'll
work with either one.
The code that you posted puts the desired "new" number into the
variable strReserNum. You then need to write that value
somewhere if you want to then display and/or use it. If you want
the new number to show in the Reserveringsnummer textbox, you
then would need one last code step:
Reserveringsnummer = strReserNum
However, I caution you about using the GotFocus event of the
Reserveringsnummer control to run this code. To receive the
focus, the Reserveringsnummer control needs to be enabled -- so
unless you've also locked the control, the user then is able to
change the "new" number to something else.
--
Ken Snell
<MS ACCESS MVP>
Hi Ken,
Thanks again,
However, I can't get it to work...
I have this field in my main table "boekingsformuliertabel" the
field is called "Reserveringsnummer" (does this have to be an
autonumber? as it has now the number propertie..)
The code you gave me, I have copied and pasted it in the form
on the Got Focus event.
The code I use is:
-----------------------------------------
Private Sub Reserveringsnummer_GotFocus()
'Hier wordt het reserveringsnummer bepaalt
Dim strYear As String, strReserNum As String
strYear = Year(Date)
strYear = Left(strYear, 1) & Right(strYear, 2)
strReserNum = strYear & Format(CLng(Nz( _
DMax("Reserveringsnummer", _
"boekingsformuliertabel", "Reserveringsnummer Like '" & _
strYear & "*'"), 0) + 1), "000")
End Sub
---------------------------------------------
While I run the code it simply doesn't give anything, It also
does not give an VBA error..
Do you have any idea??
note: I would like to use your recomendation. However, I'm not
sure how to set it up...
Thanks again..
Arjan
the Netherlands..
"Ken Snell (MVP)" <
[email protected]> schreef in
bericht Error in the code:
Dim strYear As String, strReserNum As String
strYear = Year(Date())
strYear = Left(strYear, 1) & Right(strYear, 2)
strReserNum = strYear & Format(CLng(Nz( _
DMax("ReservationNumberField", _
"TableName", "ReservationNumberField Like '" & _
strYear & "*'"), 0) + 1), "000")
--
Ken Snell
<MS ACCESS MVP>
message Something like this, perhaps (assuming that the year 2010
would be 210):
Dim strYear As String, strReserNum As String
strYear = Year(Date())
strYear = Left(strYear, 1) & Right(strYear, 2)
strReserNum = strYear & CStr(CLng(Nz( _
DMax("ReservationNumberField", _
"TableName", "ReservationNumberField Like '" & _
strYear & "*'"), 0) + 1))
Note that your setup will fail after 999 reservation numbers,
though.
However, I do not recommend that you store a single
reservation number the way you desire. Instead, I would use
two fields in the table -- one to hold the year (either as
yyyy or in your shortened version) for the reservation, and
the other to hold the sequential number. Then you can use
concatenation (calculated field in a query or an expression
as a textbox's Control Source, etc.) to display the combined
number. By separating the two "identity" values, you'll make
your queries for finding data within a given year or years
much easier to do because you won't need to extract the year
as part of the criterion (note what I had to do in the above
code example to filter to just the desired year).
--
Ken Snell
<MS ACCESS MVP>
Hi All,
I have a question.. I want to create a unique reservation
number, for
invoice payments etc..
However, I want the reservation number to be build from two
things;
first: the year we live in minus one 0 i.e. 2006 = 206, 2007
= 207, 2008 =
208 etc..
second: I want to add 3 digits to the 'year'number. These 3
digits will
start as 001 and every new reservation number will be added
with 1.
so eventualy the reservationnumbers will look at this:
206001
206002
206003
Further more, when a new year starts a new serie has too
begin..
207001
207002
207003
etc..
Is there anyone who can help me?
Thanks in advance..
Arjan
the Netherlands
