Problem with queries 1

[mphayesuk]

What I have is three textfields and a subform with a datasheet in. All working from the same table

What I want to happen is filter on one, two or all three textfields.

So you would have a query that would query the table using the textfields and display the results in the subform.

Can anyone help me with this query.

Thanks

 

[spizotfl]

are the text boxes on the main form bound to the underlying table?
are you just providing the boxes on the main form to allow people to restrict the records shown in the subform?
if so, you can make a parameter query for the subform and use afterupdate events on the text boxes to change what is shown.
or you could use a button that takes the values is the text boxes and builds the where clause for the query.

 

[PHV]

What have you tried so far ?
Have a look at the Filter and FilterOn properties of the Form orm object and at the IsNull function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mphayesuk]

The three text boxes are not bound to the table. And yes the boxes are there to restrict the data shown in the subform.

Tried the querys that work after update and no joy....what I want is the subform to update (ie cut down the number of records shown) after each text box has had data put in.

 

[spizotfl]

I just set up a little form/subform like what you are looking for. I set this code to restrict the subform records. To include other text boxes and to make them apply to something specific will take a little extra coding and some means of validating the input to make sure it makes sense for the query.

Private Sub Text0_AfterUpdate()
    Dim str As String
    
    str = "SELECT tblYouth.* FROM tblYouth WHERE tblYouth.LName=forms!frm1.Text0;"
    Me!frmYouth.Form.RecordSource = str
    Me!frmYouth.Form.Refresh
End Sub

 

[mphayesuk]

The code looks good, but what I was after was a way of doing it using three text boxes, I can make it work with one, but your code looks like it will do the job a lot better then the code I have used.

So if you could point me in the right direction for using 3 text boxes, it would be very much appreciated.

Thanks for your post

 

[spizotfl]

will people be entering data into all three boxes and then filtering records? or do you want it to update as the user leaves each box? do you want them to be able to put things in box 1 and box 3, nothing in box 2 and still get results?
i am fairly newb at this, so i am trying to understand as much as i can for writing the code.

 

[mphayesuk]

OK update after leaving each box.

And they will enter info into box1 and nothing else
OR box 1 and 2 and leave 3 blank
OR all 3
But no other combination.

Thanks a lot for help me with this.

 

[spizotfl]

here is a work up of 3 textboxes. there is no error handling or anything like that. you may want to add a reset button or something like that so you can clear the controls. you would just need to set the recordsource back to whatever the original one was. i am sure there is some fairly easy way to do this (like Me!frmYouth.Form.RecordSource = originalQuery).

Private Sub Text0_AfterUpdate()
    Dim str As String

    str = "SELECT tblYouth.* FROM tblYouth WHERE tblYouth.LName=forms!frm1.Text0;"

    Me!frmYouth.Form.RecordSource = str
    
End Sub

Private Sub Text2_AfterUpdate()
    Dim str As String
    
    str = "SELECT tblYouth.* FROM tblYouth "
    str = str & "WHERE tblYouth.LName=forms!frm1.Text0 "
    str = str & "AND tblYouth.Sex = forms!frm1.Text2;"

    Me!frmYouth.Form.RecordSource = str
    
End Sub

Private Sub Text4_AfterUpdate()
    Dim str As String
    
    str = "SELECT tblYouth.* FROM tblYouth "
    str = str & "WHERE tblYouth.LName=forms!frm1.Text0 "
    str = str & "AND tblYouth.Sex = forms!frm1.Text2 "
    str = str & "AND tblYouth.County = forms!frm1.Text4;"
    
    Me!frmYouth.Form.RecordSource = str
End Sub

frm1 is the main form
frmYouth is the subform