Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > answers

Opening a report based on a user defined query

Newbie
  
Join Date: Aug 2007
Posts: 24
#1: Aug 2 '07
I have been looking for some time now (reading books off Safari, searching through forums,etc) I have found no solution to this problem. I turn to anyone of you that may be able to help me.

I'm trying to create a database for work, using Microsoft Access 2003 on Windows XP.

I have a product licensing database and it has one table named serial numbers, that table holds all the information associated with SNs, including customer name, customer state, customer city.... etc etc. Its approximately 30 fields including the serial number field.

It would be impossible for me to create a report to show all the information, so I decided it would be more convenient to have the user define which fields they would like to see on the report.

I have forms set up to guide the user through the process of picking fields and setting the ordering, and then I generate the query that would present the user with the information they desire. Now, the query works and pops up the query I want, here is the code for that:
Expand|Select|Wrap|Line Numbers
  1.     Dim database As DAO.database
  2.     Dim qdef As DAO.QueryDef
  3.  
  4.     Set database = CurrentDb
  5.     Set qdef = database.QueryDefs("QueryForReport")
  6.  
  7.     qdef.SQL = query
  8.  
  9.     DoCmd.OpenQuery "QueryForReport", , acEdit
  10.  
query is a string that contains all the syntax in a valid SQL statement

Now here comes the part I can't get to work.

I have a button on a form, that when I click it the query result pops up. Instead I want a report to come up with the information that the query has in it, except with a title (the user specified), page number, date, and formatted in a printable way.

This way I wont have to create hundreds of reports, I can just let the user make their own report with a max of 6 fields.

Any help would be appreciated. I can hold my own with VBA, and Access stuff (I'm a little confused about bang notation, but i dont think this problem involves it)

I tried multiple different forms of this kind of code immediately following the open query statement.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "UserDefinedReport", acViewPreview, query
  2.  
That opened a report but a blank one with no information in it. I want it based on the SQL string that query holds.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#2: Aug 6 '07

re: Opening a report based on a user defined query

OK you can open a dynamic report but the formatting of the report is not great.

Expand|Select|Wrap|Line Numbers
  1. Sub NewReport()
  2. Dim rpt As Report
  3.  
  4.     ' Return variable of data type Report pointing to
  5.     ' new Report object.
  6.     Set rpt = CreateReport
  7.     ' Set properties for new report.
  8.     With rpt
  9.         .RecordSource = "QueryForReport"
  10.         .Caption = "Report Title"
  11.     End With
  12.     ' Restore new report.
  13.     DoCmd.Restore
  14. End Sub
  15.  
Newbie
  
Join Date: Aug 2007
Posts: 24
#3: Aug 6 '07

re: Opening a report based on a user defined query

Thanks for the response! For how hard I had to search for an answer I sure didn't think the code would be as short as it was. I will try this sometime this afternoon and let you know how it goes. Also, do you have a reference or anything pointing me to where you got this info from? Its always good to have a bunch of resources.

Also, while I was waiting for a response from someone I managed to find this link, which shows how to do a dynamic report except its using a crosstab query...(I can't use one of them because it doesn't produce the results I want) I am trying to modify the code however.

Here is that link:
http://support.microsoft.com/default...;en-us;Q328320
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#4: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by wexx

Thanks for the response! For how hard I had to search for an answer I sure didn't think the code would be as short as it was. I will try this sometime this afternoon and let you know how it goes. Also, do you have a reference or anything pointing me to where you got this info from? Its always good to have a bunch of resources.

Also, while I was waiting for a response from someone I managed to find this link, which shows how to do a dynamic report except its using a crosstab query...(I can't use one of them because it doesn't produce the results I want) I am trying to modify the code however.

Here is that link:
http://support.microsoft.com/default...;en-us;Q328320

As I said, the code I gave you doesn't format the report very well.

If you need to impose a format then use the above code. Concentrate from Create a Report That Is Named EmployeeSales onwards.

The code I have given you can actually be found in the VBA help file.
Newbie
  
Join Date: Aug 2007
Posts: 24
#5: Aug 6 '07

re: Opening a report based on a user defined query

Ok for now I just want the report to work (good formatting or not). No matter which way I try, your way or the microsoft help page's way it still justs gives me a blank report. In the case that I use your way of opening the report, it opens the form with the name Report1 in Design View, instead of preview mode. Do I have to add any of my own code to the code you posted?
missinglinq's Avatar
Moderator
  
Join Date: Nov 2006
Location: Richmond, Virginia USA
Posts: 2,994
#6: Aug 6 '07

re: Opening a report based on a user defined query

Two things I noticed just in passing:

"query is a string that contains all the syntax in a valid SQL statement"

I take this to mean that query is a string variable. If you've actually named a variable "query" you need to change this to something else, such as strQuery, as query is a reserved word in Access and sooner or later this is going to cause you problems.

"that table holds all the information associated with SNs, including customer name, customer state, customer city.... etc etc"

Is a customer ever associated with more than one Serial Number? If so, you really should have at least two tables, one for Serial Numbers and one for Cutomer Information, tied together by a unique Customer identifying field.

Linq ;0)>
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#7: Aug 6 '07

re: Opening a report based on a user defined query

Also can you tell us where query is defined and can you post the sql of the query.
Newbie
  
Join Date: Aug 2007
Posts: 24
#8: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by missinglinq

