Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    7

    Unanswered: Grab data froma different table based on combo box

    I thought this would be easy, but apparantly not (for me)

    I have two tables
    Stafftbl
    StaffID
    Name
    Mon Hours
    Tue Hours
    Wed Hours
    etc

    Timecard
    StaffID
    MonHours
    TuesHours
    etc

    The idea being that the staff table contains the standard hours a worker
    works each week, so one record for each staff member.

    The timecard table then is a record of the number of hours worked every week - which varies.

    When I open the timecard form I would like to be able to select a staff
    member from a combo box (lookup from staff table) and then populate the forum with the standard data from the stafftable, then make alterations to store the record. So for example I selct Mr Taylor. The form then pulls out the standard hours worked each day (say 8) I can then modify Monday's hours to 9, and Tuesdays to 7 then store the whole record

    But i am completely baffled as to how to do this. I thought abut a combo box but that only finds the record based on the current table, not a different table

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The rowsource of a combobox can be based on any table or query, not just on the RecordSource of the form where it resides.

    You can populate your combo from a query based on [Stafftbl] and containing as many columns as they are fields in this table that you need to fill in the form.

    You can address the different columns of the combo using the cbo.Column(x) syntax, then (in the AfterUpdate event procedure of the combo):
    Code:
    Me.<FieldX>.Value = Me.<Combo>.Columns(2)    ' etc...
    Another solution would consist in opening a RecorSet based on the value of the combo to populate the form:
    Code:
    Dim strSQL as String
    Dim rst as DAO.RecordSet
    strSQL = "SELECT <Column1>, <Column2>, etc. FROM Stafftbl WHERE StaffID = " & Me.<Combo>.Value
    Set rst = Currentdb.Openrecordset(strSQL, dbOpenSnapshot)
    With rst
        If .EOF = False Then
            Me.<Field1>.Value = rst!<Column1>
            ' etc...
        Else
            ' No match --> Handle error
        End If
        .Close
    End With
    Set rst = Nothing
    And another solution would consist in using DLookUp() functions to populate the form, but it would degrade the performances if there are many values to retrieve:
    Code:
    Me.<Field1>.Value = DLookUp("<Column1>", "Stafftbl", "StaffID = " & Me.<Combo>.Value)    ' etc...
    Have a nice day!

  3. #3
    Join Date
    Mar 2011
    Posts
    7
    That is just what I was after - many many thanks, you are a star!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •