Katrina-
You would need to add a column to PS_CaseID_Next_tbl to keep track of the date.
You would also need to make sure that the PS_Police_Case_id field can handle the
digits to hold the date as well as sufficient digits to generate a unique number
for all the cases in one day. Right now, it looks like PS_Police_Case_id
contains:
YY - nnnnn
.. where YY is the year, and nnnnn is the sequential case number. The field
would need to be large enough to contain something like:
YYYYMMDD-nnn
.. where YYYYMMDD is the full date, and nnn can handle up to 999 cases per day.
Let's assume there's a new field called Case_Date in PS_CaseID_Next_tbl. Your
new code might look like:
----------------------------------------------------------
Private Sub PS_Police_case_id_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim Rec As DAO.Recordset
Dim addDate As Date
Dim CASE_Num As Double
Dim intlockretry As Integer
Const Lock_Retry_Max = 7
intlockretry = 0
' Grab today's date
addDate = Date
Set db = CurrentDb() ' Return reference to current database.
'Open the Next Help Number table
Set Rec = db.OpenRecordset("PS_CaseID_Next_tbl", , dbDenyWrite)
'and place an exclusive lock on it
With Rec
.Edit
' If last date is today,
If !Case_Date = addDate Then
' .. just increment the number
![NEXTCASENO] = ![NEXTCASENO] + 1
Else
' Reset the case number
![NEXTCASENO] = 1
' Save the date
!Case_Date = addDate
End If
' Save the new number
CASE_Num = Rec![NEXTCASENO]
.Update 'Save changes to record.
End With
Rec.Close 'close the table
Set db = Nothing 'disassociate the reference
'Assign the new Help Number in the work variable to the form field
Me.PS_Police_Case_id = Format(addDate, "yyyymmdd") & "-" & _
Format(CASE_Num, "000")
Me.PS_Police_Case_id.BackColor = 11796479
End Sub
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Lahaina, HI)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
Sent: Tuesday, January 31, 2012 12:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Question on VBA Coding for double click field
Clive,
After researching this database and asking a few people, I have found that the
creator of this database does not have a code that restarts the case number of
January 1st. She manually goes in and changes the table every 1st of January to
1. How can I setup a code to automatically do this everyday instead of yearly?
Thanks!
Katrina
--- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@...> wrote:
>
> Hi Katrina,
>
> The code you posted handles the incrementing of
> the NEXTCASENO.
>
> There must be some other code that detects the
> EndOfYear so that the NEXTCASENO can be initilaized
> when the year changes. That is probably the code
> that needs to be changed.
>
> Can you find that code and post it to us please.
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@>
wrote:
> >
> > I have a database used by our communications center which allows dispatchers
to
> > enter incidents that occur. On this form there is a Case ID which when
double
> > clicked assigns a new case ID to the incident. I did not create this
database,
> > and because I'm not too familiar with vba coding I'm not exactly sure what
to
> > do. What we want is to have the Case ID start at 1 everyday instead of
yearly.
> > The following is the code I found when the Case ID is double clicked. If
more
> > information is needed I can try to upload the database to the Assistance
Needed
> > folder.
> > -----------------------
> > Private Sub PS_Police_case_id_DblClick(Cancel As Integer)
> > Dim db As DAO.Database
> > Dim Rec As DAO.Recordset
> > Dim addDate
> > Dim addTime
> > Dim CASE_Num As Double
> > Dim intlockretry As Integer
> > Const Lock_Retry_Max = 7
> >
> >
> > intlockretry = 0
> >
> > Set db = CurrentDb() ' Return reference to current database.
> > Set Rec = db.OpenRecordset("PS_CaseID_Next_tbl", , dbDenyWrite) 'Open the
> > Next Help Number table
> > 'and place an exclusive
> > lock on it
> > With Rec
> > .Edit
> >
> > ![NEXTCASENO] = ![NEXTCASENO] + 1 'Increment the Help Number
> >
> > CASE_Num = Rec![NEXTCASENO] 'Assign new Help Number to a work variable
> > .Update 'Save changes to record.
> > End With
> >
> > Rec.Close 'close the table
> > Set db = Nothing 'disassociate the reference
> >
> >
> > ' Me.PS_Police_Case_id = "09 - " & Right("00000" & [CASE_Num], 5) 'Assign
the
> > new Help Number in the work variable to the form field
> > Me.PS_Police_Case_id = Right(Year(Date), 2) & " - " & Right("00000" &
> > [CASE_Num], 5) 'Assign the new Help Number in the work variable to the form
> > field
> >
> > Me.PS_Police_Case_id.BackColor = 11796479
> >
> > 'MsgBox (Me.Dirty) 'IMPORTANT KEEP THIS - It determines whether your form is
> > dirty or not
> > 'example: true means the record has changed it should
> > show "False"
> > End Sub
> > -------------------
> >
> > Any help is appreciated!!
> >
> > Thanks!
> >
> > Katrina
> >
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar