Form and subform - timing of event triggering

A form with product details with a one row subform showing the
salesperson assigned on the basis of a franchise . The subform is
linked to the main form using link master/child fields comprising two
fields. The data in the subform appears as soon as the data in the
second of the two fields is populated. This part of the code works
fine.
I have code in the “on exit” event of the second of the two link
fields to test If there is no record in the subform:
If Me!sfrmSalesPersonLink.Form.RecordsetClone.RecordC ount = 0 Then
...... show a dialog box asking if the user wishes to create a new link
between salesperson and product.
Again this part of the code works fine when there is no record in the
subform. The problem is that my dialog box is appearing even if there
is a link already. If I say no the dialog box, then the subform is
populated.
It seems as if the link between form and subform is triggering after
the event code to test if there any link. How do I get round this?

Gordon

Post the code, please. Can't troubleshoot what we can't see.

wrote in message
...
A form with product details with a one row subform showing the
salesperson assigned on the basis of a franchise . The subform is
linked to the main form using link master/child fields comprising two
fields. The data in the subform appears as soon as the data in the
second of the two fields is populated. This part of the code works
fine.
I have code in the “on exit” event of the second of the two link
fields to test If there is no record in the subform:
If Me!sfrmSalesPersonLink.Form.RecordsetClone.RecordC ount = 0 Then
...... show a dialog box asking if the user wishes to create a new link
between salesperson and product.
Again this part of the code works fine when there is no record in the
subform. The problem is that my dialog box is appearing even if there
is a link already. If I say no the dialog box, then the subform is
populated.
It seems as if the link between form and subform is triggering after
the event code to test if there any link. How do I get round this?

Gordon

On Nov 1, 9:45*pm, "Klatuu" wrote:
Post the code, please. Can't troubleshoot what we can't see.

wrote in message

...
A form with product details *with a one row subform showing the
salesperson assigned on the basis of a franchise . The subform is
linked to the main form using link master/child fields comprising two
fields. *The data in the subform appears as soon as the data in the
second of the two fields is populated. This part of the code works
fine.
I have code in the “on exit” event *of the second of the two link
fields to test If there is no record in the subform:
If Me!sfrmSalesPersonLink.Form.RecordsetClone.RecordC ount = 0 Then
..... show a dialog box asking if the user wishes to create a new link
between salesperson and product.
Again this part of the code works fine when there is no record in the
subform. *The problem is that my dialog box is appearing even if there
is a link already. *If I say no the dialog box, then the subform is
populated.
It seems as if the link between form and subform is triggering after
the event code to test if there any link. *How do I get round this?

Gordon

Sorry, trying to keep it brief. To repeat the problem - the On Current
event below seems to be firing after the On Exit event of fldProductID
(see further below). So even when there is a record in the subform
(sfrmIFALink), I get the message asking me if I want to create a new
link to populate the subform.

Private Sub Form_Current()
Dim strwhere As String

strwhere = "[fldOutcode] = '" & Me!fldEnqOutcode & "'"
If Not IsNull(Me!fldEnqOutcode) Then
Me!fldCatchmentAreaID = DLookup("[fldCatchmentAreaID]", "tblOutcodes",
"[fldOutcode]= '" & Me!fldEnqOutcode & "'")
Else: Exit Sub
End If
End Sub


Private Sub fldProductID_Exit(Cancel As Integer)
Dim strTitle As String, intMsgDialog As Integer, intNewEntry As
Integer
On Error GoTo Err_fldProductID_Click
intCatchmentArea = Me!fldCatchmentAreaID
If Me!sfrmIFALink.Form.RecordsetClone.RecordCount = 0 Then
' Display message box asking if user wants to add a new link
strTitle = "Warning"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
strMsg = "There is no IFA assigned to this postcode." & vbCrLf
strMsg = strMsg & " Do you want to assign an IFA?"
intNewEntry = MsgBox(strMsg, vbOKOnly + intMsgDialog)
If intNewEntry = vbNo Then
Exit Sub
Else
DoCmd.OpenForm "frmAssignPostcodes", , , , acFormAdd, ,
intCatchmentArea
End If
End If


Gordon

this line of code:
strwhere = "[fldOutcode] = '" & Me!fldEnqOutcode & "'"
means that fldOutcode will never be null. Iif it were Null before this
line, it will now be = ''

strwhere = "[fldOutcode] = '" & Me!fldEnqOutcode & "'"

The control function will always fire before the current function. The
Current function fires as soon as you navigate to a new record.

If you can describe what it is you want to do, maybe we can help with the
how to.
wrote in message
...
On Nov 1, 9:45 pm, "Klatuu" wrote:
Post the code, please. Can't troubleshoot what we can't see.

wrote in message

...
A form with product details with a one row subform showing the
salesperson assigned on the basis of a franchise . The subform is
linked to the main form using link master/child fields comprising two
fields. The data in the subform appears as soon as the data in the
second of the two fields is populated. This part of the code works
fine.
I have code in the “on exit” event of the second of the two link
fields to test If there is no record in the subform:
If Me!sfrmSalesPersonLink.Form.RecordsetClone.RecordC ount = 0 Then
..... show a dialog box asking if the user wishes to create a new link
between salesperson and product.
Again this part of the code works fine when there is no record in the
subform. The problem is that my dialog box is appearing even if there
is a link already. If I say no the dialog box, then the subform is
populated.
It seems as if the link between form and subform is triggering after
the event code to test if there any link. How do I get round this?

Gordon

Sorry, trying to keep it brief. To repeat the problem - the On Current
event below seems to be firing after the On Exit event of fldProductID
(see further below). So even when there is a record in the subform
(sfrmIFALink), I get the message asking me if I want to create a new
link to populate the subform.

