Conditional formatting with like

[vbaInet]

Good but what I wanted to see was the form in Design View.

 

[guinness]

Thanks for sticking with this.

Screen attached

 

[vbaInet]

Wow! How many textboxes do you have there? Did you create a textbox, one for almost every day up until the end of 2014? I'm guessing you are looking to do the same thing next year as well?

To be honest with you you're using the wrong tool for this. You should be doing this in Excel, not Access. Have you considered this option?

 

[guinness]

Yep, there's a text box for every working day of the year. There's also another form that displays >Date ()-30 and <Date () +320, and then there will be one for 2015, 2016 and 2017.

I can very quickly create a similar sort of Gantt chart in excel but I want to be able to apply all sorts of rules and manipulate data. In my experience the db may prove difficult to build but is very reliable for years after where as spread sheets fall down.

I'm certain that the conditional formatting wizard is simply applying some sort of vba code so I suppose I'm back to my initial enquiry. How to do you create vba code to apply conditional formatting to the whole form?

I really do appreciate your taking the time to look and hope I haven't frustrated you too much in the process.

Cheers

Guinness

 

[vbaInet]

I'm certain that the conditional formatting wizard is simply applying some sort of vba code so I suppose I'm back to my initial enquiry. How to do you create vba code to apply conditional formatting to the whole form?

And I know how to do it in VBA but that's not the point I'm driving at. Access was mainly built for data manipulation, the presentation of data comes afterwards. A database is mainly used for data that grows downwards which is where Access comes into play, whereas data that grows and is presented horizontally (which is your case) should be handled in Excel. With the way you're going you'll end up needing Excel functions and find that you can't do the same in Access (without significant amount of coding). If this was Excel you'll simply highlight the entire row and apply your conditional format.

I can very quickly create a similar sort of Gantt chart in excel but I want to be able to apply all sorts of rules and manipulate data. In my experience the db may prove difficult to build but is very reliable for years after where as spread sheets fall down.

In your case your goal should be to save and manipulate data in Access but you present it in Excel. Data can be easily exchanged between both platforms with the click of a button. Excel is as solid as Access if they're used for the right purposes.

In any case, if you still want to go ahead with your current way of working, do you know how to write code? I can give you some aircode which you need to manipulate to suit your needs.

 

[guinness]

Thanks vbaInet

I'm not great with code but am learning.

I did consider moving between excel and access, I even considered trying to do something with project. All the calculations will be done using queries. However there must also be a timeline that can be quickly viewed. This timeline will be the only instance where information is viewed across the page.

If you did have some code that I could manipulate I would be extremely grateful.

Thanks again

Guinness

 

[vbaInet]

Here are the steps:

1. Cycle through the controls on the form using the form's Controls collection and identify which controls:
i. are of type Textbox - you can use the Control's ControlType method or TypeName() function
ii. have a name that contains backslash "/" - you can use the Instr() function for this or since the textbox name is a date use the IsDate() function
2. For each control identified in 1, use the FormatConditions method to add the necessary conditions. Here's a snippet:

With [COLOR="Blue"]TextboxName[/COLOR].FormatConditions
    .Delete
    .Add acExpression,, "[COLOR="blue"]Enter your expression here[/COLOR]"
End With

Give number 1 a bash first and we'll take it from there.

 

[guinness]

Sorry but you lost me at 'Cycle through the forms controls'

I have adapted a number of pieces of code in the past replacing field names etc but have no idea how to tell vba to find similar fields or how to specify the same code for all fields. I'm quite willing to go away and do some research into it if you can point me in the right direction.

Cheers
Guinness

 

[vbaInet]

Don't worry, I'll start you of:

dim ctl as Control

for each ctl in me.controls
    
next

That will cycle through all the controls on your form. ctl will be the variable that's pointing to the current control in the loop. You can use that to find the name of the control. See how you get on.

 

[guinness]

Still at a loss. Sorry

Would that look like this?

Private Sub Form_Current()
Dim ctl As Control
For Each ctl In Me.Controls

Next
End Sub

 

[vbaInet]

The reason why I'm doing it this way is because you'll need to understand the entire code since you'll be needing this functionality every year.

How do you get the name of a textbox through VBA? Do you know that?

 

[guinness]

I'm really sorry vbaInet but with this one you'll really need to treat me as a complete novice.

I totally understand if it's too much trouble because with code you're dealing with a rank amateur.

I'd love to understand but really don't know where to begin.

So I start with the form. I think I add code to the 'On Current event'. Then what?

Please feel free to say that it's too complicated to explain. If you know a good resource that could start me off I'd be happy to do some research myself.

 

[vbaInet]

Ok, here's some code from Microsoft's KB:

http://support.microsoft.com/kb/304104

More complicated than I would have explained but give it a go.

 

[guinness]

Problem is that the field in question contains the "/" which vba wont accept

