I recently posted an inquiry regarding Forms and having fields auto-populate based on an entry in a single field. What I had was a table that contained names and relative information on secretaries. That table included who the secretaries' bosses were.

My form had a field where I could select a boss from a drop down list (linked to the afformentioned table). It had several other fields that listed the appropriate secretary and contact information for the selected Boss. I wanted to be able to select the boss from the combo box and have the secretary information auto-populate.

Several alternatives were presented, Relational-joins etc. I was advised that while I could get the information to populate on the form, the data would not be saved in the related table. (second Table btw). I did in fact find this to be true. After selecting the boss, the secretary information would populate on the form (bringing a fuzzy grin to my face) but if I closed the form and checked the table, sure enough, no secretarial info was there.

I solved this in the following manner:
This Section of code populated the fields for me....
Private Sub PW_AfterUpdate()
FSW = DLookup("[FSW]", "[Perm]", "[PW] ='" & Forms![Children]![PW] & "'")
Perm_Office = DLookup("[Office]", "[Perm]", "[PW] ='" & Forms![Children]![PW] & "'")
Perm_Address = DLookup("[Address]", "[Perm]", "[PW] ='" & Forms![Children]![PW] & "'")
Perm_City = DLookup("[City]", "[Perm]", "[PW] ='" & Forms![Children]![PW] & "'")
Perm_Zip = DLookup("[Zip]", "[Perm]", "[PW] ='" & Forms![Children]![PW] & "'")
Perm_Phone = DLookup("[Phone]", "[Perm]", "[PW] ='" & Forms![Children]![PW] & "'")
End Sub

I used this section to store the information in the related table
Private Sub PW_LostFocus()
FSW.SetFocus
Perm_Office.SetFocus
Perm_Address.SetFocus
Perm_City.SetFocus
Perm_Zip.SetFocus
Perm_Phone.SetFocus
End Sub

It seems that the only reason the data does not store in the underlying table is that the fields themselves never getfocus. I stumbled on this when I moved the cursor to one of the fields during my trial. Upon checking the table, I noticed that the field where I had placed the cursor, contained data!


Anyway, by having the program flash the focus through the points, it "perm'd" the information so that it would appear in the table.

I know very little about the workings of Access but I have a knack for finding alternative solutions.....


Jason