Select Case Statements & OldValue Property

T

Ted

I'm trying to suss out which way to do this in my A2K application.

When a user clicks the radio button linked to the 5, the objective is to
launch a query which uses two of the current form's control values as
criteria that determine which record from the current form's underlying table
gets appended to a linked table in the current database. As I understand the
OldValue property (courtesy of A2K help doc'n), "You can use the OldValue
property to determine the unedited value of a bound control".

What I have tried to do in the code I am attaching is lay out the various
actions I want to take place when, given the 'unedited value' of the bound
control, the user presses any one of the radio buttons in the Frame1.

This doesn't seem to getting quite off the ground as yet.

Are there any obvious Achilles' Heels in this so far you can make out?

-Ted

VBA Code:


Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3 Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7 Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case Else
End Select

End Sub


=====================================================

SQL version of Append Query:

INSERT INTO [Patients on Follow-Up] ( Dummy, [IRB Number], MR_Number, [Pt
Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].RegisteredDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening Log].Outcome_) In (5)));
 
J

Jeff Boyce

Ted

I can't quite tell from your description, but the .OldValue property applies
when the control's value has been changed. How does that relate to what's
happening on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ted said:
I'm trying to suss out which way to do this in my A2K application.

When a user clicks the radio button linked to the 5, the objective is to
launch a query which uses two of the current form's control values as
criteria that determine which record from the current form's underlying
table
gets appended to a linked table in the current database. As I understand
the
OldValue property (courtesy of A2K help doc'n), "You can use the OldValue
property to determine the unedited value of a bound control".

What I have tried to do in the code I am attaching is lay out the various
actions I want to take place when, given the 'unedited value' of the bound
control, the user presses any one of the radio buttons in the Frame1.

This doesn't seem to getting quite off the ground as yet.

Are there any obvious Achilles' Heels in this so far you can make out?

-Ted

VBA Code:


Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3 Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7 Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case Else
End Select

End Sub


=====================================================

SQL version of Append Query:

INSERT INTO [Patients on Follow-Up] ( Dummy, [IRB Number], MR_Number, [Pt
Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[IRB Number], [Screening
Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].RegisteredDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening
Log
(Edit Only)].[MR Number]) AND (([Screening Log].Outcome_) In (5)));
Click to expand...
 
T

Ted

Jeff,

Well....I think it has a bearing on all this, because I have to allow for
the fact that the user arrives at a record on the form where the oldvalue was
for example a 5 and wants to 'correct' that entry. Since that record would
have already been exported to the table named in the Append query, then we
want to test for the fact that moving to a Case 2 for example from a Case 5
is what describes the users state of affairs and handle the launching of the
Delete query. Does that make sense?

Also, I think part of the problem is due to some fact(s) that are
transparent to my posting, namely that there are some fields named in the
Append query which are required in the targeted table but which are not
always entered in the table underlying the form holding this options group.
Data entry folks were told about that and are working on entering those data.

Best,

Ted

Jeff Boyce said:
Ted

I can't quite tell from your description, but the .OldValue property applies
when the control's value has been changed. How does that relate to what's
happening on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ted said:
I'm trying to suss out which way to do this in my A2K application.

When a user clicks the radio button linked to the 5, the objective is to
launch a query which uses two of the current form's control values as
criteria that determine which record from the current form's underlying
table
gets appended to a linked table in the current database. As I understand
the
OldValue property (courtesy of A2K help doc'n), "You can use the OldValue
property to determine the unedited value of a bound control".

What I have tried to do in the code I am attaching is lay out the various
actions I want to take place when, given the 'unedited value' of the bound
control, the user presses any one of the radio buttons in the Frame1.

This doesn't seem to getting quite off the ground as yet.

Are there any obvious Achilles' Heels in this so far you can make out?

-Ted

VBA Code:


Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3 Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7 Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete Off
Study Pxs--Edit Form")
Case Else
End Select

End Sub


=====================================================

SQL version of Append Query:

INSERT INTO [Patients on Follow-Up] ( Dummy, [IRB Number], MR_Number, [Pt
Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[IRB Number], [Screening
Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].RegisteredDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening
Log
(Edit Only)].[MR Number]) AND (([Screening Log].Outcome_) In (5)));
Click to expand...
Click to expand...
 
