Default value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a pable that i want the primary key to be the the date followed by a
sequetail number and I need the number to restart each day>... Can someone
assit me with the exact coding scheme. I would like for it to look like
05102005-001
 
Hi Rhonda,

You need a control table with a field like last_date (date) and my_counter
(numeric)

check the table when adding a new record make sure the last_date is = to
today then just increment my_counter + 1 and use that value, if the
last_date <> today then set the last_date value = today and set my_counter =
1

then get the value and do the following

dim strpKey as string
dim lngMyCounter as long ' this gets the new value from the control table.

strpKey = trim(format(now,"dd") & trim(format(now,"mm") &
trim(format(now,"yyyy") & "-" & trim(str(lngMyCounter))
 
Rhonda,
You will need a 2 field primary key that you can, then, display in the
format that you need. One field would be a date data type field and have
it's default value set to Date(). The other would be a long integer field
and you could use the following code in the BeforeUpdate event of your form
to calculate the appropriate sequential number:

If IsNull(Me.txtID_Number) And DMax("[ID_Number]", "YourTable", "[YourDate]
= #" & Me.YourDate & "#") > 0 Then
Me.txtID_Number = DMax("[ID_Number]", "YourTable", "[YourDate] = #" & Date
& "#") + 1
Else
Me.txtID_Number = 1
End If

Watch out for word wrap on the lines of code above. Everything from "If" to
"Then" needs to be on one line. Also, you may need to change the field name
for your sequential number to match what you have in your table and on your
form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
Back
Top