Changing individual record properties in a form

[kilt]

Hi,

Does anybody know how to do this.

I want to be able to change the forecolour of a text field depending on whether the data in that field matches criteria specified in an if statement.

Does anyone know how to change one text box colour without affecting those that don't match the criteria.

I currently have an if statement that does the checking part and loops through all form records but can't figure out how to change the colour of an individual text box, or if it is even possible.

Thanks in advance

 

[Stretchwickster]

Use something like this:

if {your-condition} then
  ' sets text colour to yellow
  TextBox1.ForeColor = &HFFFF&
  ' sets textbox background colour to red
  TextBox1.BackColor = &HFF&
end if

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[kilt]

My actual problem is that i am first trying to convert the value in the textbox to a date format - when the DateValue function comes across a textbox that is empty, it returns an error. Also some of the text boxes that aren't empty contain text that can not be converted to a date, and i wish to ignore these.

Then i want to go through each record to check whether the date is more than 2 days from the current date, if so changing the text colour, if not doing nothing.

 

[Stretchwickster]

Are you using Access or Excel?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[kilt]

I am using Access, it is basically a booking tracking form, in which i want to be able to highlight dates (in the relevant text field) that are 2 days old.

 

[Stretchwickster]

Ok - here's a start. I've written a function to handle what you said:

Public Function ConvertToDate(AValue As String) As String

On Error GoTo ERR_ConvertToDate

  ConvertToDate = AValue
  If (AValue <> &quot;0&quot;) And (AValue <> &quot;&quot;) Then
    ConvertToDate = DateValue(AValue)
  End If
  
EXIT_ConvertToDate:
  Exit Function
  
ERR_ConvertToDate:
  MsgBox &quot;Unable to convert value to date&quot;

End Function

You can remove the MsgBox line if you don't want the message to occur.

You just need to call this function for each value that you want to check e.g.

TextBox1.Value = ConvertToDate(TextBox1.Value)

I hope this helps!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Stretchwickster]

Actually it can be shortened to this:

Public Function ConvertToDate(AValue As String) As String

On Error Resume Next
  ConvertToDate = AValue
  If AValue <> &quot;&quot; Then ConvertToDate = DateValue(AValue)
  
End Function

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[kilt]

The above code you gave me works to an extent. But the one thing it doesn't do is to give me individual control of each row in the detail section when the form is loaded.

I need to be able to go through each row (on the form load event) and go through how ever many records there are on the form checking each date field, and if appropriate highlighting it. The above code changes the colour of every record, not just individual instances that match the validation criteria i am trying to impose.

 

[Stretchwickster]

I'm afraid this is where my expertise stops as I've never done VBA Access programming. I only work in Excel. So I'm passing the baton on to someone more knowledgeable!

Any takers?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)