Continuous Split Form Update....

[mrmmickle1]

I have a Split Form where I have two dropdowns RPT_LOB and RPT_SUBLOB.



If the RPT_LOB is A then I have options B,C & D for the RPT_SUBLOB.....

If the RPT_LOB is E then I have options F,G & H for the RPT_SUBLOB....

These dropdowns are in the bottom portion of the split form where the table records are visible....

My issue is that when the form is loaded whatever the first SUBLOB dropdown clicked on has for RPT_LOB it will Load those values into the RPT_SUBLOB...so if RPT_LOB = A then I will have values B,C & D in the RPT_SUBLOB dropdown.

However when I go to another RPT_SUBLOB dropdown that has RPT_LOB = E the dropdowns still show B,C & D rather than F,G & H like they are expected too....

I have fixed the issue by using the below code:

Private Sub RPT_SUBLOB_GotFocus()


  Dim strSQL As String
  
  strSQL = "SELECT tblReference.SUBCATEGORY FROM tblReference WHERE (((tblReference.TYPE)='LOB/SUBLOB DROPDOWN') AND"
  strSQL = strSQL & vbCrLf & "((tblReference.CATEGORY)=Forms!frmReviewData!RPT_LOB) AND ((tblReference.ACTIVE)=True)) ORDER BY tblReference.SUBCATEGORY;"
  Me.RPT_SUBLOB.RowSource = strSQL
  
End Sub

My issue isn't that it doesn't work....but that when the user clicks on the RPT_SubLOB dropdown there is a screenflash then the dropdown is viewed then the dropdown is updated....then the user has to click the button again in order to see the updated values...so essentially the user is having to click the RPT_SUBLOB dropdown twice to view the narrowed down list of RPT_SUBLOB... which is pretty annoying.

I was wondering if there is a way to suppress the initial screenflash/ update so the user only clicks the dropdown once to view the narrowed down results...

The RowSource when the form loads is like this but like I said it seems to only update on the first click then it appears to be stored in some kind of cache:

SELECT tblReference.SUBCATEGORY FROM tblReference WHERE (((tblReference.TYPE)='LOB/SUBLOB DROPDOWN') AND ((tblReference.CATEGORY)=Forms!frmReviewData!RPT_LOB) AND ((tblReference.ACTIVE)=True)) ORDER BY tblReference.SUBCATEGORY;

I tried using this to no avail:

Application.Echo False

Any help would be appreciated!

[Bulzie]

I think you need to requery the SUBLOB box in the AfterUpdate Event on RPT_LOB field.
Me.RPT_SUBLOB.Requery

[mrmmickle1]

In the top of the split form I am already doing what you suggest. My issue is with the bottom portion of the split form. However, I think what you just said fixes my concerns....based on the logic


So if the user clicks on the SubLOB that is in the form it may have the incorrect list associated with it....but

The user really shouldn't be editing this unless they edit the LOB first....which makes it more of an order of operations thing. So even though it doesn't work correctly...it might not matter based on the order of operations the user will be using. Still kind of annoying though.

[Bulzie]

Yes I guess they need to go back and select the correct Rpt_LOB to get the correct list in the Sub. You can limit to the list to prevent random input.