By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,071 Members | 1,196 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,071 IT Pros & Developers. It's quick & easy.

Filter sub form from the main form.

P: 13
I'm creatig a data base and in my front page i have 3 sub forms and 3 buttons for the sub forms to activate or not.

1. Subform1 is where all the current list of the patients being admitted for consultation. This form is Visible=True upon Form_Load().

2. Subform2 is where all the list of patient's name. This form is Visible=False upon Form_Load().

3. Subform3 displays the patient's personal information and the result entries (it is where the doctor encode their diagnosis, medications and laboratories). This form is Visible=False upon Form_Load().

I want to select a patient from subform1 linking the PatientID from the main form using the button3 and view it as a me.subform3.visible=True and me.subform1.Visible=False.

I have 2 option codes

First code : My problem on this code is that it will not work and no error messages.

Private Sub button3_Click()

Me.subform3.Visible = True
Dim varWhere As Variant

' Initialize to Null
varWhere = Null

' If specified a company name value
If Not IsNothing(Me.PatientID) Then
' .. build the predicate
varWhere = "[PatientID] LIKE '" & Me.PatientID & "*'"
End If

' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "Please select a patient.", vbInformation, gstrAppTitle
Exit Sub
End If

' See if any rows with a quick DLookup
If IsNothing(DLookup("PatientID", "Subform3Qry", varWhere)) Then
MsgBox "No Patient's last name meet your criteria.", vbInformation, gstrAppTitle
Exit Sub
End If

Me.Subform3.Controls("PatientID") = varWhere

End Sub

Second Code: but if im going to use this, it will display an error saying "Run time error '2185': you connot reference aproperty or method for a control unless the control has the focus."

Private Sub button3_Click()
Me.subform3.Visible = True

Dim temp As String
temp = "*" & PatientID.Text & "*"
Me.subform3.Form.Filter = "PatientID like '" & temp & "'"
Me.subform3.Form.FilterOn = True
End Sub

thanks alot in advance for helping.
Jul 5 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 5K+
P: 8,679
  1. First Code - try a change in syntax:
    Expand|Select|Wrap|Line Numbers
    1. Dim strWhere As String
    3. Me![subform3].Visible = True
    6. ' Initialize to Empty if you so desire
    7. strWhere = ""
    9. ' If specified a company name value
    10. If Not IsNull(Me![PatientID]) Then
    11.   ' .. build the predicate
    12.   strWhere = "[PatientID] LIKE '" & Me![PatientID] & "*'"
    13. End If
    15. ' Check to see that we built a filter
    16. If strWhere <> "" Then
    17.   MsgBox "Please select a patient.", vbInformation, gstrAppTitle
    18.     Exit Sub
    19. End If
    21. ' See if any rows with a quick DLookup
    22. If IsNull(DLookup("[PatientID]", "Subform3Qry", strWhere)) Then
    23.   MsgBox "No Patient's last name meet your criteria.", vbInformation, gstrAppTitle
    24. End If
  2. Second Code, try a slight modification since the Text Property can only be referenced when the associated Control has the Focus.
    Expand|Select|Wrap|Line Numbers
    1. temp = "*" & Me![PatientID] & "*"
Jul 5 '09 #2

P: 13
Tanks alot for the advice.. :)
Jul 5 '09 #3

Expert 5K+
P: 8,679
You are quite welcome. Just noticed that Line #16 should be:
Expand|Select|Wrap|Line Numbers
  1. If strWhere = "" Then 
Jul 5 '09 #4

P: 13
Oh thank you so much. i will try this one.
Jul 5 '09 #5

P: 13
wow... thank you once again for helping me. it did work.

best regards.
Jul 6 '09 #6

Expert 5K+
P: 8,679
You are quite welcome, myworks2009.
Jul 6 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.