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 obviously the table in 'B' is
called 'Protocol_Tracking' but i don't think that really
matters all that much)
in words, the database 'B' has been around for a long time
and many many records have been 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'.
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
database 'A') it proceeds to enter the null value into the
control.
how would you work around this? or could you even begin to?
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 obviously the table in 'B' is
called 'Protocol_Tracking' but i don't think that really
matters all that much)
in words, the database 'B' has been around for a long time
and many many records have been 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'.
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
database 'A') it proceeds to enter the null value into the
control.
how would you work around this? or could you even begin to?