T
Ted
i have a pair of databases that have a couple of tables
that are 'linked'....
the older one has a form called 'Protocol' which has been
maintained for several years and has hundreds of records
(its recordsource is a table of the same name).
the newer one has a link to the 'Protocol' table and a
form called 'Register Protocol' (whose recordsource is a
table called 'Protocol_Tracking').
the mgt decided that going into the future, the person
entering certain identifying type data into 'Protocol'
would be entering only data that has been entered
into 'Protocol_Tracking'. this it is felt will eliminate
duplication of effort and minimize data entry errors since
the 'Register_Protocol' form would utilize comboboxes
instead of free text controls which it had previously that
would restrict the available entries to those previously
entered into the fledgling 'Protocol_Tracking' table in
the newer database by another user.
to drill down a bit further into the nitty gritty details,
there is a field called 'CCI_Number' that gets its value
conditionally upon 'IRB Number', so i have created some
SQL code to fetch it in the 'RowSource' of 'CCI_Number'
(see below
SELECT [Protocol_Tracking].[CCI_Number] FROM
Protocol_Tracking WHERE ((([Protocol_Tracking].[IRB
Number])=[Forms]![Protocol]![IRB_Number]));
since CCRC_Number is pre-deterined every time a user
of 'Protocol' scrolls from one record to the next, i
thought it would be good to create an 'OnCurrent' event
that updated it (below is VBA code)
Private Sub Form_Current()
Me.IRB_Number.Requery
Me.CCI_Number.Requery
Me.CCI_Number.SetFocus
Me.CCI_Number.Text = IIf(IsNull(Me.CCI_Number.ItemData
(0)), " ", (Me.CCI_Number.ItemData(0)))
End Sub
this code works up to a point....whenever the CCI_Number
on a record in 'Protocol' requires completing, and it's
never been entered (i.e., the value in this record does
not exist on the 'Protocol' owing to its having been
created over the long history of the table), its value is
automatically entered into the CCI_Number control;
whenever it's not been entered already in
the 'Protocol_Tracking' table, its value remains blank.
HOWEVER, the fly in the soup is coming from the fact that
if there is already a valid CCI_Number in the
underlying 'Protocol' table, the effect of this code is to
store a blank value in it.
there's some sort of workaround that's required i think,
something that would perhaps trigger the 'OnCurrent' event
only when the data is in the list, or null, and allow the
extant CCI_Number in 'Protocol_Tracking' to remain as is
when it's not.
i'm a relative newbie at this...
that are 'linked'....
the older one has a form called 'Protocol' which has been
maintained for several years and has hundreds of records
(its recordsource is a table of the same name).
the newer one has a link to the 'Protocol' table and a
form called 'Register Protocol' (whose recordsource is a
table called 'Protocol_Tracking').
the mgt decided that going into the future, the person
entering certain identifying type data into 'Protocol'
would be entering only data that has been entered
into 'Protocol_Tracking'. this it is felt will eliminate
duplication of effort and minimize data entry errors since
the 'Register_Protocol' form would utilize comboboxes
instead of free text controls which it had previously that
would restrict the available entries to those previously
entered into the fledgling 'Protocol_Tracking' table in
the newer database by another user.
to drill down a bit further into the nitty gritty details,
there is a field called 'CCI_Number' that gets its value
conditionally upon 'IRB Number', so i have created some
SQL code to fetch it in the 'RowSource' of 'CCI_Number'
(see below
SELECT [Protocol_Tracking].[CCI_Number] FROM
Protocol_Tracking WHERE ((([Protocol_Tracking].[IRB
Number])=[Forms]![Protocol]![IRB_Number]));
since CCRC_Number is pre-deterined every time a user
of 'Protocol' scrolls from one record to the next, i
thought it would be good to create an 'OnCurrent' event
that updated it (below is VBA code)
Private Sub Form_Current()
Me.IRB_Number.Requery
Me.CCI_Number.Requery
Me.CCI_Number.SetFocus
Me.CCI_Number.Text = IIf(IsNull(Me.CCI_Number.ItemData
(0)), " ", (Me.CCI_Number.ItemData(0)))
End Sub
this code works up to a point....whenever the CCI_Number
on a record in 'Protocol' requires completing, and it's
never been entered (i.e., the value in this record does
not exist on the 'Protocol' owing to its having been
created over the long history of the table), its value is
automatically entered into the CCI_Number control;
whenever it's not been entered already in
the 'Protocol_Tracking' table, its value remains blank.
HOWEVER, the fly in the soup is coming from the fact that
if there is already a valid CCI_Number in the
underlying 'Protocol' table, the effect of this code is to
store a blank value in it.
there's some sort of workaround that's required i think,
something that would perhaps trigger the 'OnCurrent' event
only when the data is in the list, or null, and allow the
extant CCI_Number in 'Protocol_Tracking' to remain as is
when it's not.
i'm a relative newbie at this...