Building Query from Blank Form

I want to have a blank form with text boxes which will allow users of the application to build their own specific reports. They have a number of reports with parameters etc however i want them to be able to do their own. For example, have a form where you can select a range of dates, call ref numbers, priority of calls logged, development details etc? Hope this is making sense. The form will be linked to a large table which has all product details and dev calls that have been logged on the system. Just want to be able to give users more flexibility in the reports and data that they can view. Can this be done in Access? Please help on this one!!
Nikkimc

Hi nikkimc

This is called Query By Form and can be created quite easily. To get you started with a sample of how this is done, create a New Database and import the table Orders from the Northwinds Sample database to use for this example. Create a new query and add the table Orders but don't add anything into the query grid, it won't be necessary. Close and save the query as qryUserSelect. We will add what goes into the query grid from your form using sql. Next create a blank form and name this frmSelection. Add two text boxes, one will be named txtStart and the other will be named txtEnd. In the properties of each text box for the Format type in dd-mmm-yyyy. Next add a command button and give this a name of cmdResults with the caption View Results.
Open the code window and paste the following function:

Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String, strFROM As String, strWHERE As String
strSELECT = "*"
strFROM = "[Orders]"
strWHERE = " AND OrderDate >= [Forms]![frmSelection]![txtStart]"
If Not IsNull(txtStart) Then strWHERE = strWHERE
If Not IsNull(txtEnd) Then strWHERE = strWHERE & " And OrderDate <= [Forms]![frmSelection]![txtEnd]"
strSQL = "SELECT" & strSELECT
strSQL = strSQL & "FROM" & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
BuildSQLString = True
End Function

In the OnClick event for the command button type in this code:

Dim strSQL As String
If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If
CurrentDb.QueryDefs("qryUserSelect").SQL = strSQL
RefreshDatabaseWindow
DoCmd.OpenQuery "qryUserSelect", , acReadOnly

Close and save the form and reopen it. Type a date into the Start date text box in any of the standard date formats and press the command button. This will open your query of records for anything greater than this date. Typing in the End date will narrow the records down between the two dates. You might want to look at the date ranges in the table so you can select some dates that are shown in the OrderDate field so you can return records. The Northwinds Sample database is old and the dates are rather old.

Now this is just an example and there is no error handling for null values, but it will give you some ideas on how to construct several more fields with a wide range of perameters for the user to select from. All it will take is changing the WHERE clause or adding addition WHERE clauses to fit each of the particular controls you will use in your application. You can even add a list box showing the >, <, <=, >=, And, Or, Between, Like constants to offer even more flexibility and place that into your code as well.

See if this will get you started,
lwells

Thanks I will try this just now - your a star. I thought no one was bothering with me there. While you are replying to things. Is there any specific reason why Access crashes out when trying to run reports over an ODBC connection. Just one table that it does it for. The table is only 23,500 records not much but Access seeems to have probs with this???? Thanks for all your help

Hi nikkimc

Did access give you an error message or an error number when running your report? It won't be a problem due to the number of records. I have several tables containing nearly three times as many records running over an ODBC connection with no problem. So if there was an error message or error number then can approach solving the problem from that perspective first.

lwells

Hi there thanks for replying, no i receieve no error at all. When creating the report it just gives the hour glass and stops responding. It only seems to be doing it with this table for some reason. I can run the queries and see the datasheet view but the minute you try and do this with a report view it just bombs out on me. Thanks for helping

Nikki

Nikki,

Can you paste the code from your form that you are using to build your query from so I can see how you are trying to open your report. If you are able to see the query results from your form, but just not able to open the report, maybe I can spot what's causing the problem. With the hourglass showing, generally that means that the query is running in a continous loop for some reason.

I am assuming you are opening your report from a command button thats on the form.

lwells

Hi there, thanks for your quick response. I am not even being able to get the chance to build the code so that you can see it - i am creating the report using the wizard. When it gets to the last step of previewing or showing in design this is when it falls over. I can show you the code which i use for the other reports that do work if that helps but it will be the standard code which Access has created.

You may actually be able to answer this also. I am running Access 2002 on XP and i need to take out my default printer (only printer) (networked) so that i can run reports! Strange? Is there a setting in XP i need to change?

I have used the code in the example and it works better than anything I have had yet, although I am having trouble with dates in general date format, is that only me?

