E
Emily
I have a form field in a subform that is supposed to autofill with
information from another table when an ID field is entered. This works
fine for some users, but for other users it generates an error as if
the information does not exist in the original table. I am VB and
SQL-dumb, and I can't figure out why this is happening (all users are
on XP with Access 2003).
[Note: This database has been in existence for several years and the
person who originally created it is long gone. As far as I can
determine, the reason for the autofill is so that the user can opt to
keep the original description in the second table or to edit it if
necessary (the original description is the internal company description
and the second description is what's included on transmittal reports
when the documents are sent back to clients).]
The code is as follows (runs as an After Update event on the CCINumber
field). CCINumber is the document ID. Each CCINumber is unique per
AccountNumber (which is selected in the master form if that makes a
difference). Any ideas?
Private Sub CCINumber_AfterUpdate()
Dim strSQL, Desc As Variant
strSQL = "Documents!AccountNumber =
Forms!NewTransmittal.[SentSubform]![AccountNumber]"
strSQL = strSQL & " And Documents!CCINumber =
Forms!NewTransmittal.[SentSubform]!CCINumber"
Desc = DLookup("Description", "Documents", strSQL)
If IsNull(Desc) = True Then
Result = MsgBox("Document Number Does Not Exist", vbOKOnly, "Error")
Forms!NewTransmittal.[SentSubform]!Description = Null
Else
Forms!NewTransmittal.[SentSubform]!Description = Desc
End If
End Sub
information from another table when an ID field is entered. This works
fine for some users, but for other users it generates an error as if
the information does not exist in the original table. I am VB and
SQL-dumb, and I can't figure out why this is happening (all users are
on XP with Access 2003).
[Note: This database has been in existence for several years and the
person who originally created it is long gone. As far as I can
determine, the reason for the autofill is so that the user can opt to
keep the original description in the second table or to edit it if
necessary (the original description is the internal company description
and the second description is what's included on transmittal reports
when the documents are sent back to clients).]
The code is as follows (runs as an After Update event on the CCINumber
field). CCINumber is the document ID. Each CCINumber is unique per
AccountNumber (which is selected in the master form if that makes a
difference). Any ideas?
Private Sub CCINumber_AfterUpdate()
Dim strSQL, Desc As Variant
strSQL = "Documents!AccountNumber =
Forms!NewTransmittal.[SentSubform]![AccountNumber]"
strSQL = strSQL & " And Documents!CCINumber =
Forms!NewTransmittal.[SentSubform]!CCINumber"
Desc = DLookup("Description", "Documents", strSQL)
If IsNull(Desc) = True Then
Result = MsgBox("Document Number Does Not Exist", vbOKOnly, "Error")
Forms!NewTransmittal.[SentSubform]!Description = Null
Else
Forms!NewTransmittal.[SentSubform]!Description = Desc
End If
End Sub