Use data from field A on record I to update field B on record II

[matt]

Does that make sense? Let me put some meat on the bones of what I'm
trying to do, here:

I'm trying to keep track of my when my cases are on in court. I,
therefore, have a form with fields where I enter the "Case Number," the
"Court Date," and the "Adjourn Date" (the next date the case is on).
The Primary Key for this form is an AutoNumber (I can't use the Case
Number 'cause each case is, obviously, on more than one time).

What I would like to have happen is this. When I'm in front of a blank
form and I type in the Case Number, I would like to have the Court Date
field be automatically populated with the last Adjourn Date for that
case.

Any ideas? I'm ALL for the help!

 

[ManningFan]

You're a lawyer, man, spend some money on a freelance coder!

What happens when it's a brand new case number and there's no previous
record for it? All you're tracking is a case number, court date and
adjourn date? Are you planning to distinguish between Adjourn Date and
Continuance Date? Are you tracking hours? Is there more to the
database?

I know you lawyers like to be cryptic, I've worked with many over the
years...

 

[Guest]

Lawyer.SpendMoney is not available in Access
It requires Golf or Expensive Cars

 

[ManningFan]

Now THAT is some funny sh*t! P

Public Function SpendMoney() As String
On Error GoTo ErrorHappened
Dim CshFriv, StReturn As String

If CshFriv = "BMW" or CshFriv = "Golf" or CshFriv = "Boat" then
StReturn = "True"
Else If CshFriv = "Technology" then
StReturn = "False"
End If

ExitNow:
On Error Resume Next
Set CshFriv= Nothing
SpendMoney = StReturn
Exit Function

ErrorHappened:
MsgBox Err.Description, vbInformation,
Application.CodeContextObject.NAme
Resume ExitNow
End Function

 

[Guest]

That is really funny.

Now THAT is some funny sh*t! P

Public Function SpendMoney() As String
On Error GoTo ErrorHappened
Dim CshFriv, StReturn As String

If CshFriv = "BMW" or CshFriv = "Golf" or CshFriv = "Boat" then
StReturn = "True"
Else If CshFriv = "Technology" then
StReturn = "False"
End If

ExitNow:
On Error Resume Next
Set CshFriv= Nothing
SpendMoney = StReturn
Exit Function

ErrorHappened:
MsgBox Err.Description, vbInformation,
Application.CodeContextObject.NAme
Resume ExitNow
End Function

 

[doebtown]

"You're a lawyer, man, spend some money on a freelance coder!"

Yeah, yeah . . . I guess I should expect a few lawyer jokes gettin'
into somethin' like this. Believe you ME, there's nothing I would
RATHER do than hire someone to code this for me. I'm, unfortunately, a
prosecutor, so I've got no more of a slush fund to develop my case
tracking database than the guy who drives me to work on the bus has to
develop his mileage database (in fact, my bosses wanna know why I do
this on a computer at ALL . . . they keep track of everything on INDEX
cards!)

"All you're tracking is a case number, court date and adjourn date?"

I'm not trying to be cryptic at all. I'm just trying to keep the issue
I'm addressing clear. Yes, of course I'm tracking other stuff--I'm
tracking all SORTS of other stuff.

The table that this PARTICULAR form is based on, though, is tracking
the amount of time that case has been charged against the defendant's
speedy trial rights. In other words, there's another field in the
previously described form called "Time Charged." When I enter the
Court Date and the Adjourn Date, Access automatically suggests that the
ENTIRE duration between the two dates is Time Charged as a default and
I can then edit that time period if the judge has given me a particular
allowance for some of the time to NOT be charged against the
prosecution (if, for example, the defense attorney requested the
adjournment).

I've then got a separate form that queries all the adjournments for
that Case Number and returns the sum of the number of days entered in
the Time Charged fields associated with the adjournments.

"What happens when it's a brand new case number and there's no previous
record for it?"

Well, then that field stays blank. Or, if it's easier, Access inserts
whatever it wants in that field 'cause I'm going to have to go in and
tell it what the first court date is anyway.

What I'm ESPECIALLY trying to accomplish here is a process for
calculating how much Speedy Trial time has been charged for cases that
have been around for a while. So I'm looking at a file that has
ALREADY been to court seven times. The way I'm completing the form
now, it's working like this:

*new record*
Court Date: A
Adjourn Date: B
Time Charged: x

*new record*
Court Date: B
Adjourn Date: C
Time Charged: y

*new record*
Court Date: C
Adjourn Date: D
Time Charged: z

(and so on . . . ). It just seems to me that if I've already TOLD
access that "B" is going to be the next Court Date, it's repetitive for
me to go and type that AGAIN.

"Is there more to the database?"

Oh yeah . . . let's get this little issue sorted out, though, and THEN
I'll hi-cha up for help on all the other little . . . *issues* (for a
sneak peak at another little som'in-som'in, though, feel free to check
out:
http://groups.google.com/group/micr...6152531db25/29d4796235ea00fa#29d4796235ea00fa
)

Help = AWESOME!

Thanks!

 

[Guest]

Well, lawyer jokes are sort of a tradition, don't take it personnally. Each
profession has it's own set of jokes:
How many programmers does it take to change a light buld?

They cant' do that, It's a hardware problem.

And having been a musician before I got into IT:
What do you call a musician without a girl friend?
Homeless

Good luck with your database.

 

[ManningFan]

I just wrote a huge explanation and Google crapped out, and I'm too
tired to write it all again...

Try writing some VBA that fires on the After Update event of the Case
Number text box that runs some SQL ("SUM" query GROUP BY Case Number
and MAX of AdjournDate). If you get records, pull the MAX into the
Case Date text box. If not, leave it blank.

 

