report based on query run dynamically from form

[train2]

Hello again,
I could do with a little leg up on this one. I've read a couple of threads but none seems to sort out this specific issue.

I've got 3 choices on the form (all for same field, but trawling through same table up to 3 times). Based on the 3 options (from 1 to 3 choices allowed), I then create the SQL for a query, which I want to then feed into the report. I've got to the stage where the SQL seems to be correct, but how do I now link it to a report?

I'm struggling with DoCmd.OpenReport or RunSQL.... any hints.
Thanks
Train2


So far:

Private Sub OK_Click()

Dim Msg, strSQL, strSelect, strFrom, strWhere As String
Dim Repsonse As Variant

'set variables up for most basic...
strSQL = ""
strSelect = "SELECT testtest.*"
strFrom = " FROM "
strWhere = " WHERE "

'first check to ensure an input
If (IsNull(cboProgress)) Then
Msg = MsgBox("You must enter something in the first box", vbOKOnly)
Response = Msg
strFrom = ""
strWhere = ""
strSelect = ""
Else
'combo box 0
If Not (IsNull(cboProgress)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & "testtest INNER JOIN T_Progress ON (testtest.C_Id = T_Progress.C_Id) AND (testtest.P_Id = T_Progress.P_Id) "
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " (T_Progress.R_No = me![cboProgress]) "
Else
strWhere = strWhere & ""
End If
End If
'combo box 1
If Not (IsNull(cboProgress1)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress_1.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & " INNER JOIN T_Progress_1 ON (testtest.C_Id = T_Progress_1.C_Id) AND (testtest.P_Id = T_Progress_1.P_Id) "
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND (T_Progress_1.R_No = me![cboProgress1]) "
Else
strWhere = strWhere & ""
End If
End If
'combo box 2
If Not (IsNull(cboProgress2)) Then
If Len(strSelect) > 0 Then
strSelect = strSelect & ", T_Progress_2.Grade"
Else
strSelect = strSelect & ""
End If
If Len(strFrom) > 0 Then
strFrom = strFrom & " INNER JOIN T_Progress_2 ON (testtest.C_Id = T_Progress_2.C_Id) AND (testtest.P_Id = T_Progress_2.P_Id)"
Else
strFrom = strFrom & ""
End If
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND (T_Progress_2.R_No = me![cboProgress2]) "
Else
strWhere = strWhere & ""
End If
End If

strSQL = strSelect & strFrom & strWhere
Response = MsgBox(strSQL, vbOKOnly)

End If

End Sub

 

[psemianonymous]

I didn't read all your code carefully, but I understand what you're trying to do:
-Open a report with specific criteria (i.e. everything for this week only) and create this criteria in the form.


This is how I do it. I create the report with a query that lists all possible objects. THEN, when I call the report from the form, I set the filter of the report to filter down the data to what I want, based on criteria listed in the form.


So what I'm saying is that instead of creating the SQL from scratch, just focus on everything after the " WHERE " and just pass that where clause into the DoCmd.OpenReport call.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[train2]

Thanks for quick response.

I'd also thought about that, but it's slightly more complicated - basically I add more tables to the query if the fields are selected on the form. I don't see how I could escape from generating SQL for the SELECT and FROM parts of the query. If I put them in the basic query (e.g. T_Progress, T_Progress_1 and T_Progress_2) I presume I will have problems if the user only then selects just T_Progress in the form.

Can I not use the SQL code as the filter part of the DoCmd.OpenReport command? It's this part I'm really struggling with.

Anyway, I'll take a look at your links and have a read!
Thanks,
JR

 

[psemianonymous]

Heh, the links are my default signature. I often answer questions on database corruption....


As to your problem: I see now that you use multiple tables for the report. This is ... okay, I suppose. If you use different tables, then there is no easy workaround to keep one standard query. (the workaround includes a UNION query and adding a field that says "camefromtableX" for each entry--ugly).

So you can set queries' SQL by using the Querydef object and the SQL property. Like so:

CurrentDB.QueryDefs("thequeryname").SQL = strSQL

That should work, once you have assembled your SQL. I just assumed you had all the data in one table (or one set of tables, anyway).


And *this* is why I have my first link: a bunch of examples as to how to set the query's SQL property:

http://groups.google.com/groups?hl=...ms-access&meta=group=comp.databases.ms-access

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[jrbarnett]

Hello,

When you write a selection screen for a report, you don't have to pass across the whole SQL statement. The simplest way is only to dynamically generate the Where clause in your commandbutton code, which is passed over to the report as a WhereCondition argument. This has the advantage of greatly simplifying your code.

On your report, go into design view and set the RecordSource to the following query:

SELECT testtest.*, T_Progress.Grade
From testtest INNER JOIN T_Progress ON (testtest.C_Id = T_Progress.C_Id) AND (testtest.P_Id = T_Progress.P_Id) INNER JOIN T_Progress_2 ON (testtest.C_Id = T_Progress_2.C_Id) AND (testtest.P_Id = T_Progress_2.P_Id)

If you run this from the Reports tab or group depending on your version of access, it should show all data with no problems at this stage. Likewise, if you create a query with this SQL statement and run it, the correct data should be displayed on screen. If this doesn't work, then the revised code below won't.

Onto the next task at hand. From your code I gather that you have 3 controls:

cboProgress
cboProgress1
cboProgress2

Each of the matches that you are doing is against a field called R_No in a different table, and the way it is constructed means the data is numeric (you have to put apostrophes around text values, or hashes around date/time values).
The checking whether the length of the string appended with an empty string signified with the two double quotes effectively negates the need to check for zero length strings and Null values because a null value with an empty string will always result in an empty string as a result.


So, the following code will do the trick:

Private Sub OK_Click

Dim strWhere As String

If Len (Me!cboProgress & "") > 0 Then
	strWhere = " AND T_Progress.R_No = " & Me!cboProgress
End If

If Len (Me!cboProgress1 & "") > 0 Then
	strWhere = " AND T_Progress1.R_No = " & Me!cboProgress1
End If

If Len (Me!cboProgress2 & "") > 0 Then
	strWhere = " AND T_Progress2.R_No = " & Me!cboProgress2
End If

Me.Visible = False ' hide selection window to ensure report comes to front of form when opened.

' Now to open the report
If Len (strWhere & "") >0 Then
	DoCmd.OpenReport "Reportname", WhereCondition := Mid(strWhere, 6) ' means the string starting with character 6 removing the first " AND "
Else
	DoCmd.OpenReport "Reportname" ' with no parameters to show all data
End If

End Sub

I will probably end up writing an FAQ on designing a form to run a report from, the number I have answered recently.

Regards,
John

 

[train2]

OK I'm getting there slowly.
I've changed the SELECT on the report for correct INNER JOIN T_Progress_1 AS T_Progress ... i think that's necessary for the repeated table.

Now as I'm using the same table 3 times, when I tried to run it, it worked, but of course, the data I was looking at was for the same field 3 times (ultimately each with different criteria, but report on it's own shows every combination) - so it's lots longer than it should be - but no worries - presumably when the criteria kick in from the form, it will cut that down.

Now i'm just fighting with the other stuff. After sending a 10000 page job to the printer (and cancelling it v quickly) I've also added AcViewPreview.

I seem to be sitting around waiting for the form->query_>report to run - i suppose it has a lot to do...

Thanks for help
jr

 

[train2]

I'm back at work on this question.

I had 2 strange results:

1. every time I entered details on the form, it prompted me after I'd clicked OK for the same data I'd entered. it only prompted me for the fields that I'd already filled in. presumably it's something to do with the datatype. the R_Id is numeric, so I don't see what the problem could be. i checked the variables, and they seem to be set correctly.

2. when I press OK it's taking ages to run the query... and I'm waiting and I'm waiting... and then I get an enormous report, with lots of duplicated records. it looks as if the filter is not working.... as I get every possible combination of the 3 tables being repeated times.
I've switch Filter to On in the report - are there more settings I need to change? Also when I went to design of the report after I opened it, it also showed the filter to be correct; so again I'm confused there too.

I'm starting to think that this solution is not possible with a query combining the same table more than once, when it is not pre-defined exactly how many choices the user makes.

What do you think?

jr