none
Conditional Formatting Not working after 2nd change on continuous forms with a recordset assigned to it. RRS feed

  • Question

  • I have created a continuous form whereby I assign a recordset to it after compiling certain results.  I have a conditional format rule where the field if lower than the entered variable a field changes colour.  Now, when the data first runs it works perfect but when I change the variable, for some reason values like 100 and over get the colour change when they have nothing to do with the variable amount.

    i.e. my fields are based on days so I have a number of day results i.e. 30,15,90,65,70,105,200,133

    I set the conditional format of anything less than the default variable of 30 which will highlight 15, so for the first run it correctly highlights 15. However, if I change this to say 25 it will highlight values like 105 and 133, and if I say changed this to 30 again it will then highlight 105, 133 and 200.  

    I only have one condition on that field where the value is less than the entered variable but just doesn't seem to work.  I have tried a refresh of the recordset after the change but doesn't work.  Is this problem because it is a continuous form and I use a recordset rather than a query as such?

    Wednesday, August 12, 2015 4:31 AM

All replies

  • Hi. Sorry but I am having a hard time following what you're doing. What do you mean by using a recordset rather than a query? How exactly are you changing the value for the conditional formatting? If you're using code, can you please post it? Thanks.
    Wednesday, August 12, 2015 2:39 PM
  • Hi gunner,

    I am trying to reproduce the issue by set the recordset in the load event of form, however failed. The condition formating always works well for me.

    Here is test code:

    Private Sub Form_Load()
    
       Set rstSales = New ADODB.Recordset
        rstSales.CursorLocation = adUseClient
        rstSales.Open "Select * From sales", _
             CurrentProject.Connection, adOpenKeyset, adLockOptimistic
     Set Me.Recordset = rstSales
    End Sub
    Condition formating:

    I suggest that you try to compact and repair the database to see whether it is helpful. If the issue still exits, you can upload a demo database to OneDrive to help us to reproduce this issue.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 13, 2015 5:01 AM
    Moderator
  • Hello,

    What I mean is I have a form that when opened it looks at a recordset that I have created through ADODB (creating a table).  I will then assign that recordset to the form i.e. set forms![a].form.recordset = rec1

    The form is continuous.  On the top there is a text box where the user can enter the day value so that the conditional formatting I have placed on the field will highlight accordingly.  The form opens on the default value of 30 and the highlights work perfectly fine.  As soon as the value is changed, the conditional formatting on the field goes a bit weird as mentioned.

    The conditional formatting for the field, I have used the the tool in the design menu, no code just something like [fieldname] <=[day value]

    I just don't know what is going on?  Maybe it's because I'm using a recordset?  Really not sure at this moment.  What I will try to do is create a temporary table and point the form to that table and then change the value and see what happens. But ideally, even if it works, I would still prefer to have the recordset that I am using now.

    Thanks..

    Monday, August 17, 2015 7:28 AM
  • Hi gunner_hk,

    >>I just don't know what is going on?  Maybe it's because I'm using a recordset?  <<

    No. Based on the test, it works well for me. Would you mind shareing a demo database with us to help to reproduce this issue?

    You can upload it from OneDrive and please remove the sensitive information before you uploading.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 18, 2015 9:49 AM
    Moderator
  • I have created a continuous form whereby I assign a recordset to it after compiling certain results.  I have a conditional format rule where the field if lower than the entered variable a field changes colour.  Now, when the data first runs it works perfect but when I change the variable, for some reason values like 100 and over get the colour change when they have nothing to do with the variable amount.

    Hi gunner,

    A different approach.

    I never use conditional formatting to highlight a field in a record, and then "manually" search the highlighted records. Instead I construct an sql-string dynamically (or change the filter property of the form), and display only the records that fulfill the where conditions. Eventually you can also use some sorting, to mimic some kind of importance of the displayed records.

    For me this give s far more focused view of the question to want to be answered.

    Imb.

    Tuesday, August 18, 2015 12:49 PM