Message Box variations ..

[Sue Compelling]

Hi All (Access 2003)

I have an after update command that filters records based on the name of the
street being entered into the record (displayed in datasheet view). This
works fine.

I now want to be able to present a Message Box if the Street Name appears in
the table "tblexceptions" and if it does, I want the message box to show the
exception defined in the field "explanation" eg

tblexceptions looks like:

StreetName Explanation
Coronation Street tarmac laid 2008, apply surcharge of $1k per 10 mtrs
cabling
Bruce Clay underground, apply surcharge to cover cost of
specialist drilling

Message box to display:

NOTE: This street has been defined as requiring additional OSP costs.
Background: "and here I want to put the explanation from the tblexception"

Any help or explanation on how to begin would be greatly appreciated ....

TIA

Private Sub StreetNameCombo_AfterUpdate()

Dim strFilterVal As String
Dim strFilter As String
If Not IsNull(Me.StreetNameCombo) Then
strFilter = "StreetName Like ""*" & Me.StreetNameCombo & "*"""
Me.Filter = strFilter
Me.FilterOn = True

End If

End Sub

 

[Dirk Goldgar]

Hi All (Access 2003)

I have an after update command that filters records based on the name of
the
street being entered into the record (displayed in datasheet view). This
works fine.

I now want to be able to present a Message Box if the Street Name appears
in
the table "tblexceptions" and if it does, I want the message box to show
the
exception defined in the field "explanation" eg

tblexceptions looks like:

StreetName Explanation
Coronation Street tarmac laid 2008, apply surcharge of $1k per 10 mtrs
cabling
Bruce Clay underground, apply surcharge to cover cost of
specialist drilling

Message box to display:

NOTE: This street has been defined as requiring additional OSP costs.
Background: "and here I want to put the explanation from the tblexception"

Any help or explanation on how to begin would be greatly appreciated ....

TIA

Private Sub StreetNameCombo_AfterUpdate()

Dim strFilterVal As String
Dim strFilter As String
If Not IsNull(Me.StreetNameCombo) Then
strFilter = "StreetName Like ""*" & Me.StreetNameCombo & "*"""
Me.Filter = strFilter
Me.FilterOn = True

End If

End Sub

Something along the lines of:

'----- start of code -----
Private Sub StreetNameCombo_AfterUpdate()

Dim strFilterVal As String
Dim strFilter As String
Dim varException As Variant

If Not IsNull(Me.StreetNameCombo) Then

strFilter = "StreetName Like ""*" & Me.StreetNameCombo & "*"""
Me.Filter = strFilter
Me.FilterOn = True

varException = _
DLookup("Explanation", "tblExceptions", _
"StreetName = """ & Me.StreetNameCombo & """")

If Not IsNull(varException) Then
MsgBox _
"NOTE: This street has been defined as requiring " & _
"additional OSP costs. Background: " & varException, _
vbInformation, _
"Exception"
End If

End If

End Sub
'----- end of code -----

 

[Sue Compelling]

Thanks so much Dirk - prompt response and it works a treat!!!!