How to make text change in a combox box (1 Viewer)

[Slim Shamble]

Hi

I am new to Ms access. I have a form which is use to input student data for
assessment report. i have a text box and a combo box namely total and status
respectively. the status has its own text in it as Pass & fail. I want my combo box
value to change to pass if value in total is > 45 and fail if value in total is < 45.

I hope is understood. Kind respond will be appreciated.

 

[sneuberg]

if the field is called Total then try

=IIf([Total] > 45, "Pass", "Fail")

in the control source, but that will give you Fail for total <= 45.

 

[Slim Shamble]

Didn't work. When clicked it pops up "Compile error: Syntax error "

Any more idea?

 

[sneuberg]

Putting that code in a control source shouldn't create a "Compile error: Syntax error ". Normally you would see #Name? in the textbox if the field was incorrect. I guess I don't understand what you are doing or are trying to do.

Please post a screen shot of the form in design view and describe what you want to happen.

 

[bob fitz]

What is the Row Source Property of the combo box

 

[Slim Shamble]

The raw source is "Pass", "Fail"

 

[Slim Shamble]

In the results portal, I choose a subject and enter the student value for test and exam. The Total field will calculate the total score the student get for the specific subject. Now in the status combo box, the row source is "Pass", "Fail". I need the status combo box to choose "pass" if the student total score for the specific subject is greater than 45 and "Fail" if less than 45.

Hope is understood guyz?

 

[sneuberg]

If the combo box has a single column equal to either "pass" or "fail" you should be able to set it in the after update of something that fires when the total is calculated, perhaps the total text box. If a student get a pass for a score greater or equal to 45 and a fail for 44 or less the code in the afterupdate would be something like.

If Me.NameOfTextBoxWithTotal > 44 Then
    Me.TheNameOfTheComb = "Pass"
Else
    Me.TheNameOfTheComb = "Fail"
End If

If you upload your database I'll tailor this to your situation.

 

[sneuberg]

I need the status combo box to choose "pass" if the student total score for the specific subject is greater than 45 and "Fail" if less than 45.

What does the student get if the score is equal to 45?

 

[Slim Shamble]

Sneuberg it will still be "pass". Let me try your new code.

 

[Slim Shamble]

The code you gave didnt work. No error this time around

Take a look at the Student DB. I hope this is more easier.

Additional, Can you help me on how to set up position on student terminal report. Like 1st, 2nd, 3rd, 4th ................. and so on based on student average.

 

[jdraw]

You did not include your back end database, so readers can not really use your attachment.

 

[sneuberg]

Please try it in the Total_Score_Label_AfterUpdate. It would look like:

Private Sub Total_Score_Label_AfterUpdate()

If Me.Total_Score_Label > 44 Then
    Me.Status_Label = "Pass"
Else
    Me.Status_Label = "Fail"
End If

End Sub

And would you upload the database with the linked tables Result System and Students so that I can test this this and work on the student terminal report.

 

[Slim Shamble]

I really appreciate what you are doing for me. But the database wont upload because the file size is large. Can i send it through mail?

 

[Slim Shamble]

D code didn't work. Can you drop your email so i can forward the DB to you.

 

[sneuberg]

D code didn't work. Can you drop your email so i can forward the DB to you.

I sent you a PM with my email about ten minutes ago. I'll send it again just in case.

 

[sneuberg]

If you do a compact and repair and then put it in a zip file it might be small enough to upload. If it's still to big then you could make a copy and delete most of the records. If you have a parent child relationship just enable cascaded deletes and delete records from the parent. You can delete a lot of records fast by scrolling down to say record 20, shift click on the record and then scroll the the end and shift click on the last. Then hit delete.

Or I guess empty tables would be ok too.

 

[Slim Shamble]

Ok. i will do just that. Thanks

 

[sneuberg]

Got the database and the reason why this wasn't working in the afterupdate of the Total textbox is because that field is calculated which doesn't fire an afterupdate event. You can get this to work by putting the code in both the Label11 (weird* name for a combo) AfterUpdate and Status AfterUpdate as shown below.

Private Sub Label11_AfterUpdate()

If Me.Total_Score_Label > 44 Then
    Me.Status_Label = "Pass"
Else
    Me.Status_Label = "Fail"
End If

End Sub

Private Sub Status_AfterUpdate()

If Me.Total_Score_Label > 44 Then
    Me.Status_Label = "Pass"
Else
    Me.Status_Label = "Fail"
End If

End Sub

This will set the Status Label Combo whenever either the Test and Exam is entered. If this is not acceptable and you don't want that set until both the Test and Exam field are entered then I suggest you remove the default value of 0 from these fields so that they are null when no score is entered. With that you can add the follow code before the code in the above after updates so that the Status_Label is not change if either doesn't have a value.

If IsNull(Me.Label11) Or IsNull(Me.Status) Then
    Exit Sub
End If

*PS: I take it back. Label11 is not only weird for a combo box but so much like a label name that I'd say you had better change it. But do that before adding the code.

 

[Slim Shamble]

Thanks alot. Its worked just the way i expected. I even modify the same code for another form and its worked perfectly.

Really appreciate your effort and time Sneuberg.. Oh i forgot i have changed the weird label name.

i love this site. Is cool