Okay.
Open the form in Design view where you have the control you want the number
to be in
Click the Code icon on the Toolbar. (it looks like a box with little blue,
red, and yellow balls on it.
The VB Editor will open.
The Caption at the top of the screen should have the name of your form in it.
Go to the top of the form module.
The two panes at the top should say (General) on the left and (Declarations)
on the right. Just below that there should be some option statments Like:
Option Compare Database
Option Explicit
etc.
Doesn't matter what they say, just trying to get you oriented.
Copy the code I sent and paste it just below the last Option statement, but
before any other code you see.
You will need to replace my made up names with the correct names for your
own objects.
Save it.
Close the VB Editor
On your form, right click on the text box where you want the number to
display.
Select Properties from the dropdown menu.
Look for the property named Default Value
Put this in the box next to it:
=GetNextNumber()
Save it.
Test it,
let me know what happens.
Tom said:
Forgive me, I'm stupid. I've never touch this before.
I need my hand held. I have the formula ready to
paste. I just need to know exactly "step, by step"
what to click on where to go.
I had to reset my puter to yesterday to get rid
of my stupidity.
Do you want me in "forms" or "tables"
Do you want me to select "tools" then "macros"
I don't know.
Tom
:
I believe that means it can't find the function GetNextNumber. Either you
spelled it incorrectly in the function or where you call it, or the function
is not where the form can see it.
Check your spelling, and if that is not it, tell me where you put the
function?
:
Klatuu,
Now it comes up with
#Name?
In the field of "Form View"
No syntax or other errors
Tom
:
typo on my part, sorry. Should be:
GetNextNumber = Format(CLng(Left(varHighValue,6))+1,"000000000")
It doesn't need to be in a standard module unless you plan to use it else
where. If it is for this form only, put it in the General section of your
form module.
:
Rico,
It gave me a syntax error
and highlighted
GetNextNumber = =Format(CLng(Left(varHighValue,6))+1,"000000000")
Then I clicked "OK" and highlighted in Yellow was:
Function GetNextNumber as String
I also noticed that when I went to save it, next to "General"
it changed from "Declarations" to "GetNextNumber"
Tom
:
You need to open a new Module (in main database window), paste the code into
there. save the module (basGetNextNumber would be a good name)
THEN go to your table and type GetNextNumber() into the default vlaue of
your field.
HTH
Rico
:
I got the follow message trying to save the table,
in "design view" "The expression you entered contains invalid syntax. You
may have entered an operand without an operator."
I cut and pasted the formula
Tom
:
I think this question has set a record for the number of times asked. Were I
smarter, I would save the answer in a text file so I could cut and paste and
not have to write it again (3rd time this week):
Here is a function that will give you the next number.
Function GetNextNumber as String
Dim strCurrDate as String
Dim varHighValue as Variant
strCurrDate = Format(Date, "yymmdd")
varHighValue = DMax("[MyField]", "MyTable", "Left([MyField], 6) = '" _
& strCurrDate & "'")
If IsNull(varHighValue) Then
GetNextNumber = strCurrDate & "001"
Else
GetNextNumber = =Format(CLng(Left(varHighValue,6))+1,"000000000")
End If
End Function
To use it, make it the Default Value of the text box where you display the
number
:
I need a way for one of my fields to change
incrementally as new records are created.
This field will always have 9 numbers max.
It must start with todays date in the
format 051117 and then 001
This should change to 002 for the next record
The date should also change;
i.e. at 11:59pm the last record is
051117052 at 12:00am it will automatically
change to 051118001
there will never be a "000" record.
Any thoughts would be greatly appreciated.
Tom
