Tristan G Tristan G - 4 days ago 6
SQL Question

Access - Change fields in table automatically, if a field changes

Lets say I got a table with an id, pre- and lastname. I made them work as comboboxes in the table. Now if I change 1 field (lets say the id) with the combobox I want that all the other stuff is changed too (the pre and lastname). How to achieve that (with a macro or vba, or is it easier)?

Answer

I solved it. I made a sub formular. Then i managed to dropdown the fields I wanted to. Copying the same table for finding the names ids... And then i created an event for after update:

Private Sub PartnerIdServiceWorker_AfterUpdate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SELECT * FROM HTB WHERE PartnerIdServiceWorker = " & PartnerIdServiceWorker)

Do While Not rst.EOF
    Me.AnredeMitarbeiter = rst!AnredeMitarbeiter
    Me.VornameMitarbeiter = rst!VornameMitarbeiter
    Me.NachnameMitarbeiter = rst!NachnameMitarbeiter
    Exit Do
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Now I'm able to change the fields in the table and the other values do also change.

Comments