J

Jeff Boyce

Ted

Sorry, but I'm not seeing the connection between updating a field in a form
(i.e., OldValue property-related) and an Append (or a Delete) query.

If I have a form with controls on it, bound to a data source, and I pull up
a record in that form and change a field's value, I can access the field's
OldValue. Is this what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ted said:
Jeff,

Well....I think it has a bearing on all this, because I have to allow for
the fact that the user arrives at a record on the form where the oldvalue
was
for example a 5 and wants to 'correct' that entry. Since that record would
have already been exported to the table named in the Append query, then we
want to test for the fact that moving to a Case 2 for example from a Case
5
is what describes the users state of affairs and handle the launching of
the
Delete query. Does that make sense?

Also, I think part of the problem is due to some fact(s) that are
transparent to my posting, namely that there are some fields named in the
Append query which are required in the targeted table but which are not
always entered in the table underlying the form holding this options
group.
Data entry folks were told about that and are working on entering those
data.

Best,

Ted

Jeff Boyce said:
Ted

I can't quite tell from your description, but the .OldValue property
applies
when the control's value has been changed. How does that relate to
what's
happening on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ted said:
I'm trying to suss out which way to do this in my A2K application.

When a user clicks the radio button linked to the 5, the objective is
to
launch a query which uses two of the current form's control values as
criteria that determine which record from the current form's underlying
table
gets appended to a linked table in the current database. As I
understand
the
OldValue property (courtesy of A2K help doc'n), "You can use the
OldValue
property to determine the unedited value of a bound control".

What I have tried to do in the code I am attaching is lay out the
various
actions I want to take place when, given the 'unedited value' of the
bound
control, the user presses any one of the radio buttons in the Frame1.

This doesn't seem to getting quite off the ground as yet.

Are there any obvious Achilles' Heels in this so far you can make out?

-Ted

VBA Code:


Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3 Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7 Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case Else
End Select

End Sub


=====================================================

SQL version of Append Query:

INSERT INTO [Patients on Follow-Up] ( Dummy, [IRB Number], MR_Number,
[Pt
Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[IRB Number], [Screening
Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].RegisteredDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code =
[Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening
Log].MR_Number)=[Forms]![Screening
Log
(Edit Only)].[MR Number]) AND (([Screening Log].Outcome_) In (5)));
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