Two things I noticed just in passing:

"query is a string that contains all the syntax in a valid SQL statement"

I take this to mean that query is a string variable. If you've actually named a variable "query" you need to change this to something else, such as strQuery, as query is a reserved word in Access and sooner or later this is going to cause you problems.

"that table holds all the information associated with SNs, including customer name, customer state, customer city.... etc etc"

Is a customer ever associated with more than one Serial Number? If so, you really should have at least two tables, one for Serial Numbers and one for Cutomer Information, tied together by a unique Customer identifying field.

Linq ;0)>

I took your advice and renamed my string variable "query" to "strquery". Good call, I didn't pick up on that since the VBA editor didn't automatically capitalize it and highlight it.

you were also correct in assuming that serial number is a primary key. I have multiple customer names that are the same or similar but the city and state can vary. If I would end up splitting my one huge table into smaller ones, would I have to redo all my forms, queries, reports, etc. or would they be updated automagically?
Newbie
  
Join Date: Aug 2007
Posts: 24
#9: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

Also can you tell us where query is defined and can you post the sql of the query.

query is defined as a variable inside the a button click event. here is the code for that entire button.

the sql statement is built inside the button click, based off 2 forms of data, the first form (CustomReportPrinting) is to grab the 6 items they want in the report and how they want to sort the results.

The second form (TitleLayout) is to get the title name of the report, and if they want a landscape view or portrait view. This is the form with the button to create the report.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreate_Click()
  2.     Dim orientation As String   'holds the value to determine the orientation of the report
  3.     Dim title As String         'holds title of report
  4.     Dim orderby As String       'item to order things by
  5.     Dim ordertype As String     'ascending, decending, none
  6.     Dim itemcount As Integer    'variable to hold the original value of how many items 1-6 were in the contents
  7.                                 'list box
  8.  
  9.     Dim selections(5) As String 'array of what items to display in the report
  10.     Dim selectcnt As Integer    'count of the number of those items
  11.  
  12.     Dim tablename As String     'name of the master table
  13.     Dim fieldnames(5) As String 'the selections(array) formatted with [] to make it into a suitable sql statement
  14.  
  15.     Dim qselect As String       'the entire sql select statement
  16.     Dim qfrom As String         'the entire sql from statement
  17.     Dim qorderby As String      'conditional sql statement to order items by a field
  18.     Dim strquery As String         'the entire query containing the select, from, where, and anything else
  19.  
  20.     ' determine the appropriate layout
  21.     If Me.LayoutOrient.Value = 1 Then
  22.         orientation = "landscape"
  23.     ElseIf Me.LayoutOrient.Value = 2 Then
  24.         orientation = "portrait"
  25.     End If
  26.  
  27.     'Debug.Print ("orientation: " & orientation)
  28.  
  29.     'set the title
  30.     title = Me.txtTitle.Value
  31.     'Debug.Print ("title: " & title)
  32.  
  33.     'get the ordering type
  34.     If Form_CustomReportPrinting.tglOrder.Value = 1 Then
  35.         ordertype = ";"
  36.     ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
  37.         ordertype = "DESC;"
  38.     End If
  39.  
  40.     'Debug.Print ("Order type: " & ordertype)
  41.  
  42.     'assign what field to sort things by
  43.     If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
  44.         orderby = ""
  45.     Else
  46.         orderby = Form_CustomReportPrinting.cmbOrders.Value
  47.     End If
  48.  
  49.     'Debug.Print ("ordered by: " & orderby)
  50.  
  51.     selectcnt = 0
  52.     'fill in the selections array with data and get the count
  53.     While Not (IsNull(Form_CustomReportPrinting.lstcontents.ItemData(selectcnt)))
  54.         selections(selectcnt) = Form_CustomReportPrinting.lstcontents.ItemData(selectcnt)
  55.         'Debug.Print (selections(selectcnt))
  56.         selectcnt = selectcnt + 1
  57.     Wend
  58.  
  59.     selectcnt = selectcnt - 1 ' the count went one too high after finishing so fix it
  60.     itemcount = selectcnt
  61.  
  62.     'give a variable the value of the table name so as to reduce typing and mistyping the table name
  63.     tablename = "[Master AUI Serials]."
  64.  
  65.     'format the fieldnames properly
  66.     While selectcnt >= 0
  67.         fieldnames(selectcnt) = (tablename & "[" & selections(selectcnt) & "]")
  68.         'Debug.Print (fieldnames(selectcnt))
  69.         selectcnt = selectcnt - 1
  70.     Wend
  71.         selectcnt = itemcount
  72.  
  73.     Select Case itemcount 'build the select statement
  74.         Case 0
  75.             qselect = ("SELECT " & fieldnames(0) & Chr(13))
  76.         Case 1
  77.             qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & Chr(13))
  78.         Case 2
  79.             qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) & Chr(13))
  80.         Case 3
  81.             qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) & "," & fieldnames(3) & Chr(13))
  82.         Case 4
  83.             qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) _
  84.              & "," & fieldnames(3) & "," & fieldnames(4) & Chr(13))
  85.         Case 5
  86.             qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) _
  87.              & "," & fieldnames(3) & "," & fieldnames(4) & "," & fieldnames(5) & Chr(13))
  88.     End Select
  89.  
  90.     ' Build the from statement
  91.     qfrom = ("FROM [Master AUI Serials]" & Chr(13))
  92.  
  93.     If orderby = "" Then
  94.         qorderby = ("" & Chr(13))
  95.     Else
  96.         qorderby = ("ORDER BY " & tablename & "[" & orderby & "]" & Chr(13))
  97.     End If
  98.  
  99.     strquery = qselect & qfrom & qorderby
  100.  
  101.     'run the query
  102.     Dim database As DAO.database
  103.     Dim qdef As DAO.QueryDef
  104.     Dim rpt As Report
  105.  
  106.     Set rpt = CreateReport
  107.     Set database = CurrentDb
  108.     Set qdef = database.QueryDefs("QueryForReport")
  109.  
  110.     qdef.SQL = strquery
  111.  
  112.     'DoCmd.OpenQuery "QueryForReport", , acEdit
  113.  
  114.     With rpt
  115.         .RecordSource = "QueryForReport"
  116.         .Caption = title
  117.  
  118.     End With
  119.  
  120.     ' Restore new report.
  121. DoCmd.Restore
  122.     '**********************************************************
  123.     '                       Main Debug Area
  124.     '**********************************************************
  125. '    Debug.Print ("Report Title:     " & title)
  126. '    Debug.Print ("Orientation:      " & orientation)
  127. '    Debug.Print ("Ordered by:       " & orderby)
  128. '    Dim temp As Integer
  129. '    temp = itemcount
  130. '    While temp >= 0
  131. '        Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
  132. '        temp = temp - 1
  133. '    Wend
  134. '    Debug.Print ("SQL Select statement is:" & Chr(13))
  135. '    Debug.Print (qselect)
  136. '    Debug.Print (qorderby)
  137. '    Debug.Print (strquery)
  138.     '**********************************************************
  139.     '                      end debug area
  140.     '**********************************************************
  141. 'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
  142.  
  143. End Sub
  144.  
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#10: Aug 6 '07

