-
April 19th, 2005, 05:31 PM
#1
Updating table with data from another table...(combobox)
I have two tables:
JOB(JobID, Description, EmployeeID)
EMPLOYEE(EmployeeID, Name, ...)
The two tables are linked via EmployeeID(one-to-many: an employee can have several jobs)
I want to update the table JOB. Therefore, the user can choose the name(!) of an employee who will be working on the job. For this I use a combobox which uses the employees from table EMPLOYEE.
But only the three fields in table JOB needs to be updated, EMPLOYEE must stay the same.
How does it work to fill in the right EmployeeID in table JOB when the user chooses a name from the combobox?
Here's what I had in mind but it doesn't fully seem to work:
Code:
Private Sub CmdSaveJob_Click()
str = CboEmployee
stra = Trim(cn)
AdoEmployee.Recordset.Find "name like '*" & stra & "*' "
Job.Recordset.Fields("JobID") = Txt_JobID
Job.Recordset.Fields("description") = Txt_Description
Job.Recordset.Fields("EmployeeID") = AdoEmployee.Recordset.Fields("EmployeeID")
Job.Recordset.Update
End Sub
-
April 19th, 2005, 05:40 PM
#2
Re: Updating table with data from another table...(combobox)
If you fill your Employee Combobox from the database, you can use the Combox.ItemData to store the EmployeeID. Then pull it back out when you need to save the data to the Job Table.
Like this:
Code:
'Code here to query Database for Employee Name and ID
'Replace my hardcoded values with your database values
Combobox.AddItem "EmployeeName1"
Combobox.ItemData(Combobox.NewIndex) = 1 '<-- This is the Employee Number from the database
Combobox.AddItem "EmployeeName2"
Combobox.ItemData(Combobox.NewIndex) = 2
Combobox.AddItem "EmployeeName3"
Combobox.ItemData(Combobox.NewIndex) = 3
'Use this code to Save the Job
Job.Recordset.Fields("JobID") = Txt_JobID
Job.Recordset.Fields("description") = Txt_Description
Job.Recordset.Fields("EmployeeID") = Combobox.ItemData(Combobox.ListIndex)
Job.Recordset.Update
I'd rather be wakeboarding...
-
April 19th, 2005, 06:31 PM
#3
Re: Updating table with data from another table...(combobox)
Hey thanks! This works.
The only problem is that I won't be able to add employee sice I'd heve to add them in the code (I think)
Is there any way to solve this with a datacombo or so?
I've read something about itemdata and listfield and so but it wasn't properly explained.
-
April 20th, 2005, 08:20 AM
#4
Re: Updating table with data from another table...(combobox)
When you want to add a new employee, update the Employee table first with the new data, then reload the combobox from the database. (Don't forget to clear the combobox before you load it again! I always forget to clear it... )
Similar to this:
Code:
Private Sub cmdNewEmployee_Click()
'Update Employee Table with new employee info
Combobox.Clear
'Do a Select on the Employee Table
Do Until Recordset.EOF
Combobox.AddItem Recordset!EmployeeName
Combobox.ItemData(Combobox.NewIndex) = Recordset!EmployeeID
Recordset.MoveNext
Loop
End Sub
I don't know how to use datacombos, only regular comboboxes.
I'd rather be wakeboarding...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|