Conditional number format in continuous form

[AccBeg]

Hi everyone,

I'm new here. I got some skills in vba access but not that much.

I try to find an answer to my question but I can't.
I got a continuous subform with several column and I want to change the number format on a specific entry for certain conditions. Just like to change the background color for certain conditions but for the number format.

I successfully change the background color for certain condition (in VBA) but to change the number format, is it possible?
Maybe the answer is simple but I can't find it. Does anyone have a Q.

Thanks!

 

[Galaxiom]

I successfully change the background color for certain condition (in VBA)

Since continuous forms are all repetitions of the same form there is no way to use VBA to individually change the properties of controls on individual records. What you have done would have been with Conditional Formatting, not VBA.

However Conditional Formatting does not allow the Format to be customised.

The only other possibility would be to use the Format property which can format a numeric control differently for positive, negative, zero, and Null. Enter the format string for each variation in that order, separated by semicolons.

 

[AccBeg]

What you have done would have been with Conditional Formatting, not VBA.

Hi Galaxiom,

Thanks for the answer. I have to said yes and no to that because I did it via VB. In my case, it was essential(? or simple) to go through VB since a user of the Access forms that I created can change the reference value used for determining whether the background color will change or not. Here below a part of the code that I use to do that.

TableNameColumn refers to the table where the column names are stored
TableCompareValue refers to the table where the comparison values are stored (for each column of the subform there is a different comparison value. These comparison values can be changed by the user in another form)

Private Sub Form_Current()
    Dim DB As DAO.Recordset
    Dim DB_search As DAO.Recordset    
    Dim strSQL As String
    Dim strSQL_search As String
    Dim VarName As String
    Dim i As Integer

‘….

For i = 1 To 24
        strSQL_search = "SELECT TableNameColumn.NameColumn As Name_Column FROM TableNameColumn WHERE TableNameColumn.Number =" & i 
        Set DB_search = CurrentDb.OpenRecordset(strSQL_search)
        VarName = DB_search![Name_ Column]
        DB_search.Close
        Set DB_search = Nothing


        strSQL = "SELECT Min(TableCompareValue." & VarName & ") AS Name_Column_PickupValue FROM TableCompareValue HAVING TableCompareValue." & VarName & " IS NOT NULL;"
        Set DB = CurrentDb.OpenRecordset(strSQL)

         Controls(VarName).FormatConditions.Delete

       
        If IsNull(DB![Name_ Column _ PickupValue]) Then
             Controls(VarName).FormatConditions.Delete
        Else
                With Controls(VarName).FormatConditions
                    .Add acFieldValue, acGreaterThanOrEqual, DB![Name_ Column _ PickupValue]
                    With .Item(0)
                       .FontBold = True
                       .BackColor = vbRed
                    End With
                End With
        End If
       

        DB.Close
        Set DB = Nothing
Next i

End Sub

The result look like that in the continuous subform:


This works well. But it's not the original question. As you say, it may not be possible to do this.

I managed to do it when the fields are independent in an another form. Here is the code used:

        For i = 1 To 24
        strSQL_search = "SELECT TableNameColumn.NameColumn As Name_Column FROM TableNameColumn WHERE TableNameColumn.Number =" & i 
        Set DB_search = CurrentDb.OpenRecordset(strSQL_search)
        VarName = DB_search![Name_ Column]
        DB_search.Close
        Set DB_search = Nothing

                strName(1) = "Name1_" & VarName
                strName(2) = "Name2_" & VarName
                strName(3) = "Name3_" & VarName
                strName(4) = "Name4_" & VarName
                strName(5) = "Name5_" & VarName
                strName(6) = "Name6_" & VarName
                strName(7) = "Name7_" & VarName

                      For k = 1 To 7

                        strX = strName(k)
                        
                        If Me.Controls(strX).Value >= 0.001 And Me.Controls(strX).Value <= 9999 Then
                                          For j = 1 To 4
                                          If j = 1 Then
                                               If Int((Me.Controls(strX).Value) / 10 ^ j) = 0 Then
                                                   Me.Controls(strX).Value = Format(Me.Controls(strX).Value, "0.000")
                                                   Exit For
                                               End If
                                          End If
                                          If j = 2 Then
                                               If Int((Me.Controls(strX).Value) / 10 ^ j) = 0 Then
                                                  Me.Controls(strX).Value = Format(Me.Controls(strX).Value, "00.00")
                                                  Exit For
                                               End If
                                           End If
                                         If j = 3 Then
                                               If Int((Me.Controls(strX).Value) / 10 ^ j) = 0 Then
                                                  Me.Controls(strX).Value = Format(Me.Controls(strX).Value, "000.0")
                                               Exit For
                                            End If
                                       End If
                                       If j = 4 Then
                                          If Int((Me.Controls(strX).Value) / 10 ^ j) = 0 Then
                                              Me.Controls(strX).Value = Format(Me.Controls(strX).Value, "0000")
                                              Exit For
                                          End If
                                         End If
                                         Next j
                         Else
                            Me.Controls(strX).Value = Format(Me.Controls(strX).Value, "0.00E+0")
                         End If
                        If Me.Controls(strX).Value = 0 Then
                                  Me.Controls(strX).Value = Format(Me.Controls(strX).Value, Standard)
                        End If
                         
                    Next k
         Next i

The result look like that:


But it is independent fields and not a continuous form!

I'll try to do what you said last.

Thanks!

 

[AccBeg]

The only other possibility would be to use the Format property which can format a numeric control differently for positive, negative, zero, and Null. Enter the format string for each variation in that order, separated by semicolons.

Unfortunately, this is not exactly what I'm looking for...

Thanks...

 

[Galaxiom]

Unfortunately, this is not exactly what I'm looking for...

There is one other way but it is quite complex. It uses separate instances of subforms for each record and changes the RecordSource as the main form is scrolled.

http://www.access-programmers.co.uk/forums/showthread.php?p=1282586

It was contributed by one of now departed members, ChrisO.

 

[AccBeg]

Thanks Galaxiom!

As you say, this is a complex solution. However, it is likely that this would work in my case.

Like I said, I am relatively new to Access, and it will take me some time to understand everything that ChrisO realized. There is a lot of code that I have not used up to now.

Anyway, I think my problem is solved even if an easier solution would have been preferable since I do not have much time to realize this, but that's my problem ...

Thank you!

 

[AccBeg]

I found an alternative which is not the best but that helps me to quickly resolve the problem.

I gave the property "general number" in my fields. I adapted the size of my fields. Now I have some fields that display correctly but some are marked by hashes (####).

To solve this problem, I clicked on File, Access Options, Current Database, Truncated Check for Number Fields and I unchecked the box. (I use Access 2010)

Now, some numbers are not entirely visible but are readable.
The numbers too large or too small are automatically converted to scientific notation.

I know it's not the cleanest solution but it will do the job for now.