Of Mice and Men: Help with a combo box

[Rambo]

Hi,

I am trying to place a combo box on a subform [frmInterviewNote] so
that I can use it to locate records relevant to the subform. The
subform contains information about employee interviews for a given date
[dtmDateofInterview]. I want to be able to use the combo box to find a
specific date for the employee currently selected on the main form i.e.
some have more than one interview which has taken place on a different
day. The main form[frmMainDataEntry] and the subform are currently
linked by an employee id number [strEmployeeID]. I have tried to use
the combo box wizard to do this, but have run into a syntax error
saying

[SELECT [frmInterviewNote]].[strEmployeeID]

I assume that it means that the wizard is having trouble locating the
Employee ID field on the subform, but I don't know how to fix it.

Can anyone offer any help?

Thanks.

Sincerely,
Rambo

 

[Guest]

Rambo:

Lets assume the interview data is in a table called Interviews. To list the
dates of all interviews for the current employee the RowSource property of
the combo box would be:

SELECT dtmDateofInterview
FROM Interviews
WHERE strEmployeID = Forms! frmMainDataEntry!strEmployeeID
ORDER BY dtmDateofInterview;

You need to requery the combo box so it lists the dates for the current
employee as you navigate to different records, so in the Current event
procedure of the subform put:

Me.cboInterviewDates.Requery

where cboInterviewDates is the name of the combo box.

The question then arises as to how you want to show the record for the date
you select in the combo box. One way would be to open a separate form based
on the Interviews table. Say the form is called frmInterviews then you'd put
code like this in the combo box's AfterUpdate event procedure:

Dim strCriteria As String

strCriteria = "strEmployeeID = """ & Me.strEmployeeID & "" " & _
"And dtmDateofInterview = #" & _
Format(Me.cboInterviewDates,"mm/dd/yyyy") & "#"

DoCmd.OpenForm "frmInterviews", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog

This would open the frmInterviews form in dialogue mode, which would mean
you'd have to close it before returning to the main form or subform. This
keeps things neat and tidy as otherwise it could be left open inadvertently
if focus is moved back to the main form/subform.

Ken Sheridan
Stafford, England

 

[Rambo]

Rambo:

Lets assume the interview data is in a table called Interviews. To list the
dates of all interviews for the current employee the RowSource property of
the combo box would be:

SELECT dtmDateofInterview
FROM Interviews
WHERE strEmployeID = Forms! frmMainDataEntry!strEmployeeID
ORDER BY dtmDateofInterview;

You need to requery the combo box so it lists the dates for the current
employee as you navigate to different records, so in the Current event
procedure of the subform put:

Me.cboInterviewDates.Requery

where cboInterviewDates is the name of the combo box.

The question then arises as to how you want to show the record for the date
you select in the combo box. One way would be to open a separate form based
on the Interviews table. Say the form is called frmInterviews then you'd put
code like this in the combo box's AfterUpdate event procedure:

Dim strCriteria As String

strCriteria = "strEmployeeID = """ & Me.strEmployeeID & "" " & _
"And dtmDateofInterview = #" & _
Format(Me.cboInterviewDates,"mm/dd/yyyy") & "#"

DoCmd.OpenForm "frmInterviews", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog

This would open the frmInterviews form in dialogue mode, which would mean
you'd have to close it before returning to the main form or subform. This
keeps things neat and tidy as otherwise it could be left open inadvertently
if focus is moved back to the main form/subform.

Ken Sheridan
Stafford, England

Hi,

I am trying to place a combo box on a subform [frmInterviewNote] so
that I can use it to locate records relevant to the subform. The
subform contains information about employee interviews for a given date
[dtmDateofInterview]. I want to be able to use the combo box to find a
specific date for the employee currently selected on the main form i.e.
some have more than one interview which has taken place on a different
day. The main form[frmMainDataEntry] and the subform are currently
linked by an employee id number [strEmployeeID]. I have tried to use
the combo box wizard to do this, but have run into a syntax error
saying

[SELECT [frmInterviewNote]].[strEmployeeID]

I assume that it means that the wizard is having trouble locating the
Employee ID field on the subform, but I don't know how to fix it.

Can anyone offer any help?

Thanks.

Sincerely,
Rambo

Dear Mr. Sheridan,

This works in a most excellent manner. Thank you for sharing your
expertise.

Sincerely,
Rambo