Subform, Report Problem
Apr 27, 2005
Hi,
I am having trouble previewing a report using a command button located within a subform.
Each record in the subform contains a command button. When this button is clicked, I would like to generate the report preview containing the main form data for the current record AND only the subform data for the current subform record (i.e. the subform record for which the command button is clicked).
Additional info:
-The form (frmA) and subform (sfrmB) are linked by [ID]
-Data are referenced directly from their tables
-Following is the Command button OnClick event code that is currently being used to obtain data for the current main form record (needs to be modified to only retrieve subform data for the current subform record):
Dim stDocName As String
DoCmd.RunCommand acCmdSaveRecord
stDocName = "rptInvoice"
DoCmd.OpenReport stDocName, acPreview, , "sfrmBtbl_ID = Forms!frmA!ID"
I can get the report to preview the current main record only, but I do not know how to limit the subform data.
I would really appreciate some help.
View Replies
ADVERTISEMENT
Jul 5, 2007
I have a form with a subform in it - the forum is a search forum and the subform is the output from my search. I would like to run a report on the search result and do not know what is the best process for this???
Should I create a temp table with search results?? if so how do I go about this?
Private Sub btnSearch_Click()
' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter
' Requery the subform
Me.frmsubClients.Requery
End Sub
I tired this, but it does not work, thus I think I need to create a temp table or something?
Private Sub cmdReport_Click()
'Open report
DoCmd.OpenReport "rptClientData", acViewPreview, , frmsubClients
End Sub
-------------------------------------------------------------
If required my BuildFilter is:-
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter
' Check for LIKE Reference
If Me.txtReference > "" Then
varWhere = varWhere & "[Reference] LIKE """ & Me.txtReference & "*"" AND "
End If
' Check for LIKE Orig
If Me.txtOrig > "" Then
varWhere = varWhere & "[Orig] LIKE """ & Me.txtOrig & "*"" AND "
End If
' Check for min Uniq
If Me.txtUniq > "" Then
varWhere = varWhere & "[Uniq] LIKE " & Me.txtUniq & " AND "
End If
' Check for max PaymentDate
If Me.txtPaymentDate > "" Then
varWhere = varWhere & "[PaymentDate] LIKE " & Me.txtPaymentDate & " AND "
End If
' Check for max VoucherNumber
If Me.txtVoucherNumber > "" Then
varWhere = varWhere & "[VoucherNumber] LIKE " & Me.txtVoucherNumber & " AND "
End If
' Check for max Name
If Me.txtName > "" Then
varWhere = varWhere & "[Name] LIKE " & Me.txtName & " AND "
End If
' Check for max Address1
If Me.txtAddress1 > "" Then
varWhere = varWhere & "[Address1] LIKE " & Me.txtAddress1 & " AND "
End If
' Check for max Address2
If Me.txtAddress2 > "" Then
varWhere = varWhere & "[Address2] LIKE " & Me.txtAddress2 & " AND "
End If
' Check for max Address3
If Me.txtAddress3 > "" Then
varWhere = varWhere & "[Address3] LIKE " & Me.txtAddress3 & " AND "
End If
' Check for max Postcode
If Me.txtPostcode > "" Then
varWhere = varWhere & "[Postcode] LIKE " & Me.txtPostcode & " AND "
End If
' Check for max SC
If Me.txtSC > "" Then
varWhere = varWhere & "[SC] LIKE " & Me.txtSC & " AND "
End If
' Check for max PaymentMethod
If Me.txtPaymentMethod > "" Then
varWhere = varWhere & "[PaymentMethod] LIKE " & Me.txtPaymentMethod & " AND "
End If
' Check for max Factor
If Me.txtFactor > "" Then
varWhere = varWhere & "[Factor] LIKE " & Me.txtFactor & " AND "
End If
' Check for max Type
If Me.txtType > "" Then
varWhere = varWhere & "[Type] LIKE " & Me.txtType & " AND "
End If
' Check for max InvoiceReference
If Me.txtInvoiceReference > "" Then
varWhere = varWhere & "[InvoiceReference] LIKE " & Me.txtInvoiceReference & " AND "
End If
' Check for max InvoiceDate
If Me.txtInvoiceDate > "" Then
varWhere = varWhere & "[InvoiceDate] LIKE " & Me.txtInvoiceDate & " AND "
End If
' Check for max InvoiceAmount
If Me.txtInvoiceAmount > "" Then
varWhere = varWhere & "[InvoiceAmount] LIKE " & Me.txtInvoiceAmount & " AND "
End If
' Check for max Dept
If Me.txtDept > "" Then
varWhere = varWhere & "[Dept] LIKE " & Me.txtDept & " AND "
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
View 8 Replies
View Related
Mar 30, 2008
Hi all,
I have a small problem at the moment i have a subform which details get enteredt into and then a report which prints out details in the subform but the order they are in the subform and the order they are in the report are different so if there were items like tea, coffee, latte, on the report will come out like latte tea coffe is there a way of having the report print out exactually the way it is in the subform and what is affecting this can it be the subform or report?? thanks guys.
regards
Naym
View 1 Replies
View Related
Jul 17, 2014
I am using Access 2007..I have created a main form and have inserted a subform. When I open the main form, the subform displays correctly. However, when I go to create a Report from the main form, the subform information is not displaying.
View 3 Replies
View Related
Aug 31, 2015
I am attempting to create a report and pass on the information from the current form to it. It is supposed to create a student ID and I am unable to get the information into the appropriate fields.I can get all the info from OpenArgs into one box but it wont parse it all out. The Form is within a navigation form. I know the below option is not the best way to do it, but other options I have found it has issues with sending when the form is within a navigation form and I prefer not to redo the entire thing at this point to remove the Navigation form.
Code:
DoCmd.OpenReport "rptStudentID", acViewPreview, , , , OpenArgs = "lblSSID|" & Me.SSID & "lblFirst|" & Me.firstName & "lblLast|" & Me.lastName & "lblDate|" & Me.dateAdded & "lblImg|" & studentList.Column(4)
The Parsing which is mostly just a copy from the above site
[code]....
View 6 Replies
View Related
Aug 17, 2006
THis has to be an easy issue.
I have a subform that in the on curren event i passes the ClassId out to my main for in an unbound text box
Forms![Student]![ClassID] = Me![ClassID]
I know this part works
I then have a command button that should pass ClassID to a report so that it can be filtered. Here is the on_click code
Private Sub cmdReprintAccom_Click()
On Error GoTo Err_cmdReprintAccom_Click
Dim stDocName As String
Dim strReptCriteria As String
strReptCriteria = ClassID
stDocName = "Forms - Accomodations"
DoCmd.OpenReport stDocName, acViewPreview, , _
"[ClassID] = '" & strReptCriteria & "'"
My problem is that I keep getting a type mismatch error. I know that it is because CLassID is a number and it is getting passed as a string i just can't figure out the syntax to the highlighted code.
View 5 Replies
View Related
Oct 4, 2006
I have a subform that displays results based on selections from cascading boxes on the form. I am trying to print them to a report. I think my procedure is correct for the print button and Im afraid my problem is that the form is not based on a seperate query. Is that they only way to be able to print? It is based on procedures defined in the cascading boxes. How can correct it so it will print the results to a report? Thanx.
View 2 Replies
View Related
Dec 6, 2004
I have a form with a subform in it and I would like to print the contents of that form to a report. I am able to print now, using a "print" command button, but it only prints one record, just the one that is being displayed. I want to be able to print a report with all the records that are in the filtered result.
Also, when the press the "print" button I would like to give them an option "Print this record?" "Print All records?". depending on the input, it would print the report.
Can someone please help me with this? Much appreicated.
Thanks.
ronnie
View 1 Replies
View Related
Nov 7, 2013
how do i select one record from a subform and open it on a report ?
View 2 Replies
View Related
Aug 31, 2006
Hi there,
I am trying to save my form that i have created as a report by right clicking on the form and choosing save as report. However, my form includes a subform but the subform does not show up in the report. Any ideas as to why this and how it can be shown????
Thanks in advance
Lucy
View 2 Replies
View Related
Dec 25, 2014
i have some combobox which shows the column shown into the subform. i can filter the subform using the comboxes. now i need to build an instant report based on the current filtering. i can filter more or less. but i need the current position of the subform into a report.
View 2 Replies
View Related
Feb 20, 2014
How do I pull information from a subform (more than 1 record in subform at times) to put on my report?
I have added the subform to the report and set Visible property to No,
I know the syntax to refer to the subform and its fields but I don't understand how to get all records in the subform for the main record to be displayed.
View 3 Replies
View Related
May 30, 2013
I'm using access 2003
I've successfully printed a report from a main form control and now I'm trying to do the same with a subform control.
I'm using a query to filter the report, with the criteria of:
Code: [forms]![MainForm]![Subform].[form]![textbox]
Is there something I'm missing?
View 10 Replies
View Related
Feb 28, 2014
I have a report which has a subform attached which I print it to a dot-matrix printer so I can use carbon paper.
The subform has shaded headers which look on the screen, but bot when printed with dot-matrix.
Can un-shade the headers or even hide them?
I attach a screen shot for reference.
View 4 Replies
View Related
Jul 10, 2013
I have tbl_Proj, tbl_Notes, and tbl_Proj_City. They are all joined by the tbl_Proj's ID.I have a set list in tbl_City - tbl_Proj_City is 3 columns an ID for itself, tbl_City ID and tbl_Proj ID...In instances where a project covers more than one city, when I generate a report (or query) for that project I get each notes that number of times. If a project is in 3 cities I get each note 3 times.
I have a project entry form with a combobox subform for the city selection.The report is from a qry, by Proj_ID, I need to show the cities - the "key" city is the first alphabetically (also lowest via autonumber in City_ID). I would like to add that I do not know SQL. I have created this database using access commands.
I am thinking that I may be able to query the project ID for the Cities and somehow select the first alphabetically or the lowest in ID and store that somewhere? and then run a query that uses that and the notes to generate the report and then have a sub report for the other cities?
View 6 Replies
View Related
May 21, 2014
I am having a terrible time getting this to work. I have a mainform that contains 11 multi-list boxes. That mainform when I select whatever I want in any of the 11, select all items in each of the 11, or select nothing and click my show results works great. In my detail section it displays the information it should. I need to now take that and put it in a report with a pivot graph. I created a subform that is my pivotgraph and the reason for a subform is the end-user still wants the ability to filter more should they choose.
In addition, the regular graph you can put in the report does not allow me to put in multiple items. For instance I need to have sumofmbrstargeted and sumofmbrsconverted in the values and it will say I can only choose 1 value and then it will say up to 6 items and I have a 8. So, I opted for a subformpivotchart. If I create the following code, the subform updates based on the filters on this mainform everytime. Works like a charm.
Code:
Private Sub cmdGetGraph_Click()
DoCmd.OpenForm "Graph", acFormPivotChart, , GetFilterFromListBoxes
End Sub
The problem is, I want the Graph in my Report. If I take the Report and do the same basic thing:
Code:
Private Sub cmdGetReport_Click()
DoCmd.OpenReport "Search", acViewPreview, , GetFilterFromListBoxes
End Sub
It asks for the parameters again. Example, LOB, Plan, Prod_NM. I tried just disregarding the report and created yet another subform for the reporting piece and embedded the graph and that still asked for the parameters. It works great for the subform as the graph and I want to have the ability to use the GetFilterFromListBoxes, apply it to the Search Report with the embedded Graph.
View 14 Replies
View Related
Nov 2, 2013
I have a command button as a field in a continuous subform which is based on a table. I click it and it opens a report. I need the report to be filtered by the ID of said record and not to show all the records.
View 3 Replies
View Related
May 11, 2015
How do i create a report that is linked to my sub-form that i have just sorted in a different form? let's say i have a form named View Records and on that form i have added a subform that is linked to my Employee Table, and then on that View records form i have added a few combo box in order to sort the data on my subform on that form, now what i want to do is that, i want to link those data that i just sorted on my subform and transfer it to a report.
View 5 Replies
View Related
Sep 5, 2013
I have a form called GetdataFrm. Within it i have a combo box that filter a query. When the combo filters, it populates a continuous subform called GetDataQrysubform. GetDataQrysubform look like a table that contains this Jobcode information:
EmployeeID Jobcode CardAccess Folders Software
When I filter the GetDataFrm form, I want to be able to click on a Employee's EmployeeID number from a single record in GetDataQrysubform and then a form called SingleRefrm would pop up taking me to that record.So far I have a event procedure coded to open up SingleRefrm, when the EmployeeID field is clicked but its not working. The code looks like this:
Code:
DoCmd.OpenForm "SingleRefrm", acNormal, , "EmployeeID = '" & Me.EmployeeID & "'"
View 9 Replies
View Related
Jan 26, 2015
I have a form created from a table that contains item information.
I have a sub form linked to a different table that contains records.
Each item has multiple records associated with it, so for example 1 record on the main form could have 10 records displayed in the sub form.
Example: Main Form :
Item | Units | Serial Number
Thermometer1 | DegC | 123456
Sub Form:
Item | Date | Actual Measurement | Unit Reading
Thermometer1 | 01/01/15 | 25 DegC | 24 DegC
Thermometer1 | 01/01/14 | 25 DegC | 23 DegC
Thermometer1 | 01/01/13 | 25 DegC | 24 DegC
Thermometer1 | 01/01/12 | 25 DegC | 26 DegC
Thermometer1 | 01/01/11 | 25 DegC | 25 DegC
I want to be able to print preview a report based on 1 record record selected in the subform. So using the example above i'd like to print the record on say 01/01/13 as a report that contains all the information from the item table and only the information on the 01/01/13 from the record table.
View 5 Replies
View Related
Jan 12, 2015
I'm trying to create a report that contains data from a "main" form and a subform. However, I can't get the main form data to populate no matter what I try. I've been through all sorts of queries and just can't get it to work. The main form and subform are both separate tables, and there are no redundant fields.
Basically, the main form is an inventory of assets, and the subform is designed as a way to submit trouble tickets when one of those assets requires maintenance or repair.
View 3 Replies
View Related
Jun 19, 2013
I would like to show a report's print preview in a sub form. Is there any possible way? I don't want the user to see the datasheet view of the table so I decided to put on a report.
View 1 Replies
View Related
Jun 14, 2015
I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.
The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report
Code:
Private Sub PrintBtn_Click()
Dim strCriterion As String
Dim strMsg As String, strTitle As String
[Code].....
View 3 Replies
View Related
Mar 4, 2014
I have a database that I use for keeping track of clients and printing invoices using a form/sub-form and report/sub-report. I want an image to be visible on my sub-report when I choose Received Payment in my sub-form. Right now I have my image set to visible = no.
View 14 Replies
View Related
Jun 18, 2013
I have a main form (Parent) along with a subform(Children). I want to have a button that generates a report with the Parent information as a header and the items in the subform as details. In addition, I want the report to show only the children that were recently added not all of the children.
View 1 Replies
View Related
Sep 16, 2014
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
View 7 Replies
View Related
