ACCESS FEED: RE: [MS_AccessPros] Re: Question on VBA Coding for double click field

Kamis, 02 Februari 2012

RE: [MS_AccessPros] Re: Question on VBA Coding for double click field

Great!

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: Thursday, February 02, 2012 5:20 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Question on VBA Coding for double click field

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
>

------------------------------------

Yahoo! Groups Links