Data Row Problem

[OldManRiver]

All,

Attempting something I have not tried before. Have the form shown.

Need to accomplish the following on the subform:

1. Change color, row by row, for different status values,
2. Change value, row by row, from either drop-down or pop-up.

I actually only need to change/manage color and value in the "Status" column.

I can get the right values, on double-click, but when I attempt to set them all the rows set, not the individual one. I know this is needing an index for the row, but since I've not done this before, do not know what index I am looking for. Also I do not know what I should be searching for, so not getting and online help.

URLs to HOWTOs please.

Thanks!

OMR

 

[MarkK]

but when I attempt to set them all the rows set

There is only one row! Check it out in design view. And when you set a property of a control, that property is then set for all instance of that control. Microsoft would say, "This behaviour is by design."

 

[namliam]

There is only one row! Check it out in design view. And when you set a property of a control, that property is then set for all instance of that control. Microsoft would say, "This behaviour is by design."

There is an option that does support this "Conditional formatting" while there is only one row, conditional formatting allows you to set 4 different types of colours / formats... Default format + 3 specific added formats depending on (possibly) different statusus.

 

[DCrake]

Here is a demo that I put together a while ago that may be able to help you

Sample Database

David

 

[OldManRiver]

Conditional Formatting - Not the Answer

All,

I appreciate the inputs on condition formatting, but it is limited to just 3 values, where I have 9.

What I need is for the functionality of conditional formatting in vba so I can fill in the row, setting the color as it sets, then change the column and reset the color, and finally save all this.

Notice I am not using the Nav Bars, etc, so even though the subform is a bound form, this is readonly to the screen and save is handled seperate with a button, as there are 5 tables involved, which are outside of a pre-defined relationship.

Thanks!

OMR

 

[DCrake]

Have you looked at the demo I provided yet?

 

[OldManRiver]

Have you looked at the demo I provided yet?

D,

Yes and I replied, but I see the system somehow did not get my reply posted.

I saw no code in your example and I do everything 100% in vba, so your example does not help me.

Give me an example with vba code please.

OMR

 

[DCrake]

Your request was to change the colour based on the status of the record wasn't it? Well if you look at the status table each status type has a bitmap image of a certain colour and by changing the status it changes the background image to match to colour of the status. Does not need any vba. Why use vba if it is not required.

David

 

[OldManRiver]

Your request was to change the colour based on the status of the record wasn't it? Well if you look at the status table each status type has a bitmap image of a certain colour and by changing the status it changes the background image to match to colour of the status. Does not need any vba. Why use vba if it is not required.

David

D,

Well I am feeding my form with a status table so let me take a run at it, but I still have the problem that when I select a status in one row, all rows set to that status and thus to that color.

Here is the VBA I have so far:

Sub TKT_FColor()
    Dim ColorSTR, FieldVal, ValueSTR
    If Not IsNull(TargetForm![cboxHST]) Then
        FieldVal = DLookup("stat_nam", "tblStatus", "[stat_id]=" & TargetForm![cboxHST])
        ColorSTR = "10079487 11271862 16744703 65408 9058543 " & _
                   "5478172 255 16777088 2658284"
        ValueSTR = "Entered Approved Deficiencies Client_Approved " & _
                   "Invoice_Hold Billed Contested Re-Invoiced Paid"
        rc = Set_FFormat(TargetForm, "cboxHST", FieldVal, ValueSTR, ColorSTR)
    End If
End Sub

Function Set_FFormat(MyForm, MyField, MyFldVal, MyValStr, MyClrStr)
    Dim Ctl As Control, CurClr, CurWPS, N, WrdVal
    For Each Ctl In MyForm.Controls
        If Ctl.Name = MyField Then
            If words(MyFldVal) > 1 Then
                WrdVal = ""
                For N = 1 To words(MyFldVal)
                    WrdVal = WrdVal & "_" & word(MyFldVal, N)
                Next N
                If Left(WrdVal, 1) = "_" Then WrdVal = Mid(WrdVal, 2)
                CurWPS = wordpos(MyValStr, WrdVal)
            Else
                CurWPS = wordpos(MyValStr, MyFldVal)
            End If
            CurClr = word(MyClrStr, CurWPS)
            Ctl.BackColor = CurClr
        End If
    Next
End Function

And I use the attached REXX library add-in module.

Thanks for your input though! See where I get with the status table

OMR

 

[OldManRiver]

Examples

All,

So after following all the links and much more research I was either directed to or found 5 examples of how I should do this, which are in the attached file.

File0 ==> Is one with Bitmap Image and works, but cannot apply to my program as somehow this one gets a value for the combobox field and mine does not. With all my efforts I can not duplicate the ability to get a value in my field, nor is there any detail on how to set the color bitmaps in the database, which is critical.

File1,2 ==> Of no value at all.

File3 ==> Coloring and code are interesting but has no combobox so not sure of any value.

File4 ==> Good code but not Continuous Form so not sure of the value.

Still searching and not finding, so may have to write this one my self. The sites at:

http://msdn.microsoft.com/en-us/library/aa139965(office.10).aspx
http://blogs.msdn.com/frice/archive/2004/06/08/151178.aspx
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_22940240.html

all suggest this code to set up conditional formatting object values:

Sub TKT_DInit()
    Dim objCond As FormatConditions
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Entered"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Approved"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Deficiencies"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Client Approved"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Invoice Hold"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Billed"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Contested"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Re-Invoiced"))
'    Set objCond = TargetForm.[cboxHST].FormatConditions.Add(acExpression, , (TargetForm![cboxHST].Value = "Paid"))
End Sub

But as you see I had to comment them out as their syntax is incorrect and they constantly give errors.

I did find the code:

      Function GetLineNumber(f As Form, KeyName As String, KeyValue)
         Dim rs As Recordset
         Dim CountLines

         On Error GoTo Err_GetLineNumber

         Set rs = f.RecordsetClone

         ' Find the current record.
         Select Case rs.Fields(KeyName).Type
            ' Find using numeric data type key value?
            Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
            DB_DOUBLE, DB_BYTE
               rs.FindFirst "[" & KeyName & "] = " & KeyValue
            ' Find using date data type key value?
            Case DB_DATE
               rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find using text data type key value?
            Case DB_TEXT
               rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
            Case Else
               MsgBox "ERROR: Invalid key field data type!"
               Exit Function
         End Select

         ' Loop backward, counting the lines.
         Do Until rs.BOF
            CountLines = CountLines + 1
            rs.MovePrevious
         Loop

Bye_GetLineNumber:
         ' Return the result.
         GetLineNumber = CountLines

         Exit Function

Err_GetLineNumber:
         CountLines = 0
         Resume Bye_GetLineNumber

      End Function

from File3 interesting as it what I knew is needed to track the ROWindex for each row in the continuous form, but still tryng to figure out the exact application.

I also saw some this forum thread:

http://www.mdbmakers.com/forums/showthread.php?t=20445

discussing this and the one guy, who liked concise code, stripped this down to about 3 lines of active code.

Anyway I now have two forms that need this, so really hunting for it. May have to open Fields on my main form, and assign/save values from their, if I can not get the comboboxes working right. Kinda under pressure as I have to deliver at least one of my forms in the morning.

OMR