So I can't write: AddFormats Me.02/01/2014_ID, Me

I tried:

AddFormats Me.[02/01/2014], Me

But it crashed

I'm losing the will to live

Thanks for your help though. It's not your fault that I can't figure it out.

Cheers

Guinness

 

[vbaInet]

Take it step by step.

Remember in the steps I mentioned it should qualify two criteria before you even think of adding formatting:
1. It must be a textbox
2. The name of the textbox must be a date

So if we look at the line of code in question, you need to do this:

ElseIf ctl.ControlType = acTextBox And IsDate(clt.Name) = True Then

You don't reference the control name, VBA is doing that. It's a loop that's cycling through all the textboxes, labels, subforms, combo boxes etc on your form.

 

[guinness]

So what I have is:

Function AddFormats(ctlSource As Control, frm As Form) As Integer
Dim ctl As Control
Dim fcdSource As FormatCondition
Dim fcdDestination As FormatCondition
Dim varOperator As Variant
Dim varType As Variant
Dim varExpression1 As Variant
Dim varExpression2 As Variant
Dim intConditionCount As Integer
Dim intCount As Integer

intConditionCount = ctlSource.FormatConditions.Count

For Each ctl In frm.Controls
If ctl.Name = ctlSource.Name Then
' This is the source. Don't apply formatting.
ElseIf ctl.ControlType = acTextBox And IsDate(clt.Name) = True Then
intCount = 0

' Bulk remove all current FormatConditions
ctl.FormatConditions.Delete

Do Until intCount = intConditionCount
Set fcdSource = ctlSource.FormatConditions.Item(intCount)

varOperator = fcdSource.Operator
varType = fcdSource.Type
varExpression1 = fcdSource.Expression1
varExpression2 = fcdSource.Expression2

' Add the FormatCondition
ctl.FormatConditions.Add varType, varOperator, varExpression1, varExpression2

' Reference the FormatCondition to apply formatting.
' Note: The FormatCondition cannot be referenced
' in this manner until it exists.
Set fcdDestination = ctl.FormatConditions.Item(intCount)

With fcdDestination
.Backcolor = fcdSource.Backcolor
.FontBold = fcdSource.FontBold
.FontItalic = fcdSource.FontItalic
.FontUnderline = fcdSource.FontUnderline
.ForeColor = fcdSource.ForeColor
End With

' Move to the next FormatCondition
intCount = intCount + 1

Loop
End If
Next ctl

' Cleanup
AddFormats = intConditionCount
MsgBox "There were " & AddFormats & " Conditional Format(s) applied to all text and combo boxes except the source."
Set ctl = Nothing
Set fcdSource = Nothing
Set fcdDestination = Nothing
Set varOperator = Nothing
Set varType = Nothing
Set varExpression1 = Nothing
Set varExpression2 = Nothing
intConditionCount = 0
intCount = 0
End Function

Now what?

You must hate me by now. I will beat this

Thanks Guinness

 

[guinness]

Oh I added an expression 3 and expression 4:

Dim ctl As Control
Dim fcdSource As FormatCondition
Dim fcdDestination As FormatCondition
Dim varOperator As Variant
Dim varType As Variant
Dim varExpression1 As Variant
Dim varExpression2 As Variant
Dim varExpression3 As Variant
Dim varExpression4 As Variant
Dim intConditionCount As Integer
Dim intCount As Integer

 

[vbaInet]

Looks like I missed your post and didn't get to reply.

I suppose the code in the link I sent is too complex so here's one I wrote:

    Dim ctl As Control
    
    ' ctl is a control object. A control may be a textbox (acTextbox), listbox (acListbox)... etc
    ' Delete - delete all the conditions first
    ' Add - Add the conditions. Chr(34) simply wraps the word in quotes as you would when you write the condition
    ' Item(0) - take the newly added condition and set the formatting properties. 0 is first, 1 is second... etc
    
    For Each ctl In Me
        If ctl.ControlType = acTextBox And IsDate(ctl.Name) Then
            With ctl.FormatConditions
                .Delete
                .Add acExpression, , "[" & ctl.Name & "] Like " & Chr(34) & "*design*" & Chr(34) & _
                                     " Or " & _
                                     "[" & ctl.Name & "] Like " & Chr(34) & "*assessment*" & Chr(34)
                .Item(0).BackColor = vbYellow
            End With
        End If
    Next

Put that in the Load event of your form. You don't need to change anything so test it out first, make sure it's working before you add more conditions, and if you do add conditions, use the ".Add" and ".Item".

 

[guinness]

Hi vbaInet

Sorry I was away for a week and didn't have access to the database. I'll try your code now and let you know. Thanks once again for all your help and sorry for not getting back to you.

Guinness

 

[vbaInet]

I just thought you drank too much Guinness and forgot about your thread

Will wait to hear back!