Opening form on multiple criteria

[Dazzla]

I have a patient form that has a subform showing the care plans. At the side of each care plan for that patient i have a button that if clicked will take you to the specific care plan. I need this to open the care plan form based on multiple criteria which is

Date (Date Field)
Care Plan Number (Numeric Field)
Patient number. (Text field)

These fields are all on the subform and will be populated.

Any ideas?

 

[wazz]

if a relationship exists, place another subform inside the first subform. when you click on a record in the first subform the second one will show the details.

 

[Dazzla]

I have tried this code linked to a button that seems to work, however i am now getting records that i know are in the database but for some reason it doesnt seem to like the date field and just brings back a blank screen. When i go into design view on the form and theck the filter through properties, it shows the full filter but wont show the information on screen. Its very confusing, again can anybody help

Private Sub Command8_Click()
Dim strFilter As String
Dim strMsg As String


'Check to ensure atleast one criteria has been entered. If not then provide an error message
' telling the user to enter at least one piece of criteria

If IsNull([protech]) And IsNull([Episode_Date]) And IsNull([ICP_Code]) Then
strMsg = MsgBox("No ICP Information!", vbCANCELOnly)
Exit Sub
End If


strFilter = ""


If Not IsNull(Me!protech) Then
strFilter = strFilter & "[protechnic_number]= " & Chr(34) & Me!protech & Chr(34) & " AND "

If Not IsNull(Me!Episode_Date) Then
strFilter = strFilter & "[Episode_Date]= " & "#" & Me!Episode_Date & "#" & " AND "

If Not IsNull(Me!ICP_Code) Then
strFilter = strFilter & "[icp_code]= " & Me!ICP_Code




End If
End If
End If
DoCmd.OpenForm "frmSpecificAssesment", , , strFilter


End Sub

 

[wazz]

i seem to have nested subforms on the brain for some reason...
what is your care plan form based on?
can you create a query that includes your criteria fields then base your care plan form on that query? you can use ANDs and ORs in the query design (QBE).
sry, not sure what to do about your code.

 

[Dazzla]

I could try that, As i say the filter above does work however for some of the dates it does not like and throws back blank records when i know there is stuff there. These dates always seem to start with a zero. for example 03/01/2004.

Dont know if this is a bug in Access or the way the database is set up.