re: Opening a report based on a user defined query

Try this instead ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreate_Click()
  2. Dim orientation As String 'holds the value to determine the orientation of the report
  3. Dim title As String 'holds title of report
  4. Dim orderby As String 'item to order things by
  5. Dim ordertype As String 'ascending, decending, none
  6. Dim itemcount As Integer 'variable to hold the original value of how many items 1-6 were in the contents
  7. 'list box
  8. Dim selection As Variant 'variable to hold current selected value
  9. Dim selectcnt As Integer 'count of the number of those items
  10. Dim tablename As String 'name of the master table
  11. Dim fieldnames As String 'the list of fieldnames separated by comma
  12. Dim qselect As String 'the entire sql select statement
  13. Dim qfrom As String 'the entire sql from statement
  14. Dim qorderby As String 'conditional sql statement to order items by a field
  15. Dim strquery As String 'the entire query containing the select, from, where, and anything else
  16.  
  17.     ' determine the appropriate layout
  18.     If Me.LayoutOrient.Value = 1 Then
  19.         orientation = "landscape"
  20.     ElseIf Me.LayoutOrient.Value = 2 Then
  21.         orientation = "portrait"
  22.     End If
  23.  
  24.     'Debug.Print ("orientation: " & orientation)
  25.  
  26.     'set the title
  27.     title = Me.txtTitle.Value
  28.     'Debug.Print ("title: " & title)
  29.  
  30.     'get the ordering type
  31.     If Form_CustomReportPrinting.tglOrder.Value = 1 Then
  32.         ordertype = ";"
  33.     ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
  34.         ordertype = "DESC;"
  35.     End If
  36.  
  37.     'Debug.Print ("Order type: " & ordertype)
  38.  
  39.     'assign what field to sort things by
  40.     If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
  41.         orderby = ""
  42.     Else
  43.         orderby = Form_CustomReportPrinting.cmbOrders.Value
  44.     End If
  45.  
  46.     'Debug.Print ("ordered by: " & orderby)
  47.  
  48.     'give a variable the value of the table name so as to reduce typing and mistyping the table name
  49.     tablename = "[Master AUI Serials]."
  50.  
  51.     'Count number of selected records/items
  52.     selectcnt = 0
  53.     For Each selection In Form_CustomReportPrinting.lstcontents.ItemsSelected
  54.         selectcnt = selectcnt + 1
  55.     Next selection
  56.  
  57.     If selectcnt = 0 Then
  58.         MsgBox "There are no selections to show..", _
  59.         vbInformation, "Nothing selected!"
  60.         Exit Sub
  61.     End If
  62.  
  63.     'Go throught each selected 'record' (ItemsSelected) in listbox
  64.     For Each selection In Form_CustomReportPrinting.lstcontents.ItemsSelected
  65.         'Save value to appropriate text box
  66.         fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(selection) & "]" & ", "
  67.     Next selection
  68.  
  69.     'remove final comma and space
  70.     fieldnames = Left(fieldnames, Len(fieldnames) - 1)
  71.  
  72.     ' Build the from statement
  73.     qfrom = (" FROM [Master AUI Serials] ")
  74.  
  75.     'Build the select query
  76.     If orderby = "" Then
  77.         strquery = "SELECT " & fieldnames & qfrom
  78.     Else
  79.         strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
  80.     End If
  81.  
  82.     'run the query
  83.     Dim database As DAO.database
  84.     Dim qdef As DAO.QueryDef
  85.     Dim rpt As Report
  86.  
  87.     Set rpt = CreateReport
  88.     Set database = CurrentDb
  89.     Set qdef = database.QueryDefs("QueryForReport")
  90.  
  91.     qdef.SQL = strquery
  92.  
  93.     'DoCmd.OpenQuery "QueryForReport", , acEdit
  94.  
  95.     With rpt
  96.     .RecordSource = "QueryForReport"
  97.     .Caption = title
  98.  
  99.     End With
  100.  
  101.     ' Restore new report.
  102.     DoCmd.Restore
  103.     '**********************************************************
  104.     ' Main Debug Area
  105.     '**********************************************************
  106.     ' Debug.Print ("Report Title: " & title)
  107.     ' Debug.Print ("Orientation: " & orientation)
  108.     ' Debug.Print ("Ordered by: " & orderby)
  109.     ' Dim temp As Integer
  110.     ' temp = itemcount
  111.     ' While temp >= 0
  112.     ' Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
  113.     ' temp = temp - 1
  114.     ' Wend
  115.     ' Debug.Print ("SQL Select statement is:" & Chr(13))
  116.     ' Debug.Print (qselect)
  117.     ' Debug.Print (qorderby)
  118.     ' Debug.Print (strquery)
  119.     '**********************************************************
  120.     ' end debug area
  121.     '**********************************************************
  122.     'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
  123.  
  124.  
  125. End Sub
  126.  
