I am using access 97, and trying to build form, this form is attached to a table [NOS] that is updated. One of the form fields is called tech and updates [nos].[tech], it pulls its data from [outsidestaff].[employeeName].
Here is the question, I need to auto populate the field [techid] on the form, which will update the table [nos].[techid], the data that needs to go into this field is pulled from [outsidestaff].[employeeID]. I hope someone understands what I am doing.

Welcome to Utter Access!

It sounds like you want to select a employee and put his/her ID into a field in your table. You can do this by creating a combo box based on the Employee table and binding the combo box to the EmployeeID on your form. Now the user can select and Employee from the combo box and their ID will be saved in your table...

hth,
Jack

This is true,
The user of the form, completes the form and selects the employee's name that is assigned to the workorder, from this selection I need the form to update the tech name field and the techid field. I tried using the combo box and this just allows me to select what ever employee number I wish, it does not tie the tech id with the tech name I select.

Are employees and techs the same person or are these different people? If they are the same person then you can update controls on the form from the combo box. If employees are one group of people and tech's another group of people then you will need a second combo box of the tech people.

Jack

they are the same person, but not sure how to update controls, the only reason I need this to work is not really to update the table but when I run a report, I need the po# to combine the po# with the techid of the tech that was assigned to the work order, I am getting stuck, I can get the tech name to show up because it is written to the nos table that the report is attached to, but the techid is in the outsidestaff table as field name (employeeID)

The table should save the Tech (employee) ID not their name. If you create a query using the WorkOrder table and Employee table then you can show any data you like from either of the tables in your report. The two table should be joined on EmployeeID and if that is what you have then a query will be your answer. The ONLY think you want to save in the WorkOrder table that is realated to the employee is the EmployeeID, nothing else.

I hope that you understand my suggestion.

Jack

I did understood, and I fixed it, I used a combo box in the form making its control source tech and making the row source SELECT DISTINCTROW [Outside Staff].EmployeeName, [Outside Staff].EmployeeID FROM [Outside Staff];. This did pull the techid in, now I need to concatenate the po# as so po#-nos# space lot#*techid should look like this (11574-44717 wb117*9000) I tried using standard vb concatenation but I can not get it to work.

I hope that the concatenation is for display purposes only and that you are not saving this number in your table. Post the code that is not working and tell me what error(s) you get from it so I can see what you are doing...

Jack

Yes it is only for display and print purposes, this is the code
=[po] & "-" & [Nos#] & " " & [Lot] & "*" & [techid], it is not an error that I get,in fact in does concatenate them just fine, except for the techid is now the tech name. here is the output
11574-44717 EDN-17*Silvano R and it should read 11574-44717 EDN17*100

It appears that you are saving the tech name in the TechID field rather that the TechID. Be sure that you have the Lookup in the table for that field set as a Text Box and not a Combo Box. When you look at the table where you are getting the TechID for the concatention you should see the TechID, not the name.

hth,
Jack

no the table only has the tech name in it, and I verified the data is the name not the number.

Your combo box with the TechID, etc. should be saving the TechID in the field in the related table, not the name.

tblMyTable
MyTableID (PK)
...some fields...
TechID (FK)


tblEmployee
TechID (PK)
LastName
FirstName

In the example above tblMyTable must store the TechID in the TechID field, not the name.

hth,
Jack

thanks

You are welcome. I hope you have everything working as you want it....

Jack