If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Form and subform - timing of event triggering
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 |
#3
|
|||
|
|||
Form and subform - timing of event triggering
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 |
#4
|
|||
|
|||
Form and subform - timing of event triggering
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 |
#5
|
|||
|
|||
Form and subform - timing of event triggering
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 !! |
Thread Tools | |
Display Modes | |
|
|