lookup info from one table and insert into another

[dcorleto]

Have table Projects with fields called EmployeeID and ContactName.

Have another table called Contacts with EmployeeID and Name.

Have set up a relationship between the two on Employee ID.

Would like to have the database populate the name field automatically when I input the employeeID in the Projects table.

Do not want to use a query. Is this possible without doing a query?

 

[PHV]

Have a look here:

http://r937.com/relational.html

ie, why storing duplicate Names ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dcorleto]

Contact table is given to me from another source. The Projects Table is made my me, and I need to have the Contacts dumped into it. As a project is created, There is one employee that will be associated with each project. So as I create the fields for a new project, I would like to just select from a dropdown list of employee names and have the employee ID populate automatically, thereby completing the Projects table... hope this makes sense.

 

[vise]

It would be easier to just write an update query where after selecting the employee name or id, it will update that specific table with the missing information.
To get a combo box for either one, employee name or id, go to the table design view. Then go to the lookup tab. Make it a combo box and as row source select the table that you are looking these values up from. You can invoke the query builder by hitting the "..." on the right side of the row source when you are on it. There you can select what table and what you want to appear in that combo box.
You can then create an update query that updates the table you created.

-sin

 

[dcorleto]

That's an interesting approach...just one question - once I create the update query, will it automatically update and when, or do I have run it manually each time I enter new data?

 

[PHV]

Have you consider a ComboBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Using a combobox like PHV suggested you could do something like this:

In the after update of the Combo:


Private Sub Combo1_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

If Not QueryExists("QueryName") Then
Set qdf = db.CreateQueryDef("QueryName")
Else
Set qdf = db.QueryDefs("QueryName")
End If


strSQL = "UPDATE table1 INNER JOIN table2 ON table1.PK = table2.PK SET table1.[S_Name] = table2.[S_Name], table1.F_Name = table2.[F_Name] " & _
"WHERE (((table2.PK)=[forms]![frmUpdate]![combo1]))"

qdf.SQL = strSQL

If Application.SysCmd(acSysCmdGetObjectState, acQuery, "QueryName") = acObjStateOpen Then
DoCmd.Close acQuery, "QueryName"
End If


DoCmd.SetWarnings False
DoCmd.OpenQuery "QueryName"
DoCmd.SetWarnings True


Set qdf = Nothing
Set db = Nothing


End Sub

***********************************************************


And create a module:

Public Function QueryExists(QueryName As String) As Boolean
' Checks for the existence of a query (named as QueryName)
' and returns true if query exists.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
QueryExists = False
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.Name = QueryName Then
QueryExists = True
Exit Function
End If
Next qdf
End Function