[RESOLVED] [Access 2003] Form Data Source

[Torc]

I have a form on which I have a header and a detail section, the default view is set to Continuous Forms and the Data Soruce is set to a Query.

In my detail section I have some fields from the query, so that I get to see all entries of the query in the form.

In my header I have three fields, Date, Action and User.

How can I use the contents of these fields to limit the results shown.

i.e. The query is filtered on the relevant fields.

[Hack]

Off the top of my head I would say you could limit what is shown by specifiying a specific date, a specific action and and specific user.

If you specify all three, it should be really, really limited, so perhaps only specifiying two, or even one, would give you good results.

[Davadvice]

hi Torc,

You could use a query def and re-write the source query dependant on the filters you apply.

say you have a customer name then you can say select * from tblCustomer where CusomerId = 1

i aint used the querydef object in a while but if you use the help in access it gives an example that you should be able to apply. you will have to use the requery function on the form after you have changed the query.

i'm not sure if you can actualy change the rowsource of a form at runtime but this may be an easier way to do it. all you would have to do is set the form rowsource to the sql you wich to apply and then refresh the form.

I aint in work just now so don't have access opened and am unable to test sorry.

good luck

David

[Torc]

Hi Hack,

My data source has a total of five fields, Date, Action, User, IDCode and Value.
My form needed to display the list of IDCode and Value entries for Date,
Action and User, I could have 1 to 20 entries with different IDCodes.

Hi Davadvice,

Many thanks for the advice, I was able to test the following code which works, will now be able to adapt my real code..

Sub ReQuery()
Dim QuerySqlStr As String
    QuerySqlStr = "SELECT Table1.Date, Table1.User, Table1.Results, Table1.Value " & _
                    "FROM Table1 " & _
                    "WHERE Table1.Date=#" & Format(Form_Form1.TxtDate.Value, "yyyy/mm/dd") & "# AND Table1.User='" & Form_Form1.TxtName.Value & "'"

    CurrentDb.QueryDefs.Delete "Query1"
    CurrentDb.CreateQueryDef "Query1", QuerySqlStr
    
    Form_Form1.LblQuery.Caption = QuerySqlStr
    Form_Form1.RecordSource = QuerySqlStr
End Sub