Append or Update

[Chimp8471]

append or update ???

i have a table called "tbl_Details" it has the following fields

Surname
Forename
EmployeeNo (PK)
EmployeeName

the field EmployeeName is the problem are here for me.

i need the EmployeeName Field to be automatically completed upon the input of Surname and Forename, the field EmployeeName is not a visible field and is used in a search function.

i have set up a simple query to generate the EmployeeName, but i cant am unsure on how to update the Table with the query info.....

any help please

 

[WayneRyan]

chimp,

You really don't have to (or want to) store it
if you can calculate it, but ...

The hidden field should be "bound" to your
table's field. You can use the AfterUpdate
event of the other name fields to:

Me.EmployeeName = Me.LastName & ", " & Me.FirstName

hth,
Wayne

 

[Mile-O]

I wouldn't store the EmployeeName if you already have the Forename and Surname as it is a calculated value (a violation of Third Normal Form [3NF]).

If you want to search for a full employee's name then base your search on a query that calculates the value for you.

 

[Chimp8471]

ok thanks, but the reson i store the EmployeeName is because i found this search facility and i have modified it to suit my database, but part of the lookup in it refers to the following:

SELECT DISTINCTROW [Tbl_EmployeeDetails].[Employee No], [Tbl_EmployeeDetails].EmployeeName FROM [Tbl_EmployeeDetails] WHERE ((([Tbl_EmployeeDetails].EmployeeName) Like "*" & [Forms]![frm Wild Search]![txtSearchString] & "*")) OR ((([Tbl_EmployeeDetails].[Employee No]) Like "*" & [Forms]![frm Wild Search]![txtSearchString] & "*")) ORDER BY [Tbl_EmployeeDetails].EmployeeName;

i am not sure how to modify the above code to use the fields, forename and surname together.

hope this makes sense

 

[Mile-O]

If you have a query that creates the field then you change the SQL above to SELECT Information FROM TheQuery.