Private Sub Form_Current()
Dim strwhere As String

strwhere = "[fldOutcode] = '" & Me!fldEnqOutcode & "'"
If Not IsNull(Me!fldEnqOutcode) Then
Me!fldCatchmentAreaID = DLookup("[fldCatchmentAreaID]", "tblOutcodes",
"[fldOutcode]= '" & Me!fldEnqOutcode & "'")
Else: Exit Sub
End If
End Sub


Private Sub fldProductID_Exit(Cancel As Integer)
Dim strTitle As String, intMsgDialog As Integer, intNewEntry As
Integer
On Error GoTo Err_fldProductID_Click
intCatchmentArea = Me!fldCatchmentAreaID
If Me!sfrmIFALink.Form.RecordsetClone.RecordCount = 0 Then
' Display message box asking if user wants to add a new link
strTitle = "Warning"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
strMsg = "There is no IFA assigned to this postcode." & vbCrLf
strMsg = strMsg & " Do you want to assign an IFA?"
intNewEntry = MsgBox(strMsg, vbOKOnly + intMsgDialog)
If intNewEntry = vbNo Then
Exit Sub
Else
DoCmd.OpenForm "frmAssignPostcodes", , , , acFormAdd, ,
intCatchmentArea
End If
End If


Gordon

On Nov 2, 1:23*pm, "Klatuu" wrote:
this line of code:
strwhere = "[fldOutcode] = '" & Me!fldEnqOutcode & "'"
means that fldOutcode will never be null. *Iif it were Null before this
line, it will now be = ''

strwhere = "[fldOutcode] = '" & Me!fldEnqOutcode & "'"

The control function will always fire before the current function. *The
Current function fires as soon as you navigate to a new record.

If you can describe what it is you want to do, maybe we can help with the
how wrote in message

...
On Nov 1, 9:45 pm, "Klatuu" wrote:





Post the code, please. Can't troubleshoot what we can't see.

wrote in message

....
A form with product details with a one row subform showing the
salesperson assigned on the basis of a franchise . The subform is
linked to the main form using link master/child fields comprising two
fields. The data in the subform appears as soon as the data in the
second of the two fields is populated. This part of the code works
fine.
I have code in the “on exit” event of the second of the two link
fields to test If there is no record in the subform:
If Me!sfrmSalesPersonLink.Form.RecordsetClone.RecordC ount = 0 Then
..... show a dialog box asking if the user wishes to create a new link
between salesperson and product.
Again this part of the code works fine when there is no record in the
subform. The problem is that my dialog box is appearing even if there
is a link already. If I say no the dialog box, then the subform is
populated.
It seems as if the link between form and subform is triggering after
the event code to test if there any link. How do I get round this?

Gordon

Sorry, trying to keep it brief. To repeat the problem - the On Current
event below seems to be firing after the On Exit event of fldProductID
(see further below). *So even when there is a record in the subform
(sfrmIFALink), I get the message asking me if I want to create a new
link to populate the subform.

Private Sub Form_Current()
Dim strwhere As String

strwhere = "[fldOutcode] = '" & Me!fldEnqOutcode & "'"
If Not IsNull(Me!fldEnqOutcode) Then
Me!fldCatchmentAreaID = DLookup("[fldCatchmentAreaID]", "tblOutcodes",
"[fldOutcode]= '" & Me!fldEnqOutcode & "'")
Else: Exit Sub
End If
End Sub

Private Sub fldProductID_Exit(Cancel As Integer)
Dim strTitle As String, intMsgDialog As Integer, intNewEntry As
Integer
On Error GoTo Err_fldProductID_Click
intCatchmentArea = Me!fldCatchmentAreaID
If Me!sfrmIFALink.Form.RecordsetClone.RecordCount = 0 Then
' Display message box asking if user wants to add a new link
strTitle = "Warning"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
strMsg = "There is no IFA assigned to this postcode." & vbCrLf
strMsg = strMsg & " * * *Do you want to assign an IFA?"
intNewEntry = MsgBox(strMsg, vbOKOnly + intMsgDialog)
If intNewEntry = vbNo Then
Exit Sub
Else
DoCmd.OpenForm "frmAssignPostcodes", , , , acFormAdd, ,
intCatchmentArea
End If
End If

Gordon- Hide quoted text -

- Show quoted text -

Sorry, the strwhere piece of code was a remnant from an earlier failed
coding design attempt. Ignore it.
What am I trying to achieve? The main form records sales enquiries.
Sales enquiries can be for any 1 of 3 products (fldProduct) and can
emanate from any 1 of 1000 plus postcodes (fldEnqOutcode) (each of
which is grouped in one of 125 wider catchment areas,
fldCatchmentAreaID).
Once the details are entered for a new enquiry, the system must
automatically assign it to a salesperson who has responsibility for
the combination of fldProductID and fldCatchmentAreaID. The links
between salespersonID and the fldProductID and fldCatchmentAreaID are
held in a separate linking table.
The fldEnqOutcode is entered first (after update, this populates the
fldCatchmentArea on the same form), then the product field is
entered. Those two fields (fldCatchmentArea and fldProductArea) form
a combined master/child link relationship between the main form and
the subform, which will show the name of the salesperson if a link is
found. If not, then a dialog opens asking if the user wants to
manually assign a salesperson. This latter check takes place in the
On Exit event of fldProductID.

The problem is on only occurring when entering a new enquiry. For
example, I enter the fldEnqOutcode and the fldProductID in a
combination where I know there is a salesperson assigned. When I exit
the fldProductID field, I get the dialog message saying there is no
salesperson assigned, do I want to assign one?, I say no but then the
subform is populated with the salesperson.
Does any of that make sense? I know it sounds complicated – if it was
simple perhaps I would have a better chance of solving it !!