Display message if specific query returns a value

  • Thread starter Thread starter Ixtreme
  • Start date Start date
I

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''"
 
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
 
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
 
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
 
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:
Click to expand...
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")
Click to expand...
However, if I run the code below, I get an error: Too few parameters.
Expected 1.
Click to expand...
Public Sub fCheckCrossBorder(strTransactionID)
Click to expand...
Dim rst As DAO.Recordset
Dim strSQL As String
Click to expand...
Dim strCrossBorder As String
Click to expand...
Dim strCountry1 As String, strCountry2 As String
Click to expand...
strSQL = "SELECT Country FROM trxn_leg" & _
          " WHERE [Transaction Reference Identifier] = " &
strTransactionID & _
          " AND [Party Role] in ('SEND','RCV')"
Click to expand...
strCrossBorder = Forms!FOT![Cross Border Transaction Indicator]
Click to expand...
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 -
Click to expand...

- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...

Perfect.
Thanks John!
 
Back
Top