[doebtown]

"You're a lawyer, man, spend some money on a freelance coder!"

Yeah, yeah . . . I guess I should expect a few lawyer jokes gettin'
into somethin' like this. Believe you ME, there's nothing I would
RATHER do than hire someone to code this for me. I'm, unfortunately, a
prosecutor, so I've got no more of a slush fund to develop my case
tracking database than the guy who drives me to work on the bus has to
develop his mileage database (in fact, my bosses wanna know why I do
this on a computer at ALL . . . they keep track of everything on INDEX
cards!)

"All you're tracking is a case number, court date and adjourn date?"

I'm not trying to be cryptic at all. I'm just trying to keep the issue
I'm addressing clear. Yes, of course I'm tracking other stuff--I'm
tracking all SORTS of other stuff.

The table that this PARTICULAR form is based on, though, is tracking
the amount of time that case has been charged against the defendant's
speedy trial rights. In other words, there's another field in the
previously described form called "Time Charged." When I enter the
Court Date and the Adjourn Date, Access automatically suggests that the
ENTIRE duration between the two dates is Time Charged as a default and
I can then edit that time period if the judge has given me a particular
allowance for some of the time to NOT be charged against the
prosecution (if, for example, the defense attorney requested the
adjournment).

I've then got a separate form that queries all the adjournments for
that Case Number and returns the sum of the number of days entered in
the Time Charged fields associated with the adjournments.

"What happens when it's a brand new case number and there's no previous
record for it?"

Well, then that field stays blank. Or, if it's easier, Access inserts
whatever it wants in that field 'cause I'm going to have to go in and
tell it what the first court date is anyway.

What I'm ESPECIALLY trying to accomplish here is a process for
calculating how much Speedy Trial time has been charged for cases that
have been around for a while. So I'm looking at a file that has
ALREADY been to court seven times. The way I'm completing the form
now, it's working like this:

*new record*
Court Date: A
Adjourn Date: B
Time Charged: x

*new record*
Court Date: B
Adjourn Date: C
Time Charged: y

*new record*
Court Date: C
Adjourn Date: D
Time Charged: z

(and so on . . . ). It just seems to me that if I've already TOLD
access that "B" is going to be the next Court Date, it's repetitive for
me to go and type that AGAIN.

"Is there more to the database?"

Oh yeah . . . let's get this little issue sorted out, though, and THEN
I'll hi-cha up for help on all the other little . . . *issues* (for a
sneak peak at another little som'in-som'in, though, feel free to check
out:
http://groups.google.com/group/micr...6152531db25/29d4796235ea00fa#29d4796235ea00fa
)

Help = AWESOME!

Thanks!

 

[ManningFan]

What do you call a guy who hangs around a bunch of musicians? A
drummer.
What's the difference between a female vocalist and a terrorist? You
can negotiate with a terrorist.
What's the difference between a female musician and a terrorist? A
terrorist carries his own equipment.

Just a few of my faves... ) Now back to the thread!

 

[doebtown]

"You're a lawyer, man, spend some money on a freelance coder!"

Yeah, yeah . . . I guess I should expect a few lawyer jokes gettin'
into somethin' like this. Believe you ME, there's nothing I would
RATHER do than hire someone to code this for me. I'm, unfortunately, a
prosecutor, so I've got no more of a slush fund to develop my case
tracking database than the guy who drives me to work on the bus has to
develop his mileage database (in fact, my bosses wanna know why I do
this on a computer at ALL . . . they keep track of everything on INDEX
cards!)

"All you're tracking is a case number, court date and adjourn date?"

I'm not trying to be cryptic at all. I'm just trying to keep the issue
I'm addressing clear. Yes, of course I'm tracking other stuff--I'm
tracking all SORTS of other stuff.

The table that this PARTICULAR form is based on, though, is tracking
the amount of time that case has been charged against the defendant's
speedy trial rights. In other words, there's another field in the
previously described form called "Time Charged." When I enter the
Court Date and the Adjourn Date, Access automatically suggests that the
ENTIRE duration between the two dates is Time Charged as a default and
I can then edit that time period if the judge has given me a particular
allowance for some of the time to NOT be charged against the
prosecution (if, for example, the defense attorney requested the
adjournment).

I've then got a separate form that queries all the adjournments for
that Case Number and returns the sum of the number of days entered in
the Time Charged fields associated with the adjournments.

"What happens when it's a brand new case number and there's no previous
record for it?"

Well, then that field stays blank. Or, if it's easier, Access inserts
whatever it wants in that field 'cause I'm going to have to go in and
tell it what the first court date is anyway.

What I'm ESPECIALLY trying to accomplish here is a process for
calculating how much Speedy Trial time has been charged for cases that
have been around for a while. So I'm looking at a file that has
ALREADY been to court seven times. The way I'm completing the form
now, it's working like this:

*new record*
Court Date: A
Adjourn Date: B
Time Charged: x

*new record*
Court Date: B
Adjourn Date: C
Time Charged: y

*new record*
Court Date: C
Adjourn Date: D
Time Charged: z

(and so on . . . ). It just seems to me that if I've already TOLD
access that "B" is going to be the next Court Date, it's repetitive for
me to go and type that AGAIN.

"Is there more to the database?"

Oh yeah . . . let's get this little issue sorted out, though, and THEN
I'll hi-cha up for help on all the other little . . . *issues* (for a
sneak peak at another little som'in-som'in, though, feel free to check
out:
http://groups.google.com/group/micr...6152531db25/29d4796235ea00fa#29d4796235ea00fa
)

Help = AWESOME!

Thanks!