Change BackColour & Msg Display

[JennyM]

As of two weeks ago it had never occurred to me that building a database wasn't impossible. I've put together a very simple dbase using my own ingenuity and looking up the Office Assistant (sometimes he's useful).
However, I'm stuck - there's 4 days left in my assignment and I wanted to do the following - can somebody help, I appreciate any assistance given. Thankyou.

1) Change BackColour of a text box
I want to install an event procedure that will change the BackColour of three text boxes based on information entered in only one of them.
Eg: 'Date entered in "Archived", triggers a colour change, and for "Box" and "Destruction" as well '

2) Display Message on Exit
Also I want to have a message displayed only when it is recognised that the text box has Null value. This is what I've put together so far and I get a response saying it doesn't know where the procedure ends.
(Text Box "Data Code" can not be Required - I want to be able to set up a Warning to mearly 'encourage'.)

Private Sub Data_Code_Exit (Cancel As Integer)

Beep

If IsNull ([DataCode]) Then

With Assistant.NewBalloon
.Heading = "Wait !"
.Text = "Is it OK to leave Data Code Blank?"
.Show
If NotNull ([DataCode]) Then
Else
Exit Sub
End If
Stop

 

[naygl]

To change the colour of a text box you use the conditional formatting in the format menu. You can only have three different conditions maximum.

I'm no programmer but you should not have an else before the 'end sub' as it will only end the sub if a condition is met. You do not need the second if statement from what I can tell-therefore no 'else' either.

[This message has been edited by naygl (edited 02-19-2002).]

 

[JennyM]

Thanks Naygl! I've attempted the suggestions but with no success.

1) Conditional Formatting (CF) - I've used Office Assistant to find more info, but results do not show 'CF'. Tried the MS Product Support page and an article there talks about setting up two separate controls to overlay one another with different formats (Q193207). But this seems all too tricky. Where abouts do I find this CF? I've looked in Formats Menu as well a TextBox Format. (Using Access 97.)

2) Msg Display - Removed the Else statement and now when form is running throws me back to Code and highlights first line. I've been here before - stuck on code - I know there is supposed to be something after Else : but what?

You know it would make sense to wait until I've done a beginners course before diving head first into this. Essentially it works, but I know it can be done better.

 

[naygl]

You should find something in help under 'conditional'

coding:
If IsNull ([DataCode]) Then

With Assistant.NewBalloon
.Heading = "Wait !"
.Text = "Is it OK to leave Data Code Blank?"
.Show
End If

End Sub

-this should execute the code in the If statement only if the text box is null. If it isn't null the sub won't do anything.

-SORRY I did not read your post thoroughly. Access97 does not have conditional formatting.

[This message has been edited by naygl (edited 02-19-2002).]

 

[Jack Cowley]

Conditional formating does not exist in Access97 as it does in Access2000. This does not mean that you can't change the backcolor of a text box. Here is one way to do it using code in the On Exit event of the DateEntered field:

If Me![DateEntered] = "Archived" Then
Me![DataEntered].BackColor = 255
Me![Box].BackColor = 255
Me![Destruction].BackColor = 255
End if

I have never used the Office Assistant to popup messages but have stuck with the less exciting Message box. Code for that would look like this:

If IsNull ([DataCode]) Then

Dim Response As Integer

Response = MsgBox("Is it OK to leave the DataCode field blank?", vbQuestion + vbYesNo + _
vbDefaultButton1, "Wait!")

If Response = vbYes Then ' (Default)
Exit Sub
Else 'vbNo
Me![AnotherFieldOnForm].SetFocus
Me![DataCode].SetFocus
End If

End if
Good luck!

[This message has been edited by Jack Cowley (edited 02-19-2002).]

 

[JennyM]

I've had some Success! - I tried out Naygl's idea with the msgbox, but was returned with a Compile Error 'End If without block If'. So I tried Jack's idea and it worked!. I think Naygl could be close if I knew where to put in the Else statement.
Hopefully Jack is still around, because I would really like some more help with his suggestion on the BackColor. This is what I have entered, however there is no change in colour. Or is 255 white?

Private Sub Archived_Date_Exit(Cancel As Integer)

If Me![ArchivedDate] = "ArchivedDate" Then
Me![ArchiveDate].BackColor = 255
Me![BoxNo].BackColor = 255
Me![DestructionDate].BackColor = 255
End If

End Sub

 

[Accessable]

As far as I can tell, 255 is red.
I code a little differently, though. I'd probably do this:

Me.txtYourTextControl.BackColor = 255

Anyway, I don't think that matters. My guess is that you are never reaching this code. By that, I mean that your if statement is coming back as a False and kicking out. You can test this by putting this code within your if statement:

msgbox "Hello"

If you see the message at runtime, then your if statement executed with a true and the color should change. Otherwise, the code inside it never ran as it returned a false. If this is the case, then of course the color did not change because the code to do so never ran. Try this and let me know what happens.

Good luck,
-Mark

[This message has been edited by Accessable (edited 02-21-2002).]

 

[JennyM]

Thank you Accessable, I've inserted the msgbox and tested it and "Hello" didn't show. Iv'e tried changing the event to an After Update, instead of Exit and this hasn't worked either. Any advice?

 

[naygl]

I was looking at the code I had written and realised I didn't have an 'end with' at the end of the with statement- before the 'end if'.

Probably doesn't help now. And I still don't see why you need an 'Else'.

Glen.

 

[xcaliber]

Just a thought here.

Your problem may be in the if statement.

If Me![DateEntered] = "Archived" Then

If you do not have the string exactly as shown then it will not enter the if statement.

if I understand this correctly

Eg: 'Date entered in "Archived", triggers a colour change, and for "Box" and "Destruction" as well '

then the if statement would be

If Not Null (me.dateentered) then


But then you would need some code to check and make sure the entry is a proper date.

Well I hope that helps and did not confuse the issue.

sean

 

[Accessable]

Try creating a command button, and putting your code in its On_Click event. Click it and see if the color changes. If the color changes, then you probably don't understand how your events are working (ex: When After Update executes). If the color does not change there's still some problem with your code. If this is the case, post your code (in its latest state) so I can look at it.

[This message has been edited by Accessable (edited 02-26-2002).]