I need to update related records after a user updates the core record. Assume there are three related records. The user accesses a form and updates record X. When the user clicks "close" a set of VB scripts runs. A bunch of TempVars are created from information in record X. Two queries are run. The first says, "Take <fields> from X and update those fields in records Y and Z. All of these fields should match for related records. The second query then says, if records Y and Z are not locked (by fldLock) update a different set of fields, which may or may not match across related fields. The lock field is a Yes/No data type. The query uses a TempVar to filter to only records with the proper incidentNumber, and then it is supposed to use the fldLock to only update records where the Yes/No is NOT checked, i.e. is False.
When I actually run the query, the fldLock filter isn't working. No records are being returned and therefore the updates aren't occurring where they should. I assume I'm not querying the Yes/No field appropriately. There's a sample below, and I've also tried fldLock Is Null (because that seems to work in a select query).
I get the warning that the query is running, then that it is updating 0 records. Without the fldLock field included Everything works fine, but I can't protect the records that need to maintain separate information. Can someone suggest how to correctly write the above query (or last line thereof) so that any records matching in the IncidentNumber, that are not locked will be updated with the existing tempVars?
When I actually run the query, the fldLock filter isn't working. No records are being returned and therefore the updates aren't occurring where they should. I assume I'm not querying the Yes/No field appropriately. There's a sample below, and I've also tried fldLock Is Null (because that seems to work in a select query).
SQL:
UPDATE tblCaseInfo
SET tblCaseInfo.Location = [TempVars]![tVincLoc],
tblCaseInfo.ServiceElement = [TempVars]![tVservEl],
tblCaseInfo.IncDate = [TempVars]![tVincDt],
tblCaseInfo.DatIncReported = [TempVars]![tVrepDt]
WHERE ( ( ( tblCaseInfo.IncidentNumber ) = [TempVars]![tVincNum] )
AND ( ( tblCaseInfo.fldLock ) =False ) );
I get the warning that the query is running, then that it is updating 0 records. Without the fldLock field included Everything works fine, but I can't protect the records that need to maintain separate information. Can someone suggest how to correctly write the above query (or last line thereof) so that any records matching in the IncidentNumber, that are not locked will be updated with the existing tempVars?