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

Ixtreme wrote:
> 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''"
>

On 23 sep, 14:41, John Spencer <JSPEN...@Hilltop.umbc> 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

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 wrote:
> On 23 sep, 14:41, John Spencer <JSPEN...@Hilltop.umbc> 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
>

On 23 sep, 20:11, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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 wrote:
> > On 23 sep, 14:41, John Spencer <JSPEN...@Hilltop.umbc> 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!