CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    19

    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

  2. #2
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    651

    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...

  3. #3
    Join Date
    Apr 2005
    Posts
    19

    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.

  4. #4
    Join Date
    Jul 2003
    Location
    Florida
    Posts
    651

    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
  •  





Click Here to Expand Forum to Full Width

Featured