Conditional Formating

[mykil]

I want to have a Combobox that will automatically filled the "value list" fro a certain "textbox".
If a user will choose " First Year" which in the value list of ComboBoxA it must produce the "Value List" (may be called "Case Value" I'm not sure with correct term used) for TextBoxB so that; if the enter the value "1" in the TextBoxA it will supply the data for TextBoxB = "0"; if i enter the value "2" in the TextBoxA it will supply the data for TextBoxB = "0.6".. But if i choose "Second Year" in the ComboBoxA I want the ComboBoxA to produce the Value List or Case Value I think for TextBoxB; so that if TextBoxA = "1" it must supply the data for TextBoxb= "0"; if TextBoxA = "2", it must supply the data for TextBoxB = "0.9"......

Your help will be much appreciated!!! Thanks in Advance!!!

 

[John Big Booty]

Firstly please refrain from double posting it causes no end of confusion

I'm not entirely sure I follow what you are trying to do; However if I've understood correctly, if the values you wish to display in Text boxes A & B form part of the Row Source of your Combo Box you can use the following to populate your text boxes;

=YourComboName.Column([B][COLOR="Red"]x[/COLOR][/B])

Where x represents the column number (of the combo) that holds the data you wish to display in that text box. Remember that the columns in a Combo (or List box for that matter) are numbered from Zero on up i.e. the first (often hidden) column is column zero etc.

 

[mykil]

Sorry for double posting. I have already deleted the other duplicate of this tread...By the way, I Have attached a sample database(Database1) for you to take a look: This should be more likely the Logic:
ComboBoxA = "First Year" then
If TextBoxA = 1 then TextBoxB = 0
If TextBoxA = 2 then TextBoxB = 0.6

ComboBoxB = "Second Year" then
If TextBoxA = 1 then TextBoxB = 0
If TextBoxA = 2 then TextBoxB = 0.9

I think it has to do with the VBA "Case Select" Condition..

 

[John Big Booty]

I don't currently have access to Access '07 (it is '07 and not '10?). I'll take a look latter when I get home (assuming it's not '10 )

 

[mykil]

..Yes it's an Access '07 Database..Ok. I'll wait...

 

[John Big Booty]

You might get lucky and someone with '07 will jump in before I get a chance

 

[mykil]

Yeah.. Your right... I hope so...

 

[John Big Booty]

OK, I've had a look at your DB, but it's got no data in it

Also I note that you are using a Table Level lookup to populate ComboBoxA, see the link to find out why this is a bad idea.


One quick observation, why store the age of the student, when you have their birthday. The age can be calculated at any time you need to display it on a form or report by using;

=[URL="http://www.techonthenet.com/access/functions/date/datediff.php"]DateDiff[/URL]("yyyy", [BirthDate], [URL="http://www.techonthenet.com/access/functions/date/date.php"]Date()[/URL])

I've tweaked your DB a little, check out Form2, Specifically check out the code behind the following events; the Form's On Current, TextBoxA's Before Update and After Update events.

 

[Brianwarnock]

..

One quick observation, why store the age of the student, when you have their birthday. The age can be calculated at any time you need to display it on a form or report by using;

=[URL="http://www.techonthenet.com/access/functions/date/datediff.php"]DateDiff[/URL]("yyyy", [BirthDate], [URL="http://www.techonthenet.com/access/functions/date/date.php"]Date()[/URL])

.

ER not quite, you have to check to see if there has been a birthday or not and adjust the years.

Datediff("yyyy",[Birthdate],Date()) + (Format(Birthdate,"dd/mm") > Format(Date(), "dd/mm"))

is one way.

Brian

 

[John Big Booty]

Ah yes correct forgot about that, as the age will get overstated, by anything up to eleven moths if the birthday falls in December

 

[mykil]

Sorry for the late reply, I was not around for the past 2 days..

I've tweaked your DB a little, check out Form2, Specifically check out the code behind the following events; the Form's On Current, TextBoxA's Before Update and After Update events.

Nice One. I have followed the codes you've written and edit some field names and it works great!!!Thanks for the help

 

[John Big Booty]

Sorry for the late reply, I was not around for the past 2 days..
Nice One. I have followed the codes you've written and edit some field names and it works great!!!Thanks for the help

Glad I could help.

You will however need to change the code I used to calculate the age, to the one presented by Brianwarnock;

Datediff("yyyy",[Birthdate],Date()) + (Format(Birthdate,"dd/mm") > Format(Date(), "dd/mm"))

 

[mykil]

Glad I could help.

You will however need to change the code I used to calculate the age, to the one presented by Brianwarnock;

Datediff("yyyy",[Birthdate],Date()) + (Format(Birthdate,"dd/mm") > Format(Date(), "dd/mm"))

What if I have 3 textbox for the birthdate; One for Year,Month, and Day.,Is it possible? What would be the expression for this if possible?

 

[John Big Booty]

You can use the DateSerial() function to form those three fields into a fully formed date within your DateDiff().

 

[Brianwarnock]

If the date fields are separate the simplest way is

Year(date())-yeardob+(dateserial(year(date()),monthdob,daydob)>date())

Brian

 

[mykil]

If the date fields are separate the simplest way is

Year(date())-yeardob+(dateserial(year(date()),monthdob,daydob)>date())

So what would be the expression if the fieldname for textboxes: Year, Month and Day were follows: Textbox for Year = BirthDateYear, Month = BirthDateMonth, Day = BirthdateDay.....

 

[John Big Booty]

Try;

Year(Date())-BirthDateYear+(DateSerial(year(Date()),BirthDateMonth,BirthdateDay)> Date())

 

[mykil]

Try;

Year(Date())-BirthDateYear+(DateSerial(year(Date()),BirthDateMonth,BirthdateDay)> Date())

I've copied the expression but its not working..the textbox Age remains null.

 

[John Big Booty]

Did you put an equal sign in front of the expression?

 

[mykil]

Did you put an equal sign in front of the expression?

Yes, I've put an equal sign...