Opening a Form

[Andrew C]

Hi

On my main form i have a command button which currently opens another form
based upon the the staff member selected in a combo box. (Works OK) The
form it opens has the staff member field at the top and then a subform
underneath with reords of that staff member.

Wnat i want to add in a start date and end date fields to the main form. So
that when you enter a date it will show the staff members records on that
date or between the to dates.

Also i would like it that if there is no staff member selected it will show
all records and no dates entered it will show all records.

Below is what code i have so far:

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Staff Contact"

stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim strForm As String
strForm = "Main"
If CurrentProject.AllForms(strForm).IsLoaded Then
DoCmd.Close acForm, strForm
End If

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub


If you need more information just ask. Im not an advance user of access but
i know my way around.

Thanks

 

[Ofer]

Try something like
If Not Isnull(Me![Combo13]) Then
' If the combo selected it will have priority on the filter
stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]
Else
' If the combo empty it check the date fields
If Not Isnull(Me.[Start Date]) And Not IsNull(Me.[End Date]) then
stLinkCriteria = "[Contact.DateFieldName] Between #" & Me![Start
Date] & "# And #" & Me.[End Date] & "#"
Else
'If both empty the filter will be empty
stLinkCriteria = ""
End If
End If

I hope this will give you an idea how to continue

 

[Andrew C]

am i to add this code with my other code or is there other stuff i have to
type in to complete this

Try something like
If Not Isnull(Me![Combo13]) Then
' If the combo selected it will have priority on the filter
stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]
Else
' If the combo empty it check the date fields
If Not Isnull(Me.[Start Date]) And Not IsNull(Me.[End Date]) then
stLinkCriteria = "[Contact.DateFieldName] Between #" & Me![Start
Date] & "# And #" & Me.[End Date] & "#"
Else
'If both empty the filter will be empty
stLinkCriteria = ""
End If
End If

I hope this will give you an idea how to continue


--
\\// Live Long and Prosper \\//
BS"D

Hi

On my main form i have a command button which currently opens another form
based upon the the staff member selected in a combo box. (Works OK) The
form it opens has the staff member field at the top and then a subform
underneath with reords of that staff member.

Wnat i want to add in a start date and end date fields to the main form. So
that when you enter a date it will show the staff members records on that
date or between the to dates.

Also i would like it that if there is no staff member selected it will show
all records and no dates entered it will show all records.

Below is what code i have so far:

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Staff Contact"

stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim strForm As String
strForm = "Main"
If CurrentProject.AllForms(strForm).IsLoaded Then
DoCmd.Close acForm, strForm
End If

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub


If you need more information just ask. Im not an advance user of access but
i know my way around.

Thanks

 

[Ofer]

You need to add it instead of this line

stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]


--
\\// Live Long and Prosper \\//
BS"D

am i to add this code with my other code or is there other stuff i have to
type in to complete this

Try something like
If Not Isnull(Me![Combo13]) Then
' If the combo selected it will have priority on the filter
stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]
Else
' If the combo empty it check the date fields
If Not Isnull(Me.[Start Date]) And Not IsNull(Me.[End Date]) then
stLinkCriteria = "[Contact.DateFieldName] Between #" & Me![Start
Date] & "# And #" & Me.[End Date] & "#"
Else
'If both empty the filter will be empty
stLinkCriteria = ""
End If
End If

I hope this will give you an idea how to continue


--
\\// Live Long and Prosper \\//
BS"D

Hi

On my main form i have a command button which currently opens another form
based upon the the staff member selected in a combo box. (Works OK) The
form it opens has the staff member field at the top and then a subform
underneath with reords of that staff member.

Wnat i want to add in a start date and end date fields to the main form. So
that when you enter a date it will show the staff members records on that
date or between the to dates.

Also i would like it that if there is no staff member selected it will show
all records and no dates entered it will show all records.

Below is what code i have so far:

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Staff Contact"

stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim strForm As String
strForm = "Main"
If CurrentProject.AllForms(strForm).IsLoaded Then
DoCmd.Close acForm, strForm
End If

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub


If you need more information just ask. Im not an advance user of access but
i know my way around.

Thanks

 

[Andrew C]

Hi Ofer

I have tried to adjsut the code you gave me. I want it to look up if there
is a start date and no end all records from start date. If there is end date
but no start date all records before end date. If there is start date and
end date records between these dates and including the dates.

My problem is that no matter what dates i enter it doesnt do anything. I
also will mention that the command button loads a form which has the staff
member field at the top only and then a subform with all records relating to
that particular staff member. (Hope i have not confused you)

Here is what i have.

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Staff Contact"

If Not IsNull(Me![Combo13]) Then
stLinkCriteria = "[Contact.Staff Member]=" & Me![Combo13]
Else
stLinkCriteria = ""
End If

If IsNull(Me.Start) Then
If Not IsNull(Me.End) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.End, conDateFormat)
End If
Else
If IsNull(Me.End) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.Start, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.Start,
conDateFormat) _
& " And " & Format(Me.End, conDateFormat)
End If

End If


DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim strForm As String
strForm = "Main"
If CurrentProject.AllForms(strForm).IsLoaded Then
DoCmd.Close acForm, strForm
End If

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

What have i got worng??

Thanks