Automatic Updates (1 Viewer)

[cpowell3]

I've created a form that supports issues related to different projects. I'm trying to get an automatic update of corresponding feilds once one field is selected...meaning, if I select one of the project mnemonics, that the project name, project owner would automaticall be filled in based on which mnemonic I selected or the same situation if I were to select the project name first...project mnemonic and project owner would be selected. Can't seem to figure out to make it work. Any help would be greatly appreciated. They are all currently combo boxes.

 

[Travis]

If you are using comboboxes you can add the other data as a hidden column. Then on the after update of that field Just:

Private Sub Field1_AfterUpdate()
Me.[Field2]= Me.[Field1].Column(1)
Me.[Field3]= Me.[Field1].Column(2)
end sub

Do this on each combobox

Otherwise you will need to Use DLookup (my least favorite as it has performance issues) or Open a recordset based on criteria in the field.

E.g.

Set rst = CurrentDb.OpenRecordset("Select Data2, Data3 From tblTable where Data1 = " & [Field1],vbOpenSnapShot)
Me.Field2 = rst!Data2
Me.Field3 = rst!Data3
Set rst = Nothing


Personally if it's not a lot of data I prefer to add it to the comboBox make its column width 0 and use the method above.

 

[cpowell3]

Thanks for your suggestions. With some slight modifications, I was able to get it to work. One question....what does the "Me." represent? Also, since I only want the changes to take place when the mnemonic or project name (only) are updated/changed, how do I limit this from happening with the project owner?....the same person can be a project manager for many projects. Here is what I've put in as the code...

Private Sub Project_Mnemonic2_AfterUpdate()
Project_Name2 = Project_Mnemonic2.Column(1)
Project_Owner2 = Project_Mnemonic2.Column(2)
End Sub

Private Sub Project_Name2_AfterUpdate()
Project_Mnemonic2 = Project_Name2.Column(1)
Project_Owner2 = Project_Name2.Column(2)
End Sub

Private Sub Project_Owner2_AfterUpdate()
Project_Mnemonic2 = Project_Name2.Column(1)
Project_Owner2 = Project_Name2.Column(2)
End Sub