Need help beginning new project (1 Viewer)

Dear All,

I am fairly new to access tho I have a fair bit of experience with VBA in excel. I am wanting (amongst other things!) to design a form with a subform on it to show certain business enquiries. The subform to show all of the enquiries (not editable) in a continuous form view with the header field also having text boxes which filter the data (ie searching for Last Name etc). The rest of the form to show all the details (editable) of the enquiry selected in the subform.

I have managed to do the first subform which works on its own but will not filter when it is a subform of a main form. Or it will filter the whole "Main" form results but not filter the list of results on the subform.

Dim szLastName As String, szCrit As String

szLastName = "*" & Nz(Me!LastName, "") & "*"
szCrit = BuildCriteria("LastName", dbText, szLastName)
DoCmd.ApplyFilter , szCrit

As I am learning as I go along here, I would mainly like some advice of the best ways of doing this, Im not looking for full coded solutions.

Thanks in advance,
Tom

Take a look at Allen Browne's example of how to filter a main form based on filter criteria on the subform:
http://allenbrowne.com/ser-28.html

Thanks for your reply George, I found a lot of stuff on that site very informative. I didnt really understand the INNER JOIN stuff though, as I am only really working with one datasheet at the moment I didnt think it was necessary. The code that seems to work now is:

Private Sub filtering()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.FilLN) Then
strWhere = strWhere & "([LastName] Like ""*" & Me.FilLN & "*"") AND "
End If
If Not IsNull(Me.FilFN) Then
strWhere = strWhere & "([FirstName] Like ""*" & Me.FilFN & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
Me.Form.FilterOn = False
Else
strWhere = Left$(strWhere, lngLen)
Me.Form.Filter = strWhere
Me.Form.FilterOn = True
End If

End Sub


This is called like this:

Private Sub FilLN_AfterUpdate()
Call filtering
End Sub

and updates in the main window like this:

Private Sub Form_Current()

Forms![frmStudents].RecordSource = "SELECT tblStudentDetails.StudentID FROM tblStudentDetails WHERE (tblStudentDetails.StudentID)= '" & Me.StudentID & "';"

End Sub



This seems to do the trick, one slightly unrelated question though, I want to filter with a dropdown box of ages taken from the StudentDetails table using (in the row source);

SELECT tblStudentDetails.Age FROM tblStudentDetails;

But i also want to put a blank choice in this drop down so the filter can easily be removed by the user. Is there an easy way of doing this??

Thanks,
Tom

You can't just erase the value?

I can but I need to give it to someone who is completely computer illiterate, so would like to make it as obvious as possible.

Thanks,
Tom