Combo box dependant on another combo box using 3 Tables

[DomFino]

Hi All,
I am out of ideas so I am turning to the experts for help.

What I need to do is select an employee from a combo box (Combo58) and then have another combo box (Combo36) only display holidays with a holiday date greater than or equal to the selected employee’s anniversary date (annDate)

I seem to have much of this worked out except the WHERE clause. I simply do not know how to select the correct records in the second Combo Box.

Any help, suggestions, and recommendations are more than welcome.
Thanks
Dom



Tables

tblEmployees
eid – (employee ID) Autonumber – PK
fName (first name
Lname (last name)
annDate (anniversary date)
More fields, etc.

tblHolidays
hid (holiday ID) Autonumber – PK
hDate (holiday date
hName (holiday description)
so (standard or optional)

tblHolHrs
hhid (holiday hours ID) Autonumber – PK
eid (employee ID)
hDateT (date taken)
hHrs (holiday hours taken)
hid (holiday ID)

Form
frmEmpHolidays

Has two combo Boxes

Combo Box One:
Combo58 – unbound used to select appropriate employee
(row source) SELECT tblEmployees.eid, [lname] & ', ' & [fname] AS Employee, tblEmployees.annDate

FROM tblEmployees

ORDER BY [lname] & ', ' & [fname];

ColumnCount: 3

ColumnWidths: 0";1";0”

LimitToList: Yes

Events: After Update

Private Sub Combo58_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEmpHolidays"
stLinkCriteria = "tblHolHrs.[eid] =" & Me!Combo58 & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria

Combo36.Requery

End Sub



Combo box Two:
Combo36 – bound used to display all the holidays Dates and Descriptions)
(control source) hid
(row source) SELECT tblHolidays.hid, tblHolidays.hDate, tblHolidays.hName, tblHolHrs.eid

FROM tblEmployees INNER JOIN (tblHolidays INNER JOIN tblHolHrs ON tblHolidays.hid = tblHolHrs.hid) ON tblEmployees.eid = tblHolHrs.eid

WHERE (((tblHolHrs.eid)=[Forms]![frmEmpHolidays]![Combo58].Column(3)))

ORDER BY tblHolidays.hDate;


ColumnCount: 4

ColumnWidths: 0";0.75";2";0"

LimitToList: Yes

Events: Got Focus

Private Sub Combo36_GotFocus()
If Len(Trim(Nz(Combo58, "&quot & "&quot) = 0 Then
MsgBox "Please Select Employee First"
Combo58.SetFocus
Else
Combo36.Requery
End If
End Sub

 

[JimYellow]

try a routine like this
cut and paste into a vb or vba editor for some colour

Private Sub SetSearchResults()

Dim strSQL As String
strSQL = ""

Dim haswhere As Boolean
haswhere = False

strSQL = "SELECT SentFaxes.ID, SentFaxes.RefNo AS [Fax Ref No], SentFaxes.CompanyTo AS [Sent To], SentFaxes.Date AS [Date Created], SentFaxes.StaffFrom AS [Created By] FROM SentFaxes"

If isblank(Me.CompanyName) Then
Else
If haswhere Then
strSQL = strSQL & " AND ((SentFaxes.CompanyTo) = '" & Me.CompanyName & "')"
Else
strSQL = strSQL & " WHERE (((SentFaxes.CompanyTo) = '" & Me.CompanyName & "')"
haswhere = True
End If
End If

If isblank(Me.ProjectID) Then
Else
If haswhere Then
strSQL = strSQL & " AND ((SentFaxes.RefNo) = '" & Me.ProjectID & "')"
Else
strSQL = strSQL & " WHERE (((SentFaxes.RefNo) = '" & Me.ProjectID & "')"
haswhere = True
End If
End If

If isblank(Me.StaffName) Then
Else
If haswhere Then
strSQL = strSQL & " AND ((SentFaxes.StaffFrom) = '" & Me.StaffName & "')"
Else
strSQL = strSQL & " WHERE (((SentFaxes.StaffFrom) = '" & Me.StaffName & "')"
haswhere = True
End If
End If

If isblank(dlgContract.ID) Then
Else
If haswhere Then
strSQL = strSQL & " AND ((jobs.contractid) = " & dlgContract.ID & &quot"
Else
strSQL = strSQL & " WHERE (((jobs.contractid) = " & dlgContract.ID & &quot"
haswhere = True
End If
End If

If haswhere Then
strSQL = strSQL & &quot"
Else
End If
strSQL = strSQL & ";"
'print the sql to the imediate window for copying to the query builder
Debug.Print strSQL
'Change the list in the combo or list box
Me.SearchResults.RowSource = strSQL
Me.SearchResults.Requery
End Sub

 

[DomFino]

Hi Jim,
Thanks for the sample code.

I have played around with the problem today and got it to work. It was a real learning experience for a newbee like myself.

My sql for Combo58 now reads as follows:

SELECT tblEmployees.eid, [lname] & ', ' & [fname] AS Employee, tblEmployees.annDate
FROM tblEmployees
ORDER BY [lname] & ', ' & [fname];

FROM tblEmployees

ORDER BY [lname] & ', ' & [fname];

ColumnCount: 3
ColumnWidths: 0";1";0”
LimitToList: Yes
Events: After Update

Private Sub Combo58_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEmpHolidays"
stLinkCriteria = "tblHolHrs.[eid] =" & Me!Combo58 & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria

Combo36.Requery

End Sub


My sql for Combo36 now reads as follows:

SELECT tblHolidays.hid, tblHolidays.hDate, tblHolidays.hName
FROM tblHolidays
WHERE (((tblHolidays.hDate)>=[Forms]![frmEmpHolidays]![txtanndate]))
ORDER BY tblHolidays.hDate;

ColumnCount: 5
ColumnWidths: 0";0.75";2";0";0"
LimitToList: Yes
Events: Got Focus

Private Sub Combo36_GotFocus()
If Len(Trim(Nz(Combo58, "&quot & "&quot) = 0 Then
MsgBox "Please Select Employee First"
Combo58.SetFocus
Else
Combo36.Requery
End If
End Sub

I had to add a hidden field to the frmEmpHolidays called txtanndate with a control source of =[Combo58].[Column](2)

I now select an employee and the only holidays that show in the combo36 box are those that are greater than or equal to the annDate.

It took a long time to get this to work but it is working. Your suggested sample was a bit hard for me to follow since I am not an Access expert by any stretch of the imagination. I am a beginner trying to learn on my own with a great deal of help from this terrific site. I will, however, study your sample further since it appears that it may offer an alternative to the way I am doing it.
Dom