Newbie
  
Join Date: Aug 2007
Posts: 24
#11: Aug 6 '07

re: Opening a report based on a user defined query

The code seems to be working except that when I run it selectcnt is 0 and I have no data to display.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#12: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by wexx

The code seems to be working except that when I run it selectcnt is 0 and I have no data to display.

Is CustomReportPrinting form open while you are running this?
Are you running this from CustomReportPrinting form?
Are there multiple items selected in the listbox?
Newbie
  
Join Date: Aug 2007
Posts: 24
#13: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

Is CustomReportPrinting form open while you are running this?
Are you running this from CustomReportPrinting form?
Are there multiple items selected in the listbox?

Is CustomReportPrinting form open while you are running this?
Yes, I leave this form open, but this isn't the form that has the button to create the report on it. TitleLayout is the form which contains the button.
Are you running this from CustomReportPrinting form?
This code would be running from TitleLayout

Are there multiple items selected in the listbox?
I have 2 listboxes (lstchoices) which has all the fieldnames of my table in it. The other (lstcontents) is empty and is populated by the user when they add things. they click on an item in lstchoices and click add, that item is then placed in the lstcontents listbox. lstcontents is basically like a shopping basket. The user selects items from the shelf and places them in the contents basket. when they do that the orderby combo box is made visible so they can choose which item in the basket they can order things by. This is all done in the CustomReportPrinting form.

The user clicks the next button which presents them with the TitleLayout form. On this form they are to input a title and then check whether they want the report to be landscape or portrait. Once they have made their choice they click the 'cmdcreate' command button and the code that we are working on is initiated.

For a better visual, I wanted to try and mimic how the form wizards work. You select on the wizard what table you want to get info from then you have a list box with choices and one that is blank. Then you can click the little >, >>, <, << buttons in between them to determine which items you want fields for.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#14: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by wexx

Is CustomReportPrinting form open while you are running this?

Yes, I leave this form open, but this isn't the form that has the button to create the report on it. TitleLayout is the form which contains the button.
Are you running this from CustomReportPrinting form?
This code would be running from TitleLayout
Are there multiple items selected in the listbox?
I have 2 listboxes (lstchoices) which has all the fieldnames of my table in it. The other (lstcontents) is empty and is populated by the user when they add things. they click on an item in lstchoices and click add, that item is then placed in the lstcontents listbox. lstcontents is basically like a shopping basket. The user selects items from the shelf and places them in the contents basket. when they do that the orderby combo box is made visible so they can choose which item in the basket they can order things by. This is all done in the CustomReportPrinting form.

The user clicks the next button which presents them with the TitleLayout form. On this form they are to input a title and then check whether they want the report to be landscape or portrait. Once they have made their choice they click the 'cmdcreate' command button and the code that we are working on is initiated.

For a better visual, I wanted to try and mimic how the form wizards work. You select on the wizard what table you want to get info from then you have a list box with choices and one that is blank. Then you can click the little >, >>, <, << buttons in between them to determine which items you want fields for.

OK then we don't use itemsSelected. You are using a created list based on selections in the other listbox but lstContents doesn't have anything selected so we need to use a different property.

Give me a minute to revise the code.
Newbie
  
Join Date: Aug 2007
Posts: 24
#15: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

OK then we don't use itemsSelected. You are using a created list based on selections in the other listbox but lstContents doesn't have anything selected so we need to use a different property.

Give me a minute to revise the code.

If this helps any i notice the same problem after you gave me a clue with what you were asking about. I changed lines 53 - 55 in the code you posted to me from this
Expand|Select|Wrap|Line Numbers
  1.     For Each selection In Form_CustomReportPrinting.lstcontents.ItemsSelected
  2.         selectcnt = selectcnt + 1
  3.     Next selection
  4.  
to this:
Expand|Select|Wrap|Line Numbers
  1. While Not (IsNull(Form_CustomReportPrinting.lstcontents.ItemData(selectcnt)))
  2.         selectcnt = selectcnt + 1
  3.     Wend
  4.  
