John,
Thanks so much for your help! The database is working great!
Katrina
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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