Display message if specific query returns a value

[Ixtreme]

I have a form and a subform that displays transactions. General
information about the transaction is diplayed on the form. The subform
contains the parties (SENDING, RECEIVING, ORIGINATOR and BENEFICIARY)
involved in the transaction. On the form the field 'cross-border
transaction' indicates if it's a crossborder transaction. I would like
to display a message on the Form (in a textfield called 'info') if:

1. the 'cross-border transaction' = Y but country code of SENDING
party = country code of RECEIVING party
2. the 'cross-border transaction' = N but country code of SENDING
party <> country code of RECEIVING party

How do I do that?

An example:

Form (frmTransaction):
transaction id = 100
cross-border = N

Subform (frmTransactionParty)
transaction id - role - country
100 - BENEFICIARY - US
100 - RECEIVING - US
100 - SENDING - UK
100 - ORIGINATOR - UK

In this case the info field should display: "Incorrect cross-border
flag!, Flag must be 'Y''"

 

[John Spencer]

I would create a small routine to be called at the appropriate times.
Appropriate times would be at least the following.
--frmTransaction Current event
--frmTransaction Crossborder control after update event
--frmTransactionParty after update event

The sub would look something like the following UNTESTED VBA code.
Public Sub fCheckCrossBorder(lngTransactionID)
Dim rst As DAO.Recordset
Dim strSQL As String
Dim tfCrossBorder As Boolean
Dim strCountry1 As String, strCountry2 As String

strSQL = "SELECT Country FROM TransactionPartyTable" & _
" WHERE [Transaction ID] = " & lngTransactionID & _
" AND Role in 'Sending','Receiving'"
tfCrossBorder = Forms!frmTransaction![CrossborderControl]

Set rst = CurrentDb().OpenRecordset(strSQL)
With rst
If .RecordCount > 0 Then
.MoveLast
If .RecordCount <> 2 Then
Forms!frmTransaction![CrossborderControl] = vbNullString
Else
strCountry2 = .Fields(0)
.MoveFirst
strCountry1 = .Fields(0)
If strCountry1 = strCountry2 And tfCrossBorder Then
Forms!frmTransaction![CrossborderControl] = "Flag should be F"
ElseIf strCountry1 <> strCountry2 And tfCrossBorder = False Then
Forms!frmTransaction![CrossborderControl] = "Flag should be T"
Else
Forms!frmTransaction![CrossborderControl] = vbNullString
End If
End If
Else
Forms!frmTransaction![CrossborderControl] = vbNullString
End If
End With
End Sub

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Ixtreme]

I am facing a few problems with your code which I modified a bit. The
field [Transaction Reference Identifier] is a string, it contains text
(for example: "TRANS-2010-09-23_00000001")

However, if I run the code below, I get an error: Too few parameters.
Expected 1.


Public Sub fCheckCrossBorder(strTransactionID)

Dim rst As DAO.Recordset
Dim strSQL As String

Dim strCrossBorder As String

Dim strCountry1 As String, strCountry2 As String

strSQL = "SELECT Country FROM trxn_leg" & _
" WHERE [Transaction Reference Identifier] = " &
strTransactionID & _
" AND [Party Role] in ('SEND','RCV')"

strCrossBorder = Forms!FOT![Cross Border Transaction Indicator]

Set rst = CurrentDb().OpenRecordset(strSQL)
With rst
If .RecordCount > 0 Then
.MoveLast
If .RecordCount <> 2 Then
Forms![FOT].Form![strInfo] = vbNullString
Else
strCountry2 = .Fields(0)
.MoveFirst
strCountry1 = .Fields(0)
If strCountry1 = strCountry2 And strCrossBorder = "Y" Then
Forms![FOT].Form![strInfo] = "Flag should be N"
ElseIf strCountry1 <> strCountry2 And strCrossBorder = "N"
Then
Forms![FOT].Form![strInfo] = "Flag should be Y"
Else
Forms![FOT].Form![strInfo] = vbNullString
End If
End If
Else
Forms![FOT].Form![strInfo] = vbNullString
End If
End With
End Sub

 

[John Spencer]

If it is a string then you need to add string delimiters.

strSQL = "SELECT Country FROM trxn_leg" & _
" WHERE [Transaction Reference Identifier] = '" &
strTransactionID & _
"' AND [Party Role] in ('SEND','RCV')"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Ixtreme]

If it is a string then you need to add string delimiters.

strSQL = "SELECT Country FROM trxn_leg" & _
           " WHERE [Transaction Reference Identifier] = '" &
strTransactionID & _
           "' AND [Party Role] in ('SEND','RCV')"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 23 sep, 14:41, John Spencer <[email protected]> wrote:

I am facing a few problems with your code which I modified a bit. The
field [Transaction Reference Identifier] is a string, it contains text
(for example: "TRANS-2010-09-23_00000001")

However, if I run the code below, I get an error: Too few parameters.
Expected 1.

Public Sub fCheckCrossBorder(strTransactionID)

Dim rst As DAO.Recordset
Dim strSQL As String

Dim strCrossBorder As String

Dim strCountry1 As String, strCountry2 As String

strSQL = "SELECT Country FROM trxn_leg" & _
          " WHERE [Transaction Reference Identifier] = " &
strTransactionID & _
          " AND [Party Role] in ('SEND','RCV')"

strCrossBorder = Forms!FOT![Cross Border Transaction Indicator]

Set rst = CurrentDb().OpenRecordset(strSQL)
With rst
    If .RecordCount > 0 Then
       .MoveLast
       If .RecordCount <> 2 Then
          Forms![FOT].Form![strInfo] = vbNullString
       Else
          strCountry2 = .Fields(0)
          .MoveFirst
          strCountry1 = .Fields(0)
          If strCountry1 = strCountry2 And strCrossBorder = "Y" Then
            Forms![FOT].Form![strInfo] = "Flag should be N"
          ElseIf strCountry1 <> strCountry2 And strCrossBorder = "N"
Then
            Forms![FOT].Form![strInfo] = "Flag should be Y"
          Else
            Forms![FOT].Form![strInfo] = vbNullString
          End If
       End If
    Else
       Forms![FOT].Form![strInfo] = vbNullString
    End If
End With
End Sub- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Perfect.
Thanks John!