That seemed to solve that issue. Then I needed to fix some parens on this line
Expand|Select|Wrap|Line Numbers
  1. fieldnames = Left(fieldnames, Len(fieldnames - 1))
Now I get a different error. Seems as though everything works but i get the error:
"The expression On Click you entered as the event property setting produced the following error: Variable required - can't assign to this expression."

This had no option to debug. So it might be because of me changing the fieldnames = left... line
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#16: Aug 6 '07

re: Opening a report based on a user defined query

OK try this ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreate_Click()
  2. Dim orientation As String 'holds the value to determine the orientation of the report
  3. Dim title As String 'holds title of report
  4. Dim orderby As String 'item to order things by
  5. Dim ordertype As String 'ascending, decending, none
  6. Dim tablename As String 'name of the master table
  7. Dim fieldnames As String 'the list of fieldnames separated by comma
  8. Dim qfrom As String 'the entire sql from statement
  9. Dim strquery As String 'the entire query containing the select, from, where, and anything else
  10.  
  11.     ' determine the appropriate layout
  12.     If Me.LayoutOrient.Value = 1 Then
  13.         orientation = "landscape"
  14.     ElseIf Me.LayoutOrient.Value = 2 Then
  15.         orientation = "portrait"
  16.     End If
  17.  
  18.     'Debug.Print ("orientation: " & orientation)
  19.  
  20.     'set the title
  21.     title = Me.txtTitle.Value
  22.     'Debug.Print ("title: " & title)
  23.  
  24.     'get the ordering type
  25.     If Form_CustomReportPrinting.tglOrder.Value = 1 Then
  26.         ordertype = ";"
  27.     ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
  28.         ordertype = "DESC;"
  29.     End If
  30.  
  31.     'Debug.Print ("Order type: " & ordertype)
  32.  
  33.     'assign what field to sort things by
  34.     If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
  35.         orderby = ""
  36.     Else
  37.         orderby = Form_CustomReportPrinting.cmbOrders.Value
  38.     End If
  39.  
  40.     'Debug.Print ("ordered by: " & orderby)
  41.  
  42.     'give a variable the value of the table name so as to reduce typing and mistyping the table name
  43.     tablename = "[Master AUI Serials]."
  44.  
  45.     If Form_CustomReportPrinting.lstcontents.ListCount = 0 Then
  46.         MsgBox "There are no selections to show..", _
  47.         vbInformation, "Nothing selected!"
  48.         Exit Sub
  49.     End If
  50.  
  51.     'Go throught each 'record' (ListCount) in listbox
  52.     For i = 0 To Form_CustomReportPrinting.lstcontents.ListCount - 1
  53.         'Save value to appropriate text box
  54.         fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(i) & "]" & ", "
  55.     Next i
  56.  
  57.     'remove final comma and space
  58.     fieldnames = Left(fieldnames, Len(fieldnames) - 1)
  59.  
  60.     ' Build the from statement
  61.     qfrom = (" FROM [Master AUI Serials] ")
  62.  
  63.     'Build the select query
  64.     If orderby = "" Then
  65.         strquery = "SELECT " & fieldnames & qfrom
  66.     Else
  67.         strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
  68.     End If
  69.  
  70.     'run the query
  71.     Dim database As DAO.database
  72.     Dim qdef As DAO.QueryDef
  73.     Dim rpt As Report
  74.  
  75.     Set rpt = CreateReport
  76.     Set database = CurrentDb
  77.     Set qdef = database.QueryDefs("QueryForReport")
  78.  
  79.     qdef.SQL = strquery
  80.  
  81.     'DoCmd.OpenQuery "QueryForReport", , acEdit
  82.  
  83.     With rpt
  84.     .RecordSource = "QueryForReport"
  85.     .Caption = title
  86.  
  87.     End With
  88.  
  89.     ' Restore new report.
  90.     DoCmd.Restore
  91.     '**********************************************************
  92.     ' Main Debug Area
  93.     '**********************************************************
  94.     ' Debug.Print ("Report Title: " & title)
  95.     ' Debug.Print ("Orientation: " & orientation)
  96.     ' Debug.Print ("Ordered by: " & orderby)
  97.     ' Dim temp As Integer
  98.     ' While temp >= 0
  99.     ' Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
  100.     ' temp = temp - 1
  101.     ' Wend
  102.     ' Debug.Print ("SQL Select statement is:" & Chr(13))
  103.     ' Debug.Print (strquery)
  104.     '**********************************************************
  105.     ' end debug area
  106.     '**********************************************************
  107.     'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
  108.  
  109.  
  110. End Sub
  111.  
Newbie
  
Join Date: Aug 2007
Posts: 24
#17: Aug 6 '07

re: Opening a report based on a user defined query

Ok I did that, query works beautifully and when i open it, it is sorted correctly and contains the fields i selected. Also, a report pops up in design view, without any text boxes or anything. I can save it and then open it except when i do that I still get 93 pages of blankness.

I checked the report properties and it says that the recordsource is QueryForReport
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#18: Aug 6 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by wexx

Ok I did that, query works beautifully and when i open it, it is sorted correctly and contains the fields i selected. Also, a report pops up in design view, without any text boxes or anything. I can save it and then open it except when i do that I still get 93 pages of blankness.

