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: -
Dim database As DAO.database
-
Dim qdef As DAO.QueryDef
-
-
Set database = CurrentDb
-
Set qdef = database.QueryDefs("QueryForReport")
-
-
qdef.SQL = query
-
-
DoCmd.OpenQuery "QueryForReport", , acEdit
-
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. -
DoCmd.OpenReport "UserDefinedReport", acViewPreview, query
-
That opened a report but a blank one with no information in it. I want it based on the SQL string that query holds.
32 2641
OK you can open a dynamic report but the formatting of the report is not great. -
Sub NewReport()
-
Dim rpt As Report
-
-
' Return variable of data type Report pointing to
-
' new Report object.
-
Set rpt = CreateReport
-
' Set properties for new report.
-
With rpt
-
.RecordSource = "QueryForReport"
-
.Caption = "Report Title"
-
End With
-
' Restore new report.
-
DoCmd.Restore
-
End Sub
-
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
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.
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?
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)>
Also can you tell us where query is defined and can you post the sql of the 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)>
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?
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. -
Private Sub cmdCreate_Click()
-
Dim orientation As String 'holds the value to determine the orientation of the report
-
Dim title As String 'holds title of report
-
Dim orderby As String 'item to order things by
-
Dim ordertype As String 'ascending, decending, none
-
Dim itemcount As Integer 'variable to hold the original value of how many items 1-6 were in the contents
-
'list box
-
-
Dim selections(5) As String 'array of what items to display in the report
-
Dim selectcnt As Integer 'count of the number of those items
-
-
Dim tablename As String 'name of the master table
-
Dim fieldnames(5) As String 'the selections(array) formatted with [] to make it into a suitable sql statement
-
-
Dim qselect As String 'the entire sql select statement
-
Dim qfrom As String 'the entire sql from statement
-
Dim qorderby As String 'conditional sql statement to order items by a field
-
Dim strquery As String 'the entire query containing the select, from, where, and anything else
-
-
' determine the appropriate layout
-
If Me.LayoutOrient.Value = 1 Then
-
orientation = "landscape"
-
ElseIf Me.LayoutOrient.Value = 2 Then
-
orientation = "portrait"
-
End If
-
-
'Debug.Print ("orientation: " & orientation)
-
-
'set the title
-
title = Me.txtTitle.Value
-
'Debug.Print ("title: " & title)
-
-
'get the ordering type
-
If Form_CustomReportPrinting.tglOrder.Value = 1 Then
-
ordertype = ";"
-
ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
-
ordertype = "DESC;"
-
End If
-
-
'Debug.Print ("Order type: " & ordertype)
-
-
'assign what field to sort things by
-
If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
-
orderby = ""
-
Else
-
orderby = Form_CustomReportPrinting.cmbOrders.Value
-
End If
-
-
'Debug.Print ("ordered by: " & orderby)
-
-
selectcnt = 0
-
'fill in the selections array with data and get the count
-
While Not (IsNull(Form_CustomReportPrinting.lstcontents.ItemData(selectcnt)))
-
selections(selectcnt) = Form_CustomReportPrinting.lstcontents.ItemData(selectcnt)
-
'Debug.Print (selections(selectcnt))
-
selectcnt = selectcnt + 1
-
Wend
-
-
selectcnt = selectcnt - 1 ' the count went one too high after finishing so fix it
-
itemcount = selectcnt
-
-
'give a variable the value of the table name so as to reduce typing and mistyping the table name
-
tablename = "[Master AUI Serials]."
-
-
'format the fieldnames properly
-
While selectcnt >= 0
-
fieldnames(selectcnt) = (tablename & "[" & selections(selectcnt) & "]")
-
'Debug.Print (fieldnames(selectcnt))
-
selectcnt = selectcnt - 1
-
Wend
-
selectcnt = itemcount
-
-
Select Case itemcount 'build the select statement
-
Case 0
-
qselect = ("SELECT " & fieldnames(0) & Chr(13))
-
Case 1
-
qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & Chr(13))
-
Case 2
-
qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) & Chr(13))
-
Case 3
-
qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) & "," & fieldnames(3) & Chr(13))
-
Case 4
-
qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) _
-
& "," & fieldnames(3) & "," & fieldnames(4) & Chr(13))
-
Case 5
-
qselect = ("SELECT " & fieldnames(0) & "," & fieldnames(1) & "," & fieldnames(2) _
-
& "," & fieldnames(3) & "," & fieldnames(4) & "," & fieldnames(5) & Chr(13))
-
End Select
-
-
' Build the from statement
-
qfrom = ("FROM [Master AUI Serials]" & Chr(13))
-
-
If orderby = "" Then
-
qorderby = ("" & Chr(13))
-
Else
-
qorderby = ("ORDER BY " & tablename & "[" & orderby & "]" & Chr(13))
-
End If
-
-
strquery = qselect & qfrom & qorderby
-
-
'run the query
-
Dim database As DAO.database
-
Dim qdef As DAO.QueryDef
-
Dim rpt As Report
-
-
Set rpt = CreateReport
-
Set database = CurrentDb
-
Set qdef = database.QueryDefs("QueryForReport")
-
-
qdef.SQL = strquery
-
-
'DoCmd.OpenQuery "QueryForReport", , acEdit
-
-
With rpt
-
.RecordSource = "QueryForReport"
-
.Caption = title
-
-
End With
-
-
' Restore new report.
-
DoCmd.Restore
-
'**********************************************************
-
' Main Debug Area
-
'**********************************************************
-
' Debug.Print ("Report Title: " & title)
-
' Debug.Print ("Orientation: " & orientation)
-
' Debug.Print ("Ordered by: " & orderby)
-
' Dim temp As Integer
-
' temp = itemcount
-
' While temp >= 0
-
' Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
-
' temp = temp - 1
-
' Wend
-
' Debug.Print ("SQL Select statement is:" & Chr(13))
-
' Debug.Print (qselect)
-
' Debug.Print (qorderby)
-
' Debug.Print (strquery)
-
'**********************************************************
-
' end debug area
-
'**********************************************************
-
'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
-
-
End Sub
-
Try this instead ... -
Private Sub cmdCreate_Click()
-
Dim orientation As String 'holds the value to determine the orientation of the report
-
Dim title As String 'holds title of report
-
Dim orderby As String 'item to order things by
-
Dim ordertype As String 'ascending, decending, none
-
Dim itemcount As Integer 'variable to hold the original value of how many items 1-6 were in the contents
-
'list box
-
Dim selection As Variant 'variable to hold current selected value
-
Dim selectcnt As Integer 'count of the number of those items
-
Dim tablename As String 'name of the master table
-
Dim fieldnames As String 'the list of fieldnames separated by comma
-
Dim qselect As String 'the entire sql select statement
-
Dim qfrom As String 'the entire sql from statement
-
Dim qorderby As String 'conditional sql statement to order items by a field
-
Dim strquery As String 'the entire query containing the select, from, where, and anything else
-
-
' determine the appropriate layout
-
If Me.LayoutOrient.Value = 1 Then
-
orientation = "landscape"
-
ElseIf Me.LayoutOrient.Value = 2 Then
-
orientation = "portrait"
-
End If
-
-
'Debug.Print ("orientation: " & orientation)
-
-
'set the title
-
title = Me.txtTitle.Value
-
'Debug.Print ("title: " & title)
-
-
'get the ordering type
-
If Form_CustomReportPrinting.tglOrder.Value = 1 Then
-
ordertype = ";"
-
ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
-
ordertype = "DESC;"
-
End If
-
-
'Debug.Print ("Order type: " & ordertype)
-
-
'assign what field to sort things by
-
If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
-
orderby = ""
-
Else
-
orderby = Form_CustomReportPrinting.cmbOrders.Value
-
End If
-
-
'Debug.Print ("ordered by: " & orderby)
-
-
'give a variable the value of the table name so as to reduce typing and mistyping the table name
-
tablename = "[Master AUI Serials]."
-
-
'Count number of selected records/items
-
selectcnt = 0
-
For Each selection In Form_CustomReportPrinting.lstcontents.ItemsSelected
-
selectcnt = selectcnt + 1
-
Next selection
-
-
If selectcnt = 0 Then
-
MsgBox "There are no selections to show..", _
-
vbInformation, "Nothing selected!"
-
Exit Sub
-
End If
-
-
'Go throught each selected 'record' (ItemsSelected) in listbox
-
For Each selection In Form_CustomReportPrinting.lstcontents.ItemsSelected
-
'Save value to appropriate text box
-
fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(selection) & "]" & ", "
-
Next selection
-
-
'remove final comma and space
-
fieldnames = Left(fieldnames, Len(fieldnames) - 1)
-
-
' Build the from statement
-
qfrom = (" FROM [Master AUI Serials] ")
-
-
'Build the select query
-
If orderby = "" Then
-
strquery = "SELECT " & fieldnames & qfrom
-
Else
-
strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
-
End If
-
-
'run the query
-
Dim database As DAO.database
-
Dim qdef As DAO.QueryDef
-
Dim rpt As Report
-
-
Set rpt = CreateReport
-
Set database = CurrentDb
-
Set qdef = database.QueryDefs("QueryForReport")
-
-
qdef.SQL = strquery
-
-
'DoCmd.OpenQuery "QueryForReport", , acEdit
-
-
With rpt
-
.RecordSource = "QueryForReport"
-
.Caption = title
-
-
End With
-
-
' Restore new report.
-
DoCmd.Restore
-
'**********************************************************
-
' Main Debug Area
-
'**********************************************************
-
' Debug.Print ("Report Title: " & title)
-
' Debug.Print ("Orientation: " & orientation)
-
' Debug.Print ("Ordered by: " & orderby)
-
' Dim temp As Integer
-
' temp = itemcount
-
' While temp >= 0
-
' Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
-
' temp = temp - 1
-
' Wend
-
' Debug.Print ("SQL Select statement is:" & Chr(13))
-
' Debug.Print (qselect)
-
' Debug.Print (qorderby)
-
' Debug.Print (strquery)
-
'**********************************************************
-
' end debug area
-
'**********************************************************
-
'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
-
-
-
End Sub
-
The code seems to be working except that when I run it selectcnt is 0 and I have no data to display.
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?
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.
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.
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 -
For Each selection In Form_CustomReportPrinting.lstcontents.ItemsSelected
-
selectcnt = selectcnt + 1
-
Next selection
-
to this: -
While Not (IsNull(Form_CustomReportPrinting.lstcontents.ItemData(selectcnt)))
-
selectcnt = selectcnt + 1
-
Wend
-
That seemed to solve that issue. Then I needed to fix some parens on this line - 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
OK try this ... -
Private Sub cmdCreate_Click()
-
Dim orientation As String 'holds the value to determine the orientation of the report
-
Dim title As String 'holds title of report
-
Dim orderby As String 'item to order things by
-
Dim ordertype As String 'ascending, decending, none
-
Dim tablename As String 'name of the master table
-
Dim fieldnames As String 'the list of fieldnames separated by comma
-
Dim qfrom As String 'the entire sql from statement
-
Dim strquery As String 'the entire query containing the select, from, where, and anything else
-
-
' determine the appropriate layout
-
If Me.LayoutOrient.Value = 1 Then
-
orientation = "landscape"
-
ElseIf Me.LayoutOrient.Value = 2 Then
-
orientation = "portrait"
-
End If
-
-
'Debug.Print ("orientation: " & orientation)
-
-
'set the title
-
title = Me.txtTitle.Value
-
'Debug.Print ("title: " & title)
-
-
'get the ordering type
-
If Form_CustomReportPrinting.tglOrder.Value = 1 Then
-
ordertype = ";"
-
ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
-
ordertype = "DESC;"
-
End If
-
-
'Debug.Print ("Order type: " & ordertype)
-
-
'assign what field to sort things by
-
If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
-
orderby = ""
-
Else
-
orderby = Form_CustomReportPrinting.cmbOrders.Value
-
End If
-
-
'Debug.Print ("ordered by: " & orderby)
-
-
'give a variable the value of the table name so as to reduce typing and mistyping the table name
-
tablename = "[Master AUI Serials]."
-
-
If Form_CustomReportPrinting.lstcontents.ListCount = 0 Then
-
MsgBox "There are no selections to show..", _
-
vbInformation, "Nothing selected!"
-
Exit Sub
-
End If
-
-
'Go throught each 'record' (ListCount) in listbox
-
For i = 0 To Form_CustomReportPrinting.lstcontents.ListCount - 1
-
'Save value to appropriate text box
-
fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(i) & "]" & ", "
-
Next i
-
-
'remove final comma and space
-
fieldnames = Left(fieldnames, Len(fieldnames) - 1)
-
-
' Build the from statement
-
qfrom = (" FROM [Master AUI Serials] ")
-
-
'Build the select query
-
If orderby = "" Then
-
strquery = "SELECT " & fieldnames & qfrom
-
Else
-
strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
-
End If
-
-
'run the query
-
Dim database As DAO.database
-
Dim qdef As DAO.QueryDef
-
Dim rpt As Report
-
-
Set rpt = CreateReport
-
Set database = CurrentDb
-
Set qdef = database.QueryDefs("QueryForReport")
-
-
qdef.SQL = strquery
-
-
'DoCmd.OpenQuery "QueryForReport", , acEdit
-
-
With rpt
-
.RecordSource = "QueryForReport"
-
.Caption = title
-
-
End With
-
-
' Restore new report.
-
DoCmd.Restore
-
'**********************************************************
-
' Main Debug Area
-
'**********************************************************
-
' Debug.Print ("Report Title: " & title)
-
' Debug.Print ("Orientation: " & orientation)
-
' Debug.Print ("Ordered by: " & orderby)
-
' Dim temp As Integer
-
' While temp >= 0
-
' Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
-
' temp = temp - 1
-
' Wend
-
' Debug.Print ("SQL Select statement is:" & Chr(13))
-
' Debug.Print (strquery)
-
'**********************************************************
-
' end debug area
-
'**********************************************************
-
'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
-
-
-
End Sub
-
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 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.
It seems you have to add all the controls, this should work. -
Private Sub cmdCreate_Click()
-
Dim orientation As String 'holds the value to determine the orientation of the report
-
Dim title As String 'holds title of report
-
Dim orderby As String 'item to order things by
-
Dim ordertype As String 'ascending, decending, none
-
Dim tablename As String 'name of the master table
-
Dim fieldnames As String 'the list of fieldnames separated by comma
-
Dim qfrom As String 'the entire sql from statement
-
Dim strquery As String 'the entire query containing the select, from, where, and anything else
-
-
' determine the appropriate layout
-
If Me.LayoutOrient.Value = 1 Then
-
orientation = "landscape"
-
ElseIf Me.LayoutOrient.Value = 2 Then
-
orientation = "portrait"
-
End If
-
-
'Debug.Print ("orientation: " & orientation)
-
-
'set the title
-
title = Me.txtTitle.Value
-
'Debug.Print ("title: " & title)
-
-
'get the ordering type
-
If Form_CustomReportPrinting.tglOrder.Value = 1 Then
-
ordertype = ";"
-
ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
-
ordertype = "DESC;"
-
End If
-
-
'Debug.Print ("Order type: " & ordertype)
-
-
'assign what field to sort things by
-
If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
-
orderby = ""
-
Else
-
orderby = Form_CustomReportPrinting.cmbOrders.Value
-
End If
-
-
'Debug.Print ("ordered by: " & orderby)
-
-
'give a variable the value of the table name so as to reduce typing and mistyping the table name
-
tablename = "[Master AUI Serials]."
-
-
If Form_CustomReportPrinting.lstcontents.ListCount = 0 Then
-
MsgBox "There are no selections to show..", _
-
vbInformation, "Nothing selected!"
-
Exit Sub
-
End If
-
-
'Go throught each 'record' (ListCount) in listbox
-
For i = 0 To Form_CustomReportPrinting.lstcontents.ListCount - 1
-
'Save value to appropriate text box
-
fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(i) & "]" & ", "
-
Next i
-
-
'remove final comma and space
-
fieldnames = Left(fieldnames, Len(fieldnames) - 1)
-
-
' Build the from statement
-
qfrom = (" FROM [Master AUI Serials] ")
-
-
'Build the select query
-
If orderby = "" Then
-
strquery = "SELECT " & fieldnames & qfrom
-
Else
-
strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
-
End If
-
-
'Create the report
-
Dim database As DAO.database
-
Dim rstSource As DAO.Recordset
-
Dim qdef As DAO.QueryDef
-
Dim fldData As DAO.Field
-
Dim txtNew As Access.TextBox
-
Dim lblNew As Access.Label
-
Dim rpt As Report
-
Dim lngTop As Long
-
Dim lngLeft As Long
-
-
lngLeft = 0
-
lngTop = 0
-
-
' you will have to delete the report each time or it will build up a lot of reports.
-
DoCmd.DeleteObject acReport, "NameOfReport"
-
-
Set rpt = CreateReport
-
Set database = CurrentDb
-
Set qdef = database.QueryDefs("QueryForReport")
-
-
qdef.SQL = strquery
-
-
With rpt
-
.Name = "NameOfReport"
-
.RecordSource = "QueryForReport"
-
.Caption = title
-
End With
-
-
' Open recordset on specified record source.
-
Set rstSource = database.OpenRecordset("QueryForReport")
-
-
' Create corresponding label and text box controls for each field.
-
For Each fldData In rstSource.Fields
-
' Create new text box control and size to fit data.
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acDetail, , fldData.Name, lngLeft + 1500, lngTop)
-
txtNew.SizeToFit
-
' Create new label control and size to fit data.
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
-
txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height)
-
lblNew.SizeToFit
-
' Increment top value for next control.
-
lngTop = lngTop + txtNew.Height + 25
-
Next
-
-
rstSource.Close
-
Set rstSource = Nothing
-
Set qdef = Nothing
-
-
' Restore new report.
-
DoCmd.Restore
-
-
'**********************************************************
-
' Main Debug Area
-
'**********************************************************
-
' Debug.Print ("Report Title: " & title)
-
' Debug.Print ("Orientation: " & orientation)
-
' Debug.Print ("Ordered by: " & orderby)
-
' Dim temp As Integer
-
' While temp >= 0
-
' Debug.Print ("Field Name " & temp & " :" & fieldnames(temp))
-
' temp = temp - 1
-
' Wend
-
' Debug.Print ("SQL Select statement is:" & Chr(13))
-
' Debug.Print (strquery)
-
'**********************************************************
-
' end debug area
-
'**********************************************************
-
'DoCmd.OpenReport "UserDefinedReport", acViewPreview, "QueryForReport"
-
-
-
End Sub
-
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
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.
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.
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.
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.
I tried setting the value of the txtTitleHeader on the report load but it was unsuccessful.
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.
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) -
Private Sub Report_Open(Cancel As Integer)
-
Dim htitle As String
-
htitle = Form_TitleLayout.txtTitle.Value
-
Me.txtHeaderTitle.Value = htitle
-
-
End Sub
-
The code for the cmdCreate button: (modified from the previous code you gave me) -
Private Sub cmdCreate_Click()
-
Dim orientation As String 'holds the value to determine the orientation of the report
-
Dim title As String 'holds title of report
-
Dim orderby As String 'item to order things by
-
Dim ordertype As String 'ascending, decending, none
-
Dim tablename As String 'name of the master table
-
Dim fieldnames As String 'the list of fieldnames separated by comma
-
Dim qfrom As String 'the entire sql from statement
-
Dim strquery As String 'the entire query containing the select, from, where, and anything else
-
-
'Create the report
-
Dim database As DAO.database
-
Dim rstSource As DAO.recordset
-
Dim qdef As DAO.QueryDef
-
Dim fldData As DAO.Field
-
Dim txtNew As Access.TextBox
-
Dim lblNew As Access.Label
-
Dim rpt As Report
-
Dim lngTop As Long
-
Dim lngLeft As Long
-
Dim lngwidth As Long
-
Dim lngheight As Long
-
-
-
' determine the appropriate layout
-
If Me.LayoutOrient.Value = 1 Then
-
orientation = "landscape"
-
ElseIf Me.LayoutOrient.Value = 2 Then
-
orientation = "portrait"
-
End If
-
-
'set the title
-
title = Me.txtTitle.Value
-
-
'get the ordering type
-
If Form_CustomReportPrinting.tglOrder.Value = 1 Then
-
ordertype = ";"
-
ElseIf Form_CustomReportPrinting.tglOrder.Value = 2 Then
-
ordertype = "DESC;"
-
End If
-
-
'assign what field to sort things by
-
If IsNull(Form_CustomReportPrinting.cmbOrders.Value) Then
-
orderby = ""
-
Else
-
orderby = Form_CustomReportPrinting.cmbOrders.Value
-
End If
-
-
'give a variable the value of the table name so as to reduce typing and mistyping the table name
-
tablename = "[Master AUI Serials]."
-
-
If Form_CustomReportPrinting.lstcontents.ListCount = 0 Then
-
MsgBox "There are no selections to show..", _
-
vbInformation, "Nothing selected!"
-
Exit Sub
-
End If
-
-
'Go through each 'record' (ListCount) in listbox
-
For i = 0 To Form_CustomReportPrinting.lstcontents.ListCount - 1
-
'Save value to appropriate text box
-
fieldnames = fieldnames & tablename & "[" & Form_CustomReportPrinting.lstcontents.ItemData(i) & "]" & ", "
-
Next i
-
-
'remove final comma and space
-
fieldnames = Left(fieldnames, Len(fieldnames) - 2)
-
-
' Build the from statement
-
qfrom = (" FROM [Master AUI Serials] ")
-
-
'Build the select query
-
If orderby = "" Then
-
strquery = "SELECT " & fieldnames & qfrom
-
Else
-
strquery = "SELECT " & fieldnames & qfrom & "ORDER BY " & tablename & "[" & orderby & "]"
-
End If
-
-
-
lngLeft = 0
-
lngTop = 0
-
lngwidth = 0
-
lngheight = 0
-
-
' you will have to delete the report each time or it will build up a lot of reports.
-
' DoCmd.DeleteObject acReport, "NameOfReport"
-
-
-
Set database = CurrentDb
-
Set qdef = database.QueryDefs("QueryForReport")
-
Set rpt = CreateReport
-
-
qdef.SQL = strquery
-
-
With rpt
-
.RecordSource = "QueryForReport"
-
.Caption = title
-
End With
-
-
' Open recordset on specified record source.
-
Set rstSource = database.OpenRecordset("QueryForReport")
-
-
-
' Create corresponding label and text box controls for each field.
-
For Each fldData In rstSource.Fields
-
' Create new text box control and size to fit data.
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail, , _
-
fldData.Name, lngLeft, lngTop, lngwidth + 1500)
-
txtNew.SizeToFit
-
lngLeft = lngLeft + txtNew.Width + 25
-
Next
-
lngLeft = 0
-
-
For Each fldData In rstSource.Fields
-
' Create new label control and size to fit data.
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _
-
, fldData.Name, lngLeft, lngTop, txtNew.Width, txtNew.Height)
-
-
lblNew.SizeToFit
-
-
' Increment top value for next control.
-
lngLeft = lngLeft + txtNew.Width + 25
-
Next
-
-
rstSource.Close
-
-
DoCmd.OpenReport rpt.Name, acViewPreview
-
-
Set rstSource = Nothing
-
Set qdef = Nothing
-
-
' Restore new report.
-
-
End Sub
-
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. -
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
-
acPageHeader, , "Title", lngLeft, lngTop)
-
lblNew.FontBold = True
-
lblNew.FontSize = 12
-
lblNew.SizeToFit
-
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. -
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
-
acPageHeader, , "Title", lngLeft, lngTop)
-
lblNew.FontBold = True
-
lblNew.FontSize = 12
-
lblNew.SizeToFit
-
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) -
dim repDate as string
-
repDate = Now()
-
debug.print(repDate) ' shows date in immediate pane
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageFooter, , repDate, lngLeft, , lngwidth)
-
Any idea on how to get the page number, number of pages, and date on the bottom of each page using VBA?
Try this ... -
Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageFooter, , Now(), lngLeft, , lngwidth)
-
I'll have to do some more checking to see about pages.
OK this should take care of page numbers -
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", lngLeft, , lngWidth)
-
OK this should take care of page numbers -
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", lngLeft, , lngWidth)
-
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!
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Richard Hollenbeck |
last post by:
I'm building a table of grades report based on a crosstab query where
students are in the rows and the activities are in the columns. The grades
are in the body where the rows and columns...
|
by: No Spam |
last post by:
Dear Access 2000 users,
I have a crosstab query that puts together certain information
perfectly. It has a criteria that is based on a form that limits how
many columns are returned based on...
|
by: Richard Hollenbeck |
last post by:
The following query takes about one second to execute with less than 1,000
records, but the report that's based on it takes from 15-30 seconds to
format and display. That's frustrating for both me...
|
by: Richard Hollenbeck |
last post by:
I have a crosstab query that shows all the scores of all the activities of
all the students in all courses, with the students being in the rows and the
activities being in the columns and the...
|
by: Bill nguyen |
last post by:
This question was posted in vb.Crystal but never got replied:
I'm looking for a way to increase the page length limit in CR reports.
Currently, the page break will occur based on the printer you...
|
by: Tim Hunter |
last post by:
Hi,
I am running Access 2003 on WinXP.
Many of you will think I'm nuts when i explain what I am experiencing. I
have a report that is based on a crosstab query and it works fine. The
user has...
|
by: sfjnet |
last post by:
Hi
I am relatively new to Access so I hope I am not embarrassing myself with this question.
I have created a database showing contract details (contract number, contractors, dates, other...
|
by: karanj |
last post by:
Hi all,
I'm facing an issue with Access that I haven't been able to find a solution for, despite hours of googling, so I hope someone here can help!
I've got a report - rptClientsByCountry -...
|
by: MMcCarthy |
last post by:
This article contains three different approaches to creating dynamic reports.
Dynamic report from user defined SQL SELECT statement (Author: mmccarthy)
Dynamic report designed to be used with...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |