Changing the value of a combo box using Case or If statements

[Austin]

I've done quite a bit of research and studying of MSDN but I need some more help.

I have a combo boxes with letters, each letter eds to have a number value associated, but this number changes depending on if a checkbox is checked.

Simple enough right?

I've tried cases, but they are way over my head, and my if statements aren't working either.

Here is an example of what I have.



Private Sub cboGrade_AfterUpdate()
If chkGrade.Checked = True Then
If Me.cboGrade = "A" Then
MsgBox "Test"
End If
End If
End Sub

[ibgreat]

When the form loads, do you want the combo to default to a set of values and only change when the check box is checked? Based on your question, it sounds like there are two option and one would be the default. Would an option button (or cascading combo box) work better, so no values are loaded until an option is selected. In either case you should be including the code in the requery for that event (e.g., chkgrade AfterUpdate) not as part of the cboGrade AfterUpdate.

From there I am not sure what your are doing with the values you want to associate with the grade. It sounds like your combo box shows the letter grades ok. Are you only trying to display them in a msgbox or will they be stored somewhere.

Please provide a bit more detail about what you are trying to accomplish, why, and possibly your table structure.

[Austin]

Let me explain what I've decided to do and why and what you think of this idea.

It's for an advising center and they school has just switched to a new grading scale, well we can't lose the old grading scale, so whenever the checkbox is checked it needs to use the new grading scale (each letter grade has a different value associated with it).

I was going to use the checkbox to set the rowsource to a table/query for the grade combobox.

I setup a table with the following fields

Letter
NewScale
OldScale

So


Letter NewScale OldScale
A 4.0 4.0
A- 3.7
B+ 3.3 3.5
B 3.0 3.0
B-

etc.

So if the check box is checked, then the combo box rowsource can be changed using vba

Me.cboGrade.Rowsource = "Select Letter, NewScale From tblGradeScale"

then when the form is STORED it should store the associated scale grade into the courselines table.

Does this make any sense at all? I'm going to go ahead and try this method and if anyone here thinks it's a terrible way to do it then that is fine, let me know what you think.

[ibgreat]

It doesn't make sense to try to have your combo box differentiate the values (at least based on what I think you are trying to do).

Setting up a table like your stating is a good start. If you add a GradeID as a primary key (PK) to your table the PK would be stored in (let's say) a Student Grades table (see below). I would only have your combo box display the letter grade for selection. It could display the other values if they were needed for reference. Then when you need to display or calculate the overall GPA you would set the value to be used by comparing the date the grade was assigned and the date the that the grading system was changed through an if...then statement.

Let's call you current table tblGrades. I am assuming you have a table where student grades will be stored that looks something like this:

tblStudentGrades
StudGradeID (PK)
StudID (Foreign Key from a tblStudents)
GradeID (Foreign Key from tblGrades)
ClassID (Foreing Key from tblClasses)
GradeDate (date)