I checked the report properties and it says that the recordsource is QueryForReport

OK I never use this so I will have to play with it to see what the story is. Leave it with me.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#19: Aug 6 '07

re: Opening a report based on a user defined query

It seems you have to add all the controls, this should work.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreate_Click()
  2. Dim orientation As String 'holds the value to determine the orientation of the report
  3. Dim title As String 'holds title of report
  4. Dim orderby As String 'item to order things by
  5. Dim ordertype As String 'ascending, decending, none
  6. Dim tablename As String 'name of the master table
  7. Dim fieldnames As String 'the list of fieldnames separated by comma
  8. Dim qfrom As String 'the entire sql from statement
  9. Dim strquery As String 'the entire query containing the select, from, where, and anything else
  10.  
  11.     ' determine the appropriate layout
  12.     If Me.LayoutOrient.Value = 1 Then
  13.         orientation = "landscape"
  14.     ElseIf Me.LayoutOrient.Value = 2 Then
  15.         orientation = "portrait"
  16.     End If
  17.  
  18.     'Debug.Print ("orientation: " & orientation)
  19.  
  20.     'set the title
  21.     title = Me.txtTitle.Value
  22.     'Debug.Print ("title: " & title)
  23.  
  24.     'get the ordering type
  25.     If Form_CustomReportPrinting.tglOrder.Value = 1 Then
  26.         ordertype = ";"
  27.     ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
  28.         ordertype = "DESC;"
  29.     End If
  30.  
  31.     'Debug.Print ("Order type: " & ordertype)
  32.  
  33.     'assign what field to sort things by
  34.     If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
  35.         orderby = ""
  36.     Else
  37.         orderby = Form_CustomReportPrinting.cmbOrders.Value
  38.     End If
  39.  
  40.     'Debug.Print ("ordered by: " & orderby)
  41.  
  42.     'give a variable the value of the table name so as to reduce typing and mistyping the table name
  43.     tablename = "[Master AUI Serials]."
  44.  
  45.     If Form_CustomReportPrinting.lstcontents.ListCount = 0 Then
  46.         MsgBox "There are no selections to show..", _
  47.         vbInformation, "Nothing selected!"
  48.         Exit Sub
  49.     End If
  50.  
  51.     'Go throught each 'record' (ListCount) in listbox
  52.     For i = 0 To Form_CustomReportPrinting.lstcontents.ListCount - 1
  53.         'Save value to appropriate text box
  54.         fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(i) & "]" & ", "
  55.     Next i
  56.  
  57.     'remove final comma and space
  58.     fieldnames = Left(fieldnames, Len(fieldnames) - 1)
  59.  
  60.     ' Build the from statement
  61.     qfrom = (" FROM [Master AUI Serials] ")
  62.  
  63.     'Build the select query
  64.     If orderby = "" Then
  65.         strquery = "SELECT " & fieldnames & qfrom
  66.     Else
  67.         strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
  68.     End If
  69.  
  70. 'Create the report
  71. Dim database As DAO.database
  72. Dim rstSource As DAO.Recordset
  73. Dim qdef As DAO.QueryDef
  74. Dim fldData As DAO.Field
  75. Dim txtNew As Access.TextBox
  76. Dim lblNew As Access.Label
  77. Dim rpt As Report
  78. Dim lngTop As Long
  79. Dim lngLeft As Long
  80.  
  81.     lngLeft = 0
  82.     lngTop = 0
  83.  
  84.     ' you will have to delete the report each time or it will build up a lot of reports.
  85.     DoCmd.DeleteObject acReport, "NameOfReport"
  86.  
  87.     Set rpt = CreateReport
  88.     Set database = CurrentDb
  89.     Set qdef = database.QueryDefs("QueryForReport")
  90.  
  91.     qdef.SQL = strquery
  92.  
  93.     With rpt
  94.         .Name = "NameOfReport"
  95.         .RecordSource = "QueryForReport"
  96.         .Caption = title
  97.     End With
  98.  
  99.     ' Open recordset on specified record source.
  100.     Set rstSource = database.OpenRecordset("QueryForReport")
  101.  
  102.     ' Create corresponding label and text box controls for each field.
  103.     For Each fldData In rstSource.Fields
  104.         ' Create new text box control and size to fit data.
  105.         Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  106.         acDetail, , fldData.Name, lngLeft + 1500, lngTop)
  107.         txtNew.SizeToFit
  108.         ' Create new label control and size to fit data.
  109.         Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
  110.         txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height)
  111.         lblNew.SizeToFit
  112.         ' Increment top value for next control.
  113.         lngTop = lngTop + txtNew.Height + 25
  114.     Next
  115.  
  116.     rstSource.Close
  117.     Set rstSource = Nothing
  118.     Set qdef = Nothing
  119.  
  120.     ' Restore new report.
  121.     DoCmd.Restore
  122.  
  123.     '**********************************************************
  124.     ' Main Debug Area
  125.     '**********************************************************
  126.     ' Debug.Print ("Report Title: " & title)
  127.     ' Debug.Print ("Orientation: " & orientation)
  128.     ' Debug.Print ("Ordered by: " & orderby)
  129.     ' Dim temp As Integer
  130.     ' While temp >= 0
  131.     ' Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
  132.     ' temp = temp - 1
  133.     ' Wend
  134.     ' Debug.Print ("SQL Select statement is:" & Chr(13))
  135.     ' Debug.Print (strquery)
  136.     '**********************************************************
  137.     ' end debug area
  138.     '**********************************************************
  139.     'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
  140.  
  141.  
  142. End Sub
  143.  
