subform load from relational table?

[viveleroi]

I have a form with a subform that pulls all data from tblEmployees. I want to take tblEmployees.Supervisor and pull tbl.Departments.Department where tblEmployees.Supervisor=tbl.Departments.Supervisor yet I cannot figure out a way to make this happen when I load the form. At least I would like when Me.SupervisorName (a combo box with distinct supervisor names) is changed.

any ideas?

 

[Pat Hartman]

I'm not sure I understand your question but the solution may be to base the form on a query rather than a table. This is better practice anyway.

 

[viveleroi]

I have a query that pulls all fields from tblEmployees

One of the fields is Supervisor

When the subform is opened, I want to make access take the Supervisor field value for each row, check table tblDepartments for matches,

so

tblEmployees.Supervisor = John Doe
tblDepartments.Supervisor = John Doe

and then display a text box with the tblDepartments.Department value.

I tried this:

SELECT *
FROM tblDepartments INNER JOIN tblEmployees ON tblEmployees.Supervisor = tblDepartments.Supervisor;

But that only pulls the records that match - if there is an empty Supervisor field in tblEmployees it would omit that - I need to see every record in tblEmployees.

does that make more sense?

I tried:

Private Sub Detail_OnLoad()

    Dim MyDb As Database
    Dim MyRs As Recordset
    Dim strCriteria As String
    Dim Strsql As String
    
    Set MyDb = DBEngine.Workspaces(0).Databases(0)
    
    strCriteria = "Supervisor=" & Chr(34) & Me.Supervisor.Value & Chr(34)
     
    Strsql = ""
    Strsql = Strsql & "SELECT * FROM tblDepartments WHERE " & strCriteria
    
    Set MyRs = MyDb.OpenRecordset(Strsql)
    Me.Departments = MyRs.Fields("Departments")
    MyRs.Close
    Set MyRs = Nothing
    
    Me.Requery
   
End Sub

But that fails... I'm new to VB for access, but I know SQL very well.

 

[Pat Hartman]

You don't need any code at all. You just need to create a query that joins the two tables. Use a left join rather than an inner join if the relationship is optoinal. Use the query as the RecordSource for the form.

 

[viveleroi]

I got the issue resolved.