Display records on the form based on the selection

[Ac]

I created two forms which are Start and Display forms. The Start Form has a
drop-down list which can select the year from, and a cmdAll button on that
form to open the Display form. The display form lists all records from
continues years and contains two fields Effective Date and Expiration Date to
separate the records from years.

Based on selected year from drop-down list on the Start form, after the
Detail-All button is clicked, I would like Display form lists the
corresponding records in that form. (For example, drop-down list is 2007,
and Display form will list all records which are Effective Date >=1/1/2007
and Expiration Date <=12/31/2008 only)

I would like to use one Display form without creating forms for each year,
and code for Detail-All button on the Start form will be:

Private Sub cmdAll_Click()
On Error GoTo Err_cmdAll_Click

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "All"

stLinkCriteria = "Year(Forms("All").[EffectiveDate])=" & Me.cboYear

If stLinkCriteria = 2007 Then

DoCmd.OpenForm stDocName, , , stLinkCriteria
lblAll.Caption = "2007 All"
lblTotal.Caption = "Total (2007 only)"

ElseIf stLinkCriteria = 2008 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
lblAll.Caption = "2008 All"
lblTotal.Caption = "Total (2008 only)"

ElseIf ……….

End If

End Sub


It does not work. What is wrong?

 

[Beetle]

"It doesn't work. What's wrong?" is somewhat of an ambiguous question, so
I'll assume your getting some sort of error message when you click the
command button. Here are a couple of thoughts.

First, the link criteria is actually a string, so you need to use the proper
quote delimiters.

Second, instead of having an endless secession of ElseIfs to cover every
possible year, why not store the value from cboYear in a variable and then
insert it into your label caption.

Here is some sample code that should work.

Private Sub cmdAll_Click()

Dim strDocName As String
Dim strLinkCriteria As String
Dim strYear As String


strDocName = "All"
strLinkCriteria = "Year([EffectiveDate]) ='" & Me.cboYear & "'"
strYear = Me.cboYear

DoCmd.OpenForm strDocName, , , strLinkCriteria

With Forms!All

!lblAll.Caption = strYear & " All "
!lblTotal.Caption = "Total (" & strYear & " only)"

End With

End Sub

HTH