Newbie
  
Join Date: Aug 2007
Posts: 24
#20: Aug 6 '07

re: Opening a report based on a user defined query

IT WORKED!!!! You are awesome. Now I just have to figure out how to get it to open in preview mode, and be formatted in a more tabular like form.

I would rather have the data look more like a query result

fld1 fld2 fld3 fld4
data data data data
data data data data


instead of like
fld1 data
fld2 data
fld3 data
fld4 data


fld1 data
fld2 data....

other than those issues everything is nice and i thank you much, if you know any tips to formatting it please let me know
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#21: Aug 6 '07

re: Opening a report based on a user defined query

If you look at the settings for lngTop and lngLeft variables, they are setting the position of the textboxes and labels. Try playing around with them.
Newbie
  
Join Date: Aug 2007
Posts: 24
#22: Aug 7 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

If you look at the settings for lngTop and lngLeft variables, they are setting the position of the textboxes and labels. Try playing around with them.

Sweet!, It worked nicely. Where does the code go in order to open the report in preview mode instead of design view? Right now I can only seem to get it to show up in design view.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#23: Aug 7 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by wexx

Sweet!, It worked nicely. Where does the code go in order to open the report in preview mode instead of design view? Right now I can only seem to get it to show up in design view.

Try changing docmd.restore to a docmd.open report and see if that works.
Newbie
  
Join Date: Aug 2007
Posts: 24
#24: Aug 7 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

Try changing docmd.restore to a docmd.open report and see if that works.

Oh ok that seemed to work. I wasn't sure if I was able to use that with the code that you sent me.

I made a template file to help with some of the formatting, I also created an unbound textbox (txtTitleHeader), hoping that I would be able to change the value of it before I open the report. I'm just having some trouble referencing it from inside the cmdCreate button. I'm going to put it into the report code to see if I can get any results.
Newbie
  
Join Date: Aug 2007
Posts: 24
#25: Aug 7 '07

re: Opening a report based on a user defined query

I tried setting the value of the txtTitleHeader on the report load but it was unsuccessful.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#26: Aug 7 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by wexx

I tried setting the value of the txtTitleHeader on the report load but it was unsuccessful.

Let me have a look at it and get back to you.
Newbie
  
Join Date: Aug 2007
Posts: 24
#27: Aug 7 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

Let me have a look at it and get back to you.

Here is the code for the report open:
(NOTE: the unbound textbox is named txtHeaderTitle)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.     Dim htitle As String
  3.     htitle = Form_TitleLayout.txtTitle.Value
  4.     Me.txtHeaderTitle.Value = htitle
  5.  
  6. End Sub
  7.  
