Search form with subform

[liljean6]

I'm novice in access 2007 I've a project that contain a Table a form with Subform bound to that table, on tthe main form I've unbound [text boxes] and a [search button]

I want that my form act as a search form , so when I click the search button it show the only result an that table subform
I've read a couples of article, it look like I've to use some VBA code!

Can anyone helps please??

 

[billmeye]

You can do this in a couple of ways, both may require more coding than you've done in the past so it might be easier as a learning experience to attach you DB and I can show you.

1) Assign criteria to each field in the recordsource of the subform so changes on the main form affect which records will show in the subform. For example, if you have a field txtLastName on the subform and an unbound control on your main form named txtFilterLastName, the criteria for txtLastName would be

 Like "*" & [Forms]![frmYourMainForm]![txtFilterLastName] & "*"

. You would need to do the same for each field you would like to filter by.

2) You can use the forms Filter properties. This will be a problem if you have many controls you would like to use since you need to include options for all the different combinations. The VBA would be

Me.SubForm.Filter = "[txtLastName]='" & Me.txtFilterLastName & "'"
Me.SubForm.FilterOn = True

If you have 2, 3, 4 etc. controls your using as filters you would need to compount the statement:

Me.SubForm.Filter = "[txtLastName]='" & Me.txtFilterLastName & "' And [txtFirstName]='" & Me.txtFilterFirstName & "'"

 

[liljean6]

Novice alert!!
Thanks for helping me... I've found some code that I've use... it seems don't work!
- There are an other thing that I need in my projet, it's when I search for a record if the search form didn't find it I want that a msg box will pop up with a [No Record Message]

 

[liljean6]

There's the VBA code I've found

Private Sub cmdSearch_Click()
On erorr GoTo errr
Me.propertyTBL_subform.Form.RecordSource = "SELECT * FROM propertyTBL " & BuildFilter
Me.propertyTBL_subform.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"
varWhere = Null

If Me.cboStatus > "" Then
varWhere = varWhere & "[Status] like " & tmp & Me.cboStatus & tmp & " AND "
End If
If Me.cboType > "" Then
varWhere = varWhere & "[Typeproperty] like " & tmp & Me.cboType & tmp & " AND "
End If
If Me.cboZone > "" Then
varWhere = varWhere & "[zone] like " & tmp & Me.cboZone & tmp & " AND "
End If
If Me.txtpricefrom > "" Then
varWhere = varWhere & "([Prix] >= " & Me.txtpricefrom & ") AND "
End If
If Me.txtpriceto > "" Then
varWhere = varWhere & "([Prix] <= " & Me.txtpriceto & ") AND "
End If
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

Private Sub cmdClear_Click()
Me.propertyTBL_subform.Form.RecordSource = "SELECT * FROM propertyTBL "
Me.propertyTBL_subform.Requery
cboType = ""
cboStatus = ""
cboZone = ""
txtpricefrom = ""
txtpriceto = ""
cboType.SetFocus
End Sub

Private Sub cmdclose_Click()
DoCmd.Close
End Sub

Private Sub Form_Load()
cmdClear_Click
End Sub

 

[billmeye]

Attach your DB and I'll take a look.