T
Ted
this is kind of a doozie for this newbie.....
i have a database i'll call 'A' and i have placed the
following in the OnCurrent event property of the form
called 'Protocol'
Private Sub Form_Current()
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
the field 'CCI_Number' is linked to a field in another
database, we'll call that database 'B', and the table is
called 'Register Protocol'. the row source for the
CCI_Number field in 'A' is a SQL query:
SELECT Protocol_Tracking.CCI_Number
FROM Protocol_Tracking
WHERE (((Protocol_Tracking.[IRB Number])=[Forms]!
[Protocol]![IRB_Number]));
so that when the right arrow is clicked the query's
launced and the relevant CCI_Number matching the WHERE
clause appears in the combo box (obviously the table
in 'B' is called 'Protocol_Tracking' but i don't think
that really matters all that much).
expanding on my description, the database 'B' has been
around for a long time and many many records were entered
into its 'Protocol' table. then along came database 'A'
and the decision that hereafter, information
like 'CCI_Number', being entered into 'A' should not be
re-entered into 'B', but rather 'B' should be able to link
with 'A' and the data electronically extracted.
the VBA code above seems to do the job quite well UNTIL it
encounters a record where a pre-existing value for
CCI_Number had been entered before the creation of the 'A'
database. at that point it very briefly displays the
underlying value in the table behind 'Protocol' form's and
then the control goes blank!!!
what i think i need is some way to tell a2k and/or vba to
ignore records which have values that are not found when
the SQL query runs---this query will only ever be of use
when entering data into 'B' from 'A' but never of use when
wanting to look at records that were entered prior to the
creation of database 'B'.
what i see is that when the vba code i wrote is actuated
on any record, if the resulting value of 'CCI_Number' is
null (which it would be in every instance when talking
about a record in the database that was created prior to
the inception of the newer database, 'A') it proceeds to
enter a null value into the CCI_Number's control. this is
not appropriate, in that the number that belongs in the
control is the number that appears in the underlying
table, the number that was already long there before the
SQL query got invented to add new records to the data
table.
how would you work around this? or could you even begin
to? is there some way to get another SQL query to run
conditionally when, e.g. the Row Source query (above)
ffinds that the value in the table underlying the
CCI_Nuumber on the 'Protocol' form is non-null and not in
the resulting 'list'?
i have a database i'll call 'A' and i have placed the
following in the OnCurrent event property of the form
called 'Protocol'
Private Sub Form_Current()
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
the field 'CCI_Number' is linked to a field in another
database, we'll call that database 'B', and the table is
called 'Register Protocol'. the row source for the
CCI_Number field in 'A' is a SQL query:
SELECT Protocol_Tracking.CCI_Number
FROM Protocol_Tracking
WHERE (((Protocol_Tracking.[IRB Number])=[Forms]!
[Protocol]![IRB_Number]));
so that when the right arrow is clicked the query's
launced and the relevant CCI_Number matching the WHERE
clause appears in the combo box (obviously the table
in 'B' is called 'Protocol_Tracking' but i don't think
that really matters all that much).
expanding on my description, the database 'B' has been
around for a long time and many many records were entered
into its 'Protocol' table. then along came database 'A'
and the decision that hereafter, information
like 'CCI_Number', being entered into 'A' should not be
re-entered into 'B', but rather 'B' should be able to link
with 'A' and the data electronically extracted.
the VBA code above seems to do the job quite well UNTIL it
encounters a record where a pre-existing value for
CCI_Number had been entered before the creation of the 'A'
database. at that point it very briefly displays the
underlying value in the table behind 'Protocol' form's and
then the control goes blank!!!
what i think i need is some way to tell a2k and/or vba to
ignore records which have values that are not found when
the SQL query runs---this query will only ever be of use
when entering data into 'B' from 'A' but never of use when
wanting to look at records that were entered prior to the
creation of database 'B'.
what i see is that when the vba code i wrote is actuated
on any record, if the resulting value of 'CCI_Number' is
null (which it would be in every instance when talking
about a record in the database that was created prior to
the inception of the newer database, 'A') it proceeds to
enter a null value into the CCI_Number's control. this is
not appropriate, in that the number that belongs in the
control is the number that appears in the underlying
table, the number that was already long there before the
SQL query got invented to add new records to the data
table.
how would you work around this? or could you even begin
to? is there some way to get another SQL query to run
conditionally when, e.g. the Row Source query (above)
ffinds that the value in the table underlying the
CCI_Nuumber on the 'Protocol' form is non-null and not in
the resulting 'list'?