Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Unanswered: Data Row Problem

    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!

    DBS4M
    Attached Thumbnails Attached Thumbnails Ticket Approval.png  
    Last edited by dbsupport4me; 10-26-09 at 12:43.

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    1. Conditional formatting - Just use the built in help
    2. Need more information;

    Are you able to post a .zip of the db?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ia conditional formatting available on datasheet forms?

    if so then that should work, if not then I don't think you can achieve what you want within the Access RAD.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    1 Conditional formatting does work on datasheets.
    (Just select Format > Conditional Formatting on Access 2003 from the menu.)

    However, I would normally use a continuous form to display this type of data.

    There are some caveats and sometimes you have to employ a few work-arounds - give it a try first just using the interface.

    2 Might be a problem depending on what is required - pending response...

    ...Is that .zip a possibility?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    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!

    DBS4M

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    If you have >3 items you will have to use a work-around;

    Have an invisible text box with =IIf([yourfield]="Yourvalue","",Null) as the controlsource and set the backcolor to the colour you want in this condition.

    You will have to have 1 text box per colour per control per value.

    I beleive I learned this after reading this...

    Forms: Colors and Continuous forms

    Can't remember but it does work and I have employed this technique on a few occasions.

    As for 'setting' and 'saving' values - if this is a bound form, what is your actual issue here?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    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:

    Extending the Functionality of Conditional Formatting in Access
    frice's WebLog : Adding Additional Conditional Formatting in Access with VBA
    Conditional Formatting using VBA : access, conditional, vba, formatting

    all suggest this code to set up conditional formatting object values:
    Code:
    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:
    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:

    UNconditional surrender ! - MDBMakers.com

    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.

    DBS4M
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    The workaround I suggested will work and requires zero code - did you try it?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379
    Quote Originally Posted by garethdart View Post
    The workaround I suggested will work and requires zero code - did you try it?
    G,

    Could not get it to work. Obviously missing something.

    DBS4M

  10. #10
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Example

    I think I might have posted the wrong link, (Im sure credit should go to The Access Web - Dev Ashish here but cannot find the original link which I had some time ago).

    Anyhow, here is a quick example which includes:

    One table: tblData
    One query: qryData
    Two forms: frmExample - This has 5 conditions
    frmExampleExploded - This makes it more obvious as to what is going on; when you create your text boxes, they have to be stacked on top of each other and 'Sent to back' behind control/s you want to highlight.

    Hope this helps - think my original explanation was poor so apologies for that.
    Attached Files Attached Files
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  11. #11
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    2003 Please

    Quote Originally Posted by garethdart View Post
    I think I might have posted the wrong link, (Im sure credit should go to The Access Web - Dev Ashish here but cannot find the original link which I had some time ago).

    Anyhow, here is a quick example which includes:

    One table: tblData
    One query: qryData
    Two forms: frmExample - This has 5 conditions
    frmExampleExploded - This makes it more obvious as to what is going on; when you create your text boxes, they have to be stacked on top of each other and 'Sent to back' behind control/s you want to highlight.

    Hope this helps - think my original explanation was poor so apologies for that.
    G,

    Your latest is .accdb, which is 2007, and not compatible with 85% of machines, that is why I'm still using 2003 to insure 100% compatibility. Please resend in 2003.

    Thanks!

    DBS4M

  12. #12
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    2002-2003 Version

    Try this version...
    Attached Files Attached Files
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  13. #13
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    PS Select all the text boxes in design view, On the data tab set enabled to 'no' and locked to 'yes' which I forgot to do - not essential but stops background colour of text box overwriting data - have fun.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  14. #14
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Trying

    All/G,

    G, Sorry could never get anything you sent to work!

    Have not had time to work on this till now, so trying. Got this idea searching around. My Code is:
    Code:
    Private Sub cboxCRW_AfterUpdate()
        Set SCRNfields = New clsPRJent
        Set SCRNfields.SourceForm = Me
        Set SCRNfields.TargetForm = Form_sfmPRJasn
        SCRNfields.ASN_CBoxVal
    End Sub
    and
    Code:
    Sub ASN_CBoxVal()
        Dim rs As DAO.Recordset
        Set rs = SourceForm.RecordsetClone
        rs.FindFirst "LabNum='" & SourceForm![cboxCRW] & "'"
        If rs.NoMatch = False Then
            SourceForm.Bookmark = rs.Bookmark
        End If
        rs.Close
    End Sub
    Code always blows on line:
    Code:
        rs.FindFirst "LabNum='" & SourceForm![cboxCRW] & "'"
    I'm not understanding the use of "LabNum" so not getting it right.

    DBS4M

  15. #15
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "could never get anything you sent to work!"

    Can you explain why - I've posted a working .zip example.

    Why does this not do exactly what you want?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •