Home

Search this site:
 

Horshack’s
very
own site

 

This is Bavaria, Germany!
You will find links to Perl,
bootdisks for system administration,
a lot of Linux,
Windows administration
and some personal belongings.
MS Access, lookup for a record when data changed in a form

Lookup for a record when data changed in a form

User puts data into a form and other fields in the form shall change


sub number_of_adults_AfterUpdate()
    Dim sql As String
    Dim db As Database
    Dim rs As Recordset
    
    ' Find out which amount of money the club gets
    ' for having these people hosted
    ' This is different every year and has to be looked up 
    ' in a table 
    Set db = CurrentDb
    sql = "select money_for_every_adult" _
        & " from money_guide_line" _
        & " where year =""" & Me.payoff_year & """"
    Set rs = db.OpenRecordset(sql)
    rs.MoveFirst
    ' Update a field in the current form here:
    Me.money_for_all_adults_to_get = Me.number_of_adults * rs.Fields("money_for_every_adult")
    rs.Close
    '
    Call update_gros_sum
End Sub
And after this lookup don't forget to update the gros_sum in the record which is the sum of all partial money the club gets:
Private Sub update_gros_sum()
    Me.gros_sum = Me.money_for_all_adults_to_get _
       + Me.money_for_all_adolescents_to_get _
       + Me.money_for_pure_existence
End Sub

Simple Lookup for values in a table

dim Food as String
me.food_clear = DLookup("[food_cleartext]", "foodtable", "[food_id] = 12")
Impressum