G
Guest
I have a data entry form which has two tables as its record source. The SQL
is as follows:
SELECT [Order Entry].*
FROM [Order Entry] INNER JOIN ProcessCodes ON [Order Entry].[ID Process Key]
= ProcessCodes.[ID Process Key];
So, the record source contains all the fields from table Order Entry and the
field [chkOpt120] from the table Process Codes. The purpose of joining these
two tables in this was was so that I could create a conditional recordsource
for the checkbox [ckTensileTest]. If the field [chkOp120] is checked, then
that is to be the recordsource from the ProcessCodes table. If it isn't
checked, then the recordsource should be [Tensile Test] from the Order Entry
table. I tried to use an IIf statement in the controlsource for the field
[ckTensileTest] like this: =IIf([ckTensileTest]=-1, [chkOp120],[Tensile
Test]) but couldn't get it to work. The checkbox appears grayed out. So I
used the following code in the On Current event of the form Order Entry:
Private Sub Form_Current()
If Me.chkOp120 = -1 Then
Me.ckTensileTest = Me.chkOp120
Else
Me.ckTensileTest = Me.[Tensile Test]
End If
End Sub
The code works like it's supposed to, but is causing some problems when we
try to enter new records. Because table Order Entry is joined to table
ProcessCodes by field [ID_Process_Key], that field is required to be filled
in before the relationship between the two tables can exist. One cannot move
to another record or even delete the current record until this requirement is
fulfilled. This is a problem simply because at the time the new record is
being created, we don't always know what the Process Code is going to be.
The workaround is to fill in dummy data that will fulfill the requirement or
to exit the form and not allow the entered data to be saved. This slows down
data entry, since they do a lot of navigating and searching through records.
If they fill in dummy data, they run the risk of this being saved to the
table without being corrected.
I'm trying to think of a way to accomplish the conditional recordsource
without causing this problem. Perhaps if I were to add a record to the
ProcessCodes table with it's own index that the table would use as its
default value and would indicate that it is not valid data? That should
fulfill the requirement for the relationship between the two tables and can
perhaps be flagged as a value that will trigger an error message warning the
user that this value must be changed in order for the data to be valid. Does
this sound logical? If so, which event do you suggest I use? If not, do you
have any alternative suggestions? Thanks in advance!
is as follows:
SELECT [Order Entry].*
FROM [Order Entry] INNER JOIN ProcessCodes ON [Order Entry].[ID Process Key]
= ProcessCodes.[ID Process Key];
So, the record source contains all the fields from table Order Entry and the
field [chkOpt120] from the table Process Codes. The purpose of joining these
two tables in this was was so that I could create a conditional recordsource
for the checkbox [ckTensileTest]. If the field [chkOp120] is checked, then
that is to be the recordsource from the ProcessCodes table. If it isn't
checked, then the recordsource should be [Tensile Test] from the Order Entry
table. I tried to use an IIf statement in the controlsource for the field
[ckTensileTest] like this: =IIf([ckTensileTest]=-1, [chkOp120],[Tensile
Test]) but couldn't get it to work. The checkbox appears grayed out. So I
used the following code in the On Current event of the form Order Entry:
Private Sub Form_Current()
If Me.chkOp120 = -1 Then
Me.ckTensileTest = Me.chkOp120
Else
Me.ckTensileTest = Me.[Tensile Test]
End If
End Sub
The code works like it's supposed to, but is causing some problems when we
try to enter new records. Because table Order Entry is joined to table
ProcessCodes by field [ID_Process_Key], that field is required to be filled
in before the relationship between the two tables can exist. One cannot move
to another record or even delete the current record until this requirement is
fulfilled. This is a problem simply because at the time the new record is
being created, we don't always know what the Process Code is going to be.
The workaround is to fill in dummy data that will fulfill the requirement or
to exit the form and not allow the entered data to be saved. This slows down
data entry, since they do a lot of navigating and searching through records.
If they fill in dummy data, they run the risk of this being saved to the
table without being corrected.
I'm trying to think of a way to accomplish the conditional recordsource
without causing this problem. Perhaps if I were to add a record to the
ProcessCodes table with it's own index that the table would use as its
default value and would indicate that it is not valid data? That should
fulfill the requirement for the relationship between the two tables and can
perhaps be flagged as a value that will trigger an error message warning the
user that this value must be changed in order for the data to be valid. Does
this sound logical? If so, which event do you suggest I use? If not, do you
have any alternative suggestions? Thanks in advance!