Validate subform data using combobox from main form

[hanarina]

Anyone help!!

I'm a student and a really beginner with access and never touch VBA before (and sorry if my english is all messy) anyway, I need help with data validation using VBA. I have a form with one subform, in the main form i have a combobox(Programme name) if a certain programme name is selected the combobox inside the subform which is the subject will only give point to a certain subject else the point will be 0. The Point will be in another column in the subform..

I've been playing with the VB and tried a few coding

Private Sub Form_Current()
Dim db As DAO.Database
Dim t2 As DAO.Recordset

Set db = CurrentDb
Set t2 = db.OpenRecordset("Table2 sub")

If programme.Value = "computer" Then

Do While Not Table2_sub.EOF
If Subject.Value = "Math" Or Me.Table2_sub!Subject.Value = "Geography" Then
Me.Table2_sub!Point.Value = 3
t2!.MoveNext
Loop
End If

End Sub

when i tried to change to the next record an error pop-up and it show the coding saying LOOP without DO
//////////////////////////////////

Private Sub programme_Change()
If programme.Value = "computer" And Me.Table2_sub!Subject.Value = "Math" Or Me.Table2_sub!Subject.Value = "Geography" Then
Me.Table2_sub!Point.Value = 3
End If
End Sub

this code worked but it only worked for one row in the subform...

any help will be appreciated..
Thanks

 

[Pat Hartman]

Rather than hard-coding the rules, create a table so you can look them up. Using a table will simplify changes and additions since you won't need to modify code. You'll just need to add or change a table entry.

 

[hanarina]

Thanks for responding to my post..

Sorry but I don't understand what you mean by creating a table..
(Sorry I'm a Slow learner)

how do I give point only to relevant subject depending on the programme user choose?
​

 

[Pat Hartman]

tblLookupPoint
Programme (primary key)
Point

Math, 3
Geography, 3
Computer, 3

Those are all th values you mentioned. Join to this table on Programme and select the Point value.