The code for the cmdCreate button: (modified from the previous code you gave me)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreate_Click()
  2.     Dim orientation     As String 'holds the value to determine the orientation of the report
  3.     Dim title           As String 'holds title of report
  4.     Dim orderby         As String 'item to order things by
  5.     Dim ordertype       As String 'ascending, decending, none
  6.     Dim tablename       As String 'name of the master table
  7.     Dim fieldnames      As String 'the list of fieldnames separated by comma
  8.     Dim qfrom           As String 'the entire sql from statement
  9.     Dim strquery        As String 'the entire query containing the select, from, where, and anything else
  10.  
  11.     'Create the report
  12.     Dim database        As DAO.database
  13.     Dim rstSource       As DAO.recordset
  14.     Dim qdef            As DAO.QueryDef
  15.     Dim fldData         As DAO.Field
  16.     Dim txtNew          As Access.TextBox
  17.     Dim lblNew          As Access.Label
  18.     Dim rpt             As Report
  19.     Dim lngTop          As Long
  20.     Dim lngLeft         As Long
  21.     Dim lngwidth        As Long
  22.     Dim lngheight       As Long
  23.  
  24.  
  25.     ' determine the appropriate layout
  26.     If Me.LayoutOrient.Value = 1 Then
  27.         orientation = "landscape"
  28.     ElseIf Me.LayoutOrient.Value = 2 Then
  29.         orientation = "portrait"
  30.     End If
  31.  
  32.     'set the title
  33.     title = Me.txtTitle.Value
  34.  
  35.     'get the ordering type
  36.     If Form_CustomReportPrinting.tglOrder.Value = 1 Then
  37.         ordertype = ";"
  38.     ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
  39.         ordertype = "DESC;"
  40.     End If
  41.  
  42.     'assign what field to sort things by
  43.     If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
  44.         orderby = ""
  45.     Else
  46.         orderby = Form_CustomReportPrinting.cmbOrders.Value
  47.     End If
  48.  
  49.     'give a variable the value of the table name so as to reduce typing and mistyping the table name
  50.     tablename = "[Master AUI Serials]."
  51.  
  52.     If Form_CustomReportPrinting.lstcontents.ListCount = 0 Then
  53.         MsgBox "There are no selections to show..", _
  54.         vbInformation, "Nothing selected!"
  55.         Exit Sub
  56.     End If
  57.  
  58.     'Go through each 'record' (ListCount) in listbox
  59.     For i = 0 To Form_CustomReportPrinting.lstcontents.ListCount - 1
  60.         'Save value to appropriate text box
  61.         fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(i) & "]" & ", "
  62.     Next i
  63.  
  64.     'remove final comma and space
  65.     fieldnames = Left(fieldnames, Len(fieldnames) - 2)
  66.  
  67.     ' Build the from statement
  68.     qfrom = (" FROM [Master AUI Serials] ")
  69.  
  70.     'Build the select query
  71.     If orderby = "" Then
  72.         strquery = "SELECT " & fieldnames & qfrom
  73.     Else
  74.         strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
  75.     End If
  76.  
  77.  
  78.     lngLeft = 0
  79.     lngTop = 0
  80.     lngwidth = 0
  81.     lngheight = 0
  82.  
  83.     ' you will have to delete the report each time or it will build up a lot of reports.
  84.     ' DoCmd.DeleteObject acReport, "NameOfReport"
  85.  
  86.  
  87.     Set database = CurrentDb
  88.     Set qdef = database.QueryDefs("QueryForReport")
  89.     Set rpt = CreateReport
  90.  
  91.     qdef.SQL = strquery
  92.  
  93.     With rpt
  94.         .RecordSource = "QueryForReport"
  95.         .Caption = title
  96.     End With
  97.  
  98.     ' Open recordset on specified record source.
  99.     Set rstSource = database.OpenRecordset("QueryForReport")
  100.  
  101.  
  102.     ' Create corresponding label and text box controls for each field.
  103.     For Each fldData In rstSource.Fields
  104.         ' Create new text box control and size to fit data.
  105.         Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail, , _
  106.         fldData.Name, lngLeft, lngTop, lngwidth + 1500)
  107.         txtNew.SizeToFit
  108.         lngLeft = lngLeft + txtNew.Width + 25
  109.     Next
  110.     lngLeft = 0
  111.  
  112.     For Each fldData In rstSource.Fields
  113.         ' Create new label control and size to fit data.
  114.         Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _
  115.         , fldData.Name, lngLeft, lngTop, txtNew.Width, txtNew.Height)
  116.  
  117.         lblNew.SizeToFit
  118.  
  119.         ' Increment top value for next control.
  120.         lngLeft = lngLeft + txtNew.Width + 25
  121.     Next
  122.  
  123.     rstSource.Close
  124.  
  125.     DoCmd.OpenReport rpt.Name, acViewPreview
  126.  
  127.     Set rstSource = Nothing
  128.     Set qdef = Nothing
  129.  
  130.     ' Restore new report.
  131.  
  132. End Sub
  133.  
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#28: Aug 7 '07

re: Opening a report based on a user defined query

Use a label instead of a textbox and add it at the same time as the other textboxes and labels. You can use something like the following code.

Expand|Select|Wrap|Line Numbers
  1.     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  2.     acPageHeader, , "Title", lngLeft, lngTop)
  3.     lblNew.FontBold = True
  4.     lblNew.FontSize = 12
  5.     lblNew.SizeToFit
  6.  
Newbie
  
Join Date: Aug 2007
Posts: 24
#29: Aug 8 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

Use a label instead of a textbox and add it at the same time as the other textboxes and labels. You can use something like the following code.

Expand|Select|Wrap|Line Numbers
  1.     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  2.     acPageHeader, , "Title", lngLeft, lngTop)
  3.     lblNew.FontBold = True
  4.     lblNew.FontSize = 12
  5.     lblNew.SizeToFit
  6.  

I tried to set up a template so that when I wanted the user to pick landscape view or portrait view they could. I was going about making my template and put a picture on it(company logo) which was fine and showed up when i actually ran the code we've been working on. However, I noticed the page number and date that I had also put on the bottom 2 corners wasn't showing up.

I used the tools to do it and verified the code to work by previewing it. Problem is that when i run the code it seems to overwrite the date and time. I even tried something like this: (which unfortunately didn't work)
Expand|Select|Wrap|Line Numbers
  1. dim repDate as string
  2. repDate = Now()
  3. debug.print(repDate) ' shows date in immediate pane
  4. Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageFooter, , repDate, lngLeft, , lngwidth)
  5.  
Any idea on how to get the page number, number of pages, and date on the bottom of each page using VBA?
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#30: Aug 8 '07

re: Opening a report based on a user defined query

Try this ...

Expand|Select|Wrap|Line Numbers
  1. Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageFooter, , Now(), lngLeft, , lngwidth)
  2.  
I'll have to do some more checking to see about pages.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#31: Aug 8 '07

re: Opening a report based on a user defined query

OK this should take care of page numbers

Expand|Select|Wrap|Line Numbers
  1.     Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  2.     acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", lngLeft, , lngWidth)
  3.  
Newbie
  
Join Date: Aug 2007
Posts: 24
#32: Aug 8 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by mmccarthy

OK this should take care of page numbers

Expand|Select|Wrap|Line Numbers
  1.     Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  2.     acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", lngLeft, , lngWidth)
  3.  

Oh cool thanks the date and page number thing were wonderfully. Thank you so much for being patient and answering my questions I truly appreciate it!
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,865
#33: Aug 8 '07

re: Opening a report based on a user defined query

Quote:

Originally Posted by wexx

Oh cool thanks the date and page number thing were wonderfully. Thank you so much for being patient and answering my questions I truly appreciate it!

You're welcome.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,295 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved