Help. I'm stuck for two days

[Jeroen_pas]

Access 2010: I want tu use a form to filter and select data and make an output on that selection. Id o not want to use the form filter but a real button. Only bij saving the form and saving a query it is possible to work with a filter. Can somebody help me please?
See also attached file.

Resume: based on data in the database i want to make a selection and outpunt on that selection. Why is this so difficult (for me)?

 

[stevenblanc]

I'm not entirely sure what you are asking. Try and clarify.

I am assuming you are trying to filter your output based on a selection in a listbox, or group of listboxes.

The easiest way to handle that is to use VBA to build an SQL statement.

I generally split my SQL statement into 4 variables for filtering.

strSQLStart: with your SELECT statement and values

strSQLWhere: with your WHERE statement and conditions

strSQLOrder: if you want to order your data

strSQLEnd: usually just ";"

and then call your statement to populate your output listbox or table.

Effectively, its the same as building a custom query based on the user choices but I prefer not to use queries if possible.

Hopefully this is a step in the right direction.

Regards,


Steven

 

[Jeroen_pas]

Steven thanks,

Its my firts time working this advanced with access. Sorry for that.
I developed several forms for to fill the database. Now i want to use the smae forms or parts of the forms tot filter the data and make a report.
Example: one of the input fields in a form is "what is your function" with a nummebr off predifined functions. Now a would like a form wheren i can select the function and push a "start"button with gives me all the records with that specific function. Must not be that difficault?

Greetings from rainy Netherlands

 

[spikepl]

Do some tutorials or grab a free template to see how things tick in Access: http://office.microsoft.com/en-us/templates/CT010152732.aspx?av=ZAC

 

[Jeroen_pas]

I did try but could not find the answer.
i must be tired ... did not sleep this night. deadline on monday

 

[stevenblanc]

Again I'm going out on a limb here, but I am assuming you have the equivalent of two listboxes and a table where the records which have a field which might contain the function exist. Without specific names we'll refer to them as

lstFunction: Where you select your function and;
lstOutput: Where your downstream data shows up.
tblJobs: Where your downstream data is selected from, each record containing a function field. (Which I would not name Function as that may cause problems for you with VBA at some point)
tblFunctions: Where you have your list of functions

You are looking to add something like the following to the Form Load and On Click Event of lstFunction:

Private Sub Form_Load()
 
   Dim strSQL As String
    strSQL = "SELECT [Function] From tblJobs;"
    Me.lstFunction.RowSource = strSQL
 
End Sub
 
Private Sub lstFunction_Click()
 
   Dim strSQL As String
    strSTART = "SELECT [JobDescription], [JobDate], [JobManager] From tblJobs "
    strWHERE = "WHERE tblJobs.Function = """ & lstFunction.Value & """ "
    strORDER = "ORDER BY tblJobs.[JobDate] ASC"
    strEND = ";"
 
    strSQL = strSTART & strWHERE & strORDER & strEND
 
    'MsgBox strSQL
    Me.lstOutput.RowSource = strSQL
 
End Sub

Hope this solves the problem. Of course you will have to tailor it to suit your specific needs.

Note: The code will be slightly different if you plan on allowing the user to selectmultiple functions at a time.

Regards,


Steven