The value 5 represents a status of a patient's who has gone 'off study' and
the researchers want to add parts of the patient's record in -this-
particular mdb to another (linked) table in a different database. If the
Outcome_ value when they peruse a record is 5 and they want to change it to
be Case 2, then that means they're telling us that the Patient was
inadvertantly coded as being Off Study and should be coded and 'On Treatment'
(at least I think that's what Case 2 means) and therefore they would not want
that patient's record in the linked table. To go thru all the possible
combinations is probably not a good idea, but I think you should be beginning
to get the idea. Am I correct? HTH..

Ted

Jeff Boyce said:
Ted

Sorry, but I'm not seeing the connection between updating a field in a form
(i.e., OldValue property-related) and an Append (or a Delete) query.

If I have a form with controls on it, bound to a data source, and I pull up
a record in that form and change a field's value, I can access the field's
OldValue. Is this what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ted said:
Jeff,

Well....I think it has a bearing on all this, because I have to allow for
the fact that the user arrives at a record on the form where the oldvalue
was
for example a 5 and wants to 'correct' that entry. Since that record would
have already been exported to the table named in the Append query, then we
want to test for the fact that moving to a Case 2 for example from a Case
5
is what describes the users state of affairs and handle the launching of
the
Delete query. Does that make sense?

Also, I think part of the problem is due to some fact(s) that are
transparent to my posting, namely that there are some fields named in the
Append query which are required in the targeted table but which are not
always entered in the table underlying the form holding this options
group.
Data entry folks were told about that and are working on entering those
data.

Best,

Ted

Jeff Boyce said:
Ted

I can't quite tell from your description, but the .OldValue property
applies
when the control's value has been changed. How does that relate to
what's
happening on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm trying to suss out which way to do this in my A2K application.

When a user clicks the radio button linked to the 5, the objective is
to
launch a query which uses two of the current form's control values as
criteria that determine which record from the current form's underlying
table
gets appended to a linked table in the current database. As I
understand
the
OldValue property (courtesy of A2K help doc'n), "You can use the
OldValue
property to determine the unedited value of a bound control".

What I have tried to do in the code I am attaching is lay out the
various
actions I want to take place when, given the 'unedited value' of the
bound
control, the user presses any one of the radio buttons in the Frame1.

This doesn't seem to getting quite off the ground as yet.

Are there any obvious Achilles' Heels in this so far you can make out?

-Ted

VBA Code:


Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3 Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7 Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro ("Delete
Off
Study Pxs--Edit Form")
Case Else
End Select

End Sub


=====================================================

SQL version of Append Query:

INSERT INTO [Patients on Follow-Up] ( Dummy, [IRB Number], MR_Number,
[Pt
Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[IRB Number], [Screening
Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].RegisteredDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code =
[Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening
Log].MR_Number)=[Forms]![Screening
Log
(Edit Only)].[MR Number]) AND (([Screening Log].Outcome_) In (5)));
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Ted

Let me try to paraphrase...

You have a record in one database (i.e., an .mdb file).

You have a related record in another database (i.e., a different .mdb file).

You can pull up a record in DB1 and change one of the values (e.g.,
[Outcome]). I'll assume there's no issue with have the record in DB1 be
updated by the change.

You also want to have the related record in DB2 change, to reflect the new
[Outcome].

If this is your situation (paraphrased), I have a couple
comments/questions...

First, why store the data in two places (two tables in one DB or in one
table each in two DBs)?

Second, if each of the DBs are Access DBs, you can link to both of them from
a "front-end", rather than have to treat each as a stand-alone data store.
And if you are linked to both DBs, you can use an update query to modify the
second table's record (if you absolutely must have two copies of the same
data -- not often necessary in a well-normalized database).

Third, the above two will only work if there is a common rowID (?patientID)
that the two tables share. You won't be able to find the right record to
update in table2 if it doesn't have the same ID as the corresponding row in
table1.

Now, what am I still missing?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ted said:
The value 5 represents a status of a patient's who has gone 'off study'
and
the researchers want to add parts of the patient's record in -this-
particular mdb to another (linked) table in a different database. If the
Outcome_ value when they peruse a record is 5 and they want to change it
to
be Case 2, then that means they're telling us that the Patient was
inadvertantly coded as being Off Study and should be coded and 'On
Treatment'
(at least I think that's what Case 2 means) and therefore they would not
want
that patient's record in the linked table. To go thru all the possible
combinations is probably not a good idea, but I think you should be
beginning
to get the idea. Am I correct? HTH..

Ted

Jeff Boyce said:
Ted

Sorry, but I'm not seeing the connection between updating a field in a
form
(i.e., OldValue property-related) and an Append (or a Delete) query.

If I have a form with controls on it, bound to a data source, and I pull
up
a record in that form and change a field's value, I can access the
field's
OldValue. Is this what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ted said:
Jeff,

Well....I think it has a bearing on all this, because I have to allow
for
the fact that the user arrives at a record on the form where the
oldvalue
was
for example a 5 and wants to 'correct' that entry. Since that record
would
have already been exported to the table named in the Append query, then
we
want to test for the fact that moving to a Case 2 for example from a
Case
5
is what describes the users state of affairs and handle the launching
of
the
Delete query. Does that make sense?

Also, I think part of the problem is due to some fact(s) that are
transparent to my posting, namely that there are some fields named in
the
Append query which are required in the targeted table but which are not
always entered in the table underlying the form holding this options
group.
Data entry folks were told about that and are working on entering those
data.

Best,

Ted

:

Ted

I can't quite tell from your description, but the .OldValue property
applies
when the control's value has been changed. How does that relate to
what's
happening on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm trying to suss out which way to do this in my A2K application.

When a user clicks the radio button linked to the 5, the objective
is
to
launch a query which uses two of the current form's control values
as
criteria that determine which record from the current form's
underlying
table
gets appended to a linked table in the current database. As I
understand
the
OldValue property (courtesy of A2K help doc'n), "You can use the
OldValue
property to determine the unedited value of a bound control".

What I have tried to do in the code I am attaching is lay out the
various
actions I want to take place when, given the 'unedited value' of the
bound
control, the user presses any one of the radio buttons in the
Frame1.

This doesn't seem to getting quite off the ground as yet.

Are there any obvious Achilles' Heels in this so far you can make
out?

-Ted

VBA Code:


Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3
Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7
Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case Else
End Select

End Sub


=====================================================

SQL version of Append Query:

INSERT INTO [Patients on Follow-Up] ( Dummy, [IRB Number],
MR_Number,
[Pt
Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[IRB Number], [Screening
Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials],
[Screening
Log].RegisteredDate AS [On-Study Date], [Screening Log].Campus AS
Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code =
[Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening
Log].MR_Number)=[Forms]![Screening
Log
(Edit Only)].[MR Number]) AND (([Screening Log].Outcome_) In (5)));
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

Hi Jeff,

I will intersperse my responses amidst your reply .....

Jeff Boyce said:
Ted

Let me try to paraphrase...

You have a record in one database (i.e., an .mdb file). YES

You have a related record in another database (i.e., a different .mdb file). NOT UNTIL IT'S ADDED TO DB2 FROM DB1.

You can pull up a record in DB1 and change one of the values (e.g.,
[Outcome]). I'll assume there's no issue with have the record in DB1 be
updated by the change. YES, OUTCOME CAN CHANGE IN DB1, BUT ONLY CERTAIN CHANGES MAY AFFECT THE RELATED RECORD IN DB2.

You also want to have the related record in DB2 change, to reflect the new
[Outcome]. UNDER CERTAIN CONDITIONS (WHICH I ATTEMPT TO EXHAUSTIVELY POSIT IN THE AFTER UPDATE 'SELECT CASE' STATEMENTS

If this is your situation (paraphrased), I have a couple
comments/questions...

First, why store the data in two places (two tables in one DB or in one
table each in two DBs)? DB2 WAS CREATED TO DO ONE AND ONLY ONE THING WELL. RELATED RECORDS FROM DB1 (WHEN THIS FACET OF THE BUSINESS MODEL WAS DISCLOSED TO ME) WERE GOING TO BE ADDED/UPDATED/DELETED FROM THE TABLE IN DB2 MANUALLY. TO PUT IT ANOTHER WAY, I'M ALWAYS THE LAST TO KNOW WHAT'S ON THE MINDS OF THE FOLKS WHO HAVE A NEED FOR THESE DBS. DB2'S GETTING TIED IN WITH DB1 CAME TO THE 'CLIENT' AS A SORT OF AFTER-THOUGHT.

Second, if each of the DBs are Access DBs, you can link to both of them from
a "front-end", rather than have to treat each as a stand-alone data store.
And if you are linked to both DBs, you can use an update query to modify the
second table's record (if you absolutely must have two copies of the same
data -- not often necessary in a well-normalized database). BOTH ARE A2K MDBS AND I INDEED TO INTEND TO USE THE UPDATE QUERY CAPABILITY WHEN I GET AROUND TO WRITING ONE TO DOING SAME.

Third, the above two will only work if there is a common rowID (?patientID)
that the two tables share. You won't be able to find the right record to
update in table2 if it doesn't have the same ID as the corresponding row in
table1. AWARE OF IT...WELL AWARE.

Now, what am I still missing?! NOTHING, I GUESS.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ted said:
The value 5 represents a status of a patient's who has gone 'off study'
and
the researchers want to add parts of the patient's record in -this-
particular mdb to another (linked) table in a different database. If the
Outcome_ value when they peruse a record is 5 and they want to change it
to
be Case 2, then that means they're telling us that the Patient was
inadvertantly coded as being Off Study and should be coded and 'On
Treatment'
(at least I think that's what Case 2 means) and therefore they would not
want
that patient's record in the linked table. To go thru all the possible
combinations is probably not a good idea, but I think you should be
beginning
to get the idea. Am I correct? HTH..

Ted

Jeff Boyce said:
Ted

Sorry, but I'm not seeing the connection between updating a field in a
form
(i.e., OldValue property-related) and an Append (or a Delete) query.

If I have a form with controls on it, bound to a data source, and I pull
up
a record in that form and change a field's value, I can access the
field's
OldValue. Is this what you are trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Well....I think it has a bearing on all this, because I have to allow
for
the fact that the user arrives at a record on the form where the
oldvalue
was
for example a 5 and wants to 'correct' that entry. Since that record
would
have already been exported to the table named in the Append query, then
we
want to test for the fact that moving to a Case 2 for example from a
Case
5
is what describes the users state of affairs and handle the launching
of
the
Delete query. Does that make sense?

Also, I think part of the problem is due to some fact(s) that are
transparent to my posting, namely that there are some fields named in
the
Append query which are required in the targeted table but which are not
always entered in the table underlying the form holding this options
group.
Data entry folks were told about that and are working on entering those
data.

Best,

Ted

:

Ted

I can't quite tell from your description, but the .OldValue property
applies
when the control's value has been changed. How does that relate to
what's
happening on your form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


I'm trying to suss out which way to do this in my A2K application.

When a user clicks the radio button linked to the 5, the objective
is
to
launch a query which uses two of the current form's control values
as
criteria that determine which record from the current form's
underlying
table
gets appended to a linked table in the current database. As I
understand
the
OldValue property (courtesy of A2K help doc'n), "You can use the
OldValue
property to determine the unedited value of a bound control".

What I have tried to do in the code I am attaching is lay out the
various
actions I want to take place when, given the 'unedited value' of the
bound
control, the user presses any one of the radio buttons in the
Frame1.

This doesn't seem to getting quite off the ground as yet.

Are there any obvious Achilles' Heels in this so far you can make
out?

-Ted

VBA Code:


Private Sub Frame1_AfterUpdate()
Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 2
Me.REgisteredDAte.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 3
Me.OnStudyDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 4
Me.TXEndedDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 5
Me.OffStudyDate.SetFocus
If Me.Frame1.OldValue = 2 Or Me.Frame1.OldValue = 3
Or
Me.Frame1.OldValue = 4 _
Or Me.Frame1.OldValue = 6 Or Me.Frame1.OldValue = 7
Then
DoCmd.RunMacro ("Append Off Study Pxs--Edit Form")
Case 6
Me.LTFUDate.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case 7
Me.DateDth.SetFocus
If Me.Frame1.OldValue = 5 Then DoCmd.RunMacro
("Delete
Off
Study Pxs--Edit Form")
Case Else
End Select

End Sub


=====================================================

SQL version of Append Query:

INSERT INTO [Patients on Follow-Up] ( Dummy, [IRB Number],
MR_Number,
[Pt
Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[IRB Number], [Screening
Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials],
[Screening
Log].RegisteredDate AS [On-Study Date], [Screening Log].Campus AS
Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code =
[Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening
Log].MR_Number)=[Forms]![Screening
Log
(Edit Only)].[MR Number]) AND (([Screening Log].Outcome_) In (5)));
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Ted

I may have focused on an insignificant aspect ... I thought you were trying
to assure that the corresponding record in DB2 was updated after a change in
DB1.

It sounds like you have two situations. In one, a new record is added in
DB1, requiring (I still question this?<G>) a record to be added in DB2. In
the other, an existing record in DB1 (which already has a corresponding
record in DB2) is changed, and you want to promulgate that change to the DB2
record.

You mention a Select Case statement and taking differential actions,
depending on what was done in changing the record in DB1. I probably don't
understand the complexities (no, I know I don't understand them!), but it
concerns me that the corresponding record in DB2 may have different data
that what is in DB1, based on the Select Case statement (or am I mis-reading
this aspect?).

Are there still unanswered questions...?

Best of luck on your project

Jeff Boyce
Microsoft Office/Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Options Group, AfterUpdate Event, OldValue Property and ControlSou 6
Select Case and SQL 0
Error 424 'Object Required' message 6
why is my VBA runmacro not working 2
Appending Records to Another Table When Certain Values Get Updates 1
Method or date member not found 1
Learning if a record exists in a table 1
ms a2k jet database engine won't recognize field as valid 3

Top