also the results are in a databse view, is there a way to make them refresh the query within the form?
I guess its the 'RefreshDatabaseWindow' part that tells it do do this but I would rather have it refresht eh form results.

This is a great piece of script as it also allows reports from the query! Excellent for invoices etc.


While I am writing I am also having a great amount of trouble with check boxes, I have tried to use them within a query but im not sure why they dont work, do they have true / false as values or something or can you set the values as 'value you want' and 'Null' ??

Thanks again, pete.

I have tried my best and evcen went into making strings I think...



So what I tried was, to create a string for the customer WHERE depending on weither the checkbox was checked or not, but Im not sure how to implement this into the SQL statement correctly, or if this is even the best way to go about it.

I know that If I try to continue with the way its done the sql is wrong because I will have WHERE Customer = this WHERE Customer = that WHERE customer = other.....etc etc.. and SQL doesnt seem to like more than one WHERE statement.

Please help.

Well a couple of things, I think you wanted to have your checkbox = a name that was in the table for sorting, so I rewrote the code to do that, but not seeing your database it was difficult to tell. Second, the form you are asking about to change based on your search criteria can be accomplished by setting the forms record source to the query and then requery the form after the search was made. RefreshDataBaseWindow will change your query to what you searched for.

Try this code to make your checkboxes work like you wanted.

Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String, strFROM As String, strWHERE As String, strCUSTOMER As String
strSELECT = "*"
strFROM = "[mainstatic]"
strWHERE = ""
If Not IsNull(shopnumbertxt) Then strWHERE = strWHERE & " And [mainstatic]![Job number] = [Forms]![view job]![shopnumbertxt]"
If Not IsNull(jobstatusfilter) Then strWHERE = strWHERE & " And [mainstatic]![Job status] = [Forms]![view job]![jobstatusfilter]"
If Not IsNull(datestarttxt) Then strWHERE = strWHERE & " And [mainstatic]![Date of work] >= [Forms]![view job]![datestarttxt]"
If Not IsNull(datefinishtxt) Then strWHERE = strWHERE & " And [mainstatic]![Date of work] <= [Forms]![view job]![datefinishtxt]"
strCUSTOMER = ""
If aramiskacheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "aramiska" & Chr(34) & " Or [mainstatic]![Customer] ="
If bespokecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bespoke" & Chr(34) & " Or [mainstatic]![Customer] ="
If btcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bt" & Chr(34) & " Or [mainstatic]![Customer] ="
If coralcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "coral" & Chr(34) & " Or [mainstatic]![Customer] ="
If kingstoncheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "Kingston" & Chr(34) & " Or [mainstatic]![Customer] ="
If mducheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "mdu" & Chr(34) & " Or [mainstatic]![Customer] ="
If pipexcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "pipex" & Chr(34) & " Or [mainstatic]![Customer] ="
If patientlinecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "patientline" & Chr(34) & " Or [mainstatic]![Customer] ="
If residentialcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "residential" & Chr(34) & " Or [mainstatic]![Customer] ="
If sportstvcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "sports tv" & Chr(34) & " Or [mainstatic]![Customer] ="
If strCUSTOMER <> "" Then
strCUSTOMER = Left$(strCUSTOMER, Len(strCUSTOMER) - 28)
strWHERE = strWHERE & " And [mainstatic]![Customer]=" & strCUSTOMER
Else
strWHERE = strWHERE
End If
strSQL = "SELECT" & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
BuildSQLString = True
End Function

Thankyou so much for your help! It has at least let me make some progress, I am trying to understand what it is you have done as im not up to your level of understanding of access yet.

The only thing I dont understand is that the check boxes are working seperate to the rest of the query, so If I selected the status (statusfilter) as say 'completed' and hit the filter button sure enough it filters out the records with status complete...

If I select the Coral checkbox, sure enough there are the complete records done by coral, but If I select another checkbox e.g pipex it brings up the complete Coral records, but all the pipex records in the table.

I cant see why this is, it seems logical to me that your code should work...

any help from anyone would be so much appreciated.

I have tried putting the first If below the checkbox's ifs but still no results..

Make a backup copy of your database and send it to lwells1433@aol.com and let me see what you are trying to do. Using a simple beta.mdb with what I could pull from your original post, the rewritten function should be working like you wanted. Once I see the entire structure, I can taylor the code to your specific application.

lwells

I have the form running from the query made by the script, but how can I get the results to show directly into the form rather than showing the database window???

many thanks.