Modules & VBA :: Open Report With Subreport Based On Unbound Form
Apr 14, 2015
I am trying to open a report (rptGMX-IRG) that includes a sub-report (srptRG) based on criteria that is set with two unbound fields on a form (frmGMXbook). This form has two dropdown boxes (cboGMX_No & cboMachSize) and a command button. The first dropdown box includes data located in the main form and the second dropdown includes data that is part of the sub-report.
I can get the report to open properly when I enter data into both fields on frmGMXbook.
However, the problem I am having is the inability to have one or both fields be "Like '*'" and show all associated records.
Here is the code I have on the command button.
Private Sub GMX_Preview_Click()
Dim strCriteria As String
If IsNull(Me.cboGMX_No) Then
strCriteria = "[cboGMX_No] Like '*'"
Else
strCriteria = "[GMX_No] ='" & Me.cboGMX_No.Value & "'"
[Code] ....
View Replies
ADVERTISEMENT
Oct 29, 2013
I have some code for a button on click event to open a form and select the record which corresponds to a value in an unbound text box. The code is:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOpenPatientRecord"
[Code] ....
This works fine when I put in a 10 digit NHS number but opens a blank record when I enter a four digit or six character/digit PatientNumber. Both patient number and NHS number are text fields in the underlying table.
View 14 Replies
View Related
Jun 26, 2013
I have a report which programmatically sets the value of some labels based on its own internal logic for each line of the detail section of a report. This all works fine and dandy, using the Detail_format event, and accessing detail.controls.item(x).caption.
HOWEVER, when I then embed the report as a subreport (which I need to do), I goes wrong. Here, I get the values of the last row of the detail repeated in every previous one. I'm suspecting because the parent report has its own 'detail' (I've tried giving the subreport its own distinct detail name).
View 1 Replies
View Related
Apr 22, 2014
When I have a value in "WorkCenter" everything works great and graph looks fine. When I leave blank in order to see all records, I get a blank graph.
Here's the code:
Private Sub Open_Downtime_Graph_Click()
Dim strCriteria As String
If IsNull(Me.WorkCenter) Then
strCriteria = "[WorkCenter] Like '*'"
[Code] .....
View 7 Replies
View Related
Nov 25, 2013
This is my data:
Table: "Facility Info"
Data in the table: "facility", "city", "date", etc.
Query: "Q Facility"
Report: "R Facility"
Form: "Main Form" is where the data is entered that goes into the "Facility Info" table.
In the "Main Form" there is a dropdown box where I can select the "facility".I would like to add a button to this form that opens my report "R Facility". But this report is a collection of all the facilities and I would like it to just report the ones for the facility that I selected from the dropdown box on my "Main Form".
View 9 Replies
View Related
Oct 25, 2013
How to display only the first few records in a subreport but keep the grand total of the report itself. When I limit results in query; it gives me the records but only totals for that set. I thought about putting code in the on format in detail section like:
Code:
If me.control.value >10 then
me.control.visible =false
but doesn't work.
View 4 Replies
View Related
Mar 7, 2014
I have a form (frmSearch), based on (I think) John Big Bootys code. On it, a search box, which filters results as you type. Works great.
Instead of presenting the search results in a listbox, I wanted it in a subform (sfmSearchresults). Also works great.
Problem was this: I wanted a button on this form, to open another form based on [CustID]. For a listbox, I could use columns(0), but not for a subform. After much trying I got this to work:
Code:
Private Sub cmdVisaPren_Click()
Dim strWhere As String
strWhere = "[CustID] = " & Me.sfmSearchResults.Form.Controls.Item(5)
DoCmd.OpenForm "frmAddCust", acNormal, , strWhere, acFormEdit, acWindowNormal
End Sub
The part that I don't understand is the Item(5). The subform is based on a query (qrySearch), from tblCustomers. tblCustomers fields is as follows:
CustID, FirstName,LastName,Adress1,Newspaper1,Newspaper2.
Item(0) in strWhere would return Adress1 as [CustID].
Item (1) = Newspaper1, Item(2) = Newspaper2, Item(3) = FirstName, Item(4)=LastName, and (finally) Item(5)=CustID.
I don't get the logic. With listboxes, I could just count columns for columns().
Have attached my database. It's just a test database, with 4 people. The final database will have about 1200 people, and many more fields. Its .accdb (Access 2013). I tried converting to Access 2002-2003, but couldn't get it to work.
View 4 Replies
View Related
Aug 8, 2013
I have an unbound report - which is actually a form for the user to print out if they need one. There is no data on the form.
Here is the code that is run when the Button is Clicked:
'First, I reenable the Access menus so that they can use those to print
DoCmd.OpenReport "TransitionSurveyPaperFormLEA", acViewPreview
When I open the report on XP/Access 2007, it works just fine.
When I open the exact same accdb with a Win7/A2007 machine, the report does not open. If I debug and display the error, a msgbox appears
"The OpenReport action was canceled."
I have other bound reports and those open with XP/A2007 or Win7/A2007 just fine.
It makes me VERY nervous that the same exact ACCDB in A2007 runs differently on different OSs (XP vs Win7).
View 1 Replies
View Related
Jul 6, 2005
Below is the working version of the code works for single report filtering. say a report called "rptduedate_census2" and i can filter out using form instead of hard coding query of that report (say lastname=form!frmsample!txtlastname). I dont want to hard code query and there are reasons for that.
I am using a Main report called "rptDueDates_Dept' and there are about 10 different subreports (see print screen). The main report is not bound to any query. It has many subereport. Each subreport is bound to some query and i need to be able to filter them before data appear on the main report. Am i making any sense, LOL.
How can I implement my working code below to do this. PLEASE PLEASE PLEASE HELP. I AM NEW TO ALL THIS. I have attached a print screen of the main report, just to show how i put subreports in their.
===== MY WORKING CODE ======
Private Sub cmdPrv_Click()
Dim strFilter As String
strFilter = "[Mail_Census_Date] BETWEEN #" & txtStartDate & " # AND # " & txtEndDate & " # "
' Apply the filter and switch it on
If SysCmd(acSysCmdGetObjectState, acReport, "Report") <> acObjStateOpen Then
DoCmd.OpenReport "rptduedate_census2", acViewPreview
With Reports![rptduedate_census2]
.Filter = strFilter
.FilterOn = True
End With
DoCmd.Restore
Exit Sub
End If
End Sub
View 1 Replies
View Related
Feb 12, 2015
I am trying to create a customizable report that would allow the users to choose fields. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.
The error reads: Run-time error 2465: Application-defined or object-defined error
Could it be something as simple as an incorrect reference? I have checked multiple times, but I am stumped.
Code:
Option Compare Database
Option Explicit
Sub MakeReport()
[Code].....
This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.
View 14 Replies
View Related
Feb 3, 2005
I get the feeling I'm really missing something glaringly obvious here but I'm struggling.....
I have a combo box on my switchboard that looks up field txtOfficer in a query and then want it to open up a new form containing only those records relating to the officer name selectedtxtOfficer
The rowsource for the combo is:
SELECT DISTINCTROW [qryMain].[txtName] FROM [qryMain];
and the on click event is:
DoCmd.OpenForm "frmFiltered", , , "[txtName] = " & Me.cmbOfficer.Column(0), acFormEdit
When I select a name from the combo I get a parameter box and have to type in the name to open up the filtered records form. How can I get rid of it so the selection is one seamless process? :confused:
View 2 Replies
View Related
Oct 24, 2013
I am trying to setup a button that will open a report on click. The catch is that the report name is coming from a query result that is populated into a list box. The way it works the list box will never have more than one result so by default it will always house the name of the report appropriate to a chosen client. I would like to do it this way so that I don't have to clutter up the from with extra buttons all pointing to specific reports.
I found some references to this in this forum, including the example listed below, but I have been unsucessful in making work so I assume that I am entering wrong, entering it the wrong place or maybe the code is incomplete. Unfortunately I am a complete novice to Access VBA so I am still learning how it all works together.
List box name is BillingFormat
The first of 5 reports is named Standard Billing
So in the form I enter the client number and BillingFormat is auto populated with Standard Billing
I tied the button to this code: DoCmd.OpenReport Me.BillingFormat, acPreview
The results I was looking for was for the report named Standard Billing would open. The first way I set it up nothing happened not even any errors. The other way threw an error stating it did not recognize DoCmd as a valid function.
what I am doing wrong?
View 7 Replies
View Related
May 9, 2013
I have some code that was written by someone else. I am trying to duplicate this in another database but, can't seem to get it to work. I also don't understand how this code is working as as there is no reference to the combo box in the code behind the print button. But depending on what you select in the combo box the report opens and filters on that selection. The following code is behind the print button. There is a separate combo box, that does not seem to have a connection to the print button. It just has a query behind it for the items to select from.
Public Sub BTNPrint_Project__Job__Numbers_Click()
On Error GoTo Err_BTNPrint_Project__Job__Numbers_Click
Dim stDocName As String
stDocName = "RPTProject Number (Job) Book Year"
DoCmd.OpenReport stDocName, acPreview
Exit_BTNPrint_Project__Job__Numbers_Clic:
Exit Sub
Err_BTNPrint_Project__Job__Numbers_Click:
MsgBox Err.Description
Resume Exit_BTNPrint_Project__Job__Numbers_Clic
View 14 Replies
View Related
Apr 21, 2014
My table:
tblHeatTreatment
- HeatTreatmentID - PK
- HeatTreatmentDesc - Text
- HeatTreatmentDetails - Memo
My form has a listbox (lstHeatTreatments - Multi-Select disabled) that displays Heat Treatment descriptions and an unbound textbox (txtHTDetails) that I would like to have display the corresponding memo field when a description is selected from the listbox.
This is my code so far:
Code:
Private Sub lstHeatTreatments_AfterUpdate()
Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String
Dim selectedRequirementKey As Long
Set myConnection = CurrentProject.AccessConnection
Set myRecordSet.ActiveConnection = myConnection
[Code]....
When I run the code I get an error:
Quote:
Run-time error '-2147352567 (80020009)':
The Value you entered isn't valid for this field
When I debug, it highlights:
Code:
Me.txtHTDetails = myRecordSet.Fields
View 3 Replies
View Related
Jan 29, 2014
I am struggling to pull out a report based on the combo box selection. I have NOI database and want to pull out a report for a specific customer that how many NOIs are generated by this customer..
I have a separate table of customer and another table of NCRs and the customer information comes from customer table.
I have tried the below statement but gives me an error message:
code for Macro that pulls the report in click event:
[CustomerName]=[forms]![CustName]![combocust1]
do i need to mention the report as well in some where in report ?
View 14 Replies
View Related
Jul 26, 2012
What I am wanting is to be able to build dynamic form content/elements based on entries on a table. This is for a gym membership system. What this form is going to be used for is to allow the front desk to scan a membership card which then performs a search on the database. Part of this search is going to be on a table that contains various add-on classes, tanning sessions, etc that a member can add beyond their base membership.
On the left side of the form, I will display a picture of the member and their name. What I am wanting to do on the right side of the form is to build a dynamic list of the add-on perks they are enrolled in.
I know I could hard program elements on the form to be visible or invisible, but that would be static and leave gaps when I have to turn things off b/c that member isn't enrolled in that class, etc.
Is this even possible with Access and VBA? I know I could do this sort of thing with a webpage using PHP, PERL or whatever. I don't know the limits of Access Forms and VBA.
View 5 Replies
View Related
Oct 25, 2005
I have set up a query to give me details of employees names, the business they work for and where they work.
If I run it without criteria I get all of my records, as you would expect.
However, I want to be able to select parameters from combo boxes and then run a specific report of employees by location. I did have this working by accident a while ago but have changed quite a lot since then.
I have set up an unbound form with three combo boxes - Business, Business Unit and Location. Then a button for preview and print report linking to my report template.
I have refernced the controls in the relevant columns of my query as: [Forms]![FormName]![Control].
When I run the query I get nothing back and I don't understand why.
View 9 Replies
View Related
Oct 21, 2013
I would like to create a report based on a query. The first part is simple enough. However within this query I have a 'Count field' (a total of the number of duplicated this record has appeared in the table). And would like to create a subreport (if possible) based on that value for example. if count > 1 then show subreport.
My question therefore; is the above possible? How would I go about linking this logic with the subreport
View 1 Replies
View Related
Jul 16, 2014
I have a list box which contains different types of log entries. Each different log entry type has a different report that goes with it.What I want to do is select an entry in the list box, and click on print, email, pdf or view report buttons, and the appropriate happens, selecting the associated report for the log type automatically.I have a table with the logtype in it, and against that I have a DefaultReport field, which contains the name of the report that needs to be opened for that logtype.
I am getting the correct name for the report no problem at all, I have made sure that the name is right by going to rename report and copy and pasting it.When it goes to open the report I get an error message - Run-Time error 3071, the expression is typed incorrectly or is to complex to be evaluated.I have a debug line running to make sure I am pulling back the correct report name and I am.I have tried putting the dlookup where the report name would normally go in the docmd.openreport and I have also tried using ReportToView as a String and putting that in the docmd.openreport, but to no joy.
I have tried it with and without " around the report name and still no joy, either in the table or by concatenating the dlookup with Chr(34) at either end of it, then it comes up saying the report cannot be found.
View 5 Replies
View Related
Mar 20, 2013
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies
View Related
Jun 15, 2005
I have a Report linked with a SubReport. I would like to cancel viewing this Report if my SubReport Has No Data.
i tried this code but the report just shows up. Any Ideas?
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Trapper
If Reports![qryRepCurOrgProjsSR].Report.HasData = 0 Then
Cancel = True
End If
Err_Trapper:
MsgBox Err.Description
Exit Sub
End Sub
Ok i got an error message. Yoy entered an expression that has an invalid Reference to the hasData property.
Ive tried using a text bpx on my main report and putting a control source as
=IIf(qryRepCurOrgProjsSR.Report.HasData,0,"Nothing")
Then in my Report Open Event i put the code below. But it fails me still.
If trim(Me.MyText) = "Nothing" Then
MsgBox "The Report Is Cancelling....."
Cancel = True
Exit Sub
End If
View 3 Replies
View Related
Jun 26, 2014
I'm having a little trouble getting this accomplished. I can instruct access to print the report, however I just want to be able to view the report after I select a button. I will attach a picture so you can see what I have thus far. I do have an older version of this and it works flawlessly and as far as I can tell everything is a carbon copy of the working version.
View 9 Replies
View Related
Mar 5, 2006
I have a report named rpt100 with two subreports srpt100a and srpt100b. The subreports are based on query qry100a and qry100b. Both queries are based on tbl100. I removed a field named 'Comment' from tbl100, as it wasn't useful; Also removed the fieldname from both qry100a and qry100b. When opening rpt100 a parameter dialog opens asking for data on the deleted fieldname 'Comment'. The field 'Comment' was never used in the report or subreports.
Inspection of the subreport fieldlist shows field 'Comment' still present.
How, other than remaking the rpt100 and both srpt100a and srpt100b, do I remove the field 'Comment'?
Gunner...:confused:
View 1 Replies
View Related
Sep 8, 2014
I am 2 years into my database. I am trying to find and open a folder based on a text box. The problem is folder could be in many sub folders which is hold on our J: drive.
To further complicate, the folder i am searching may not be exactly as the text box states.
EG. Text box could say 123456 however the folder could be called M123456 etc.
How do i locate a folder or subfolder and open it based on part of a text box...
View 3 Replies
View Related
Nov 26, 2013
I have a database as follows: Date1 Name Issue Action_Item1 Status1 Action_Item2 Status2 .....
with up to 5 max action items/status; where Status is a checkbox.I ONLY want the report to return records where there is text in an action_Item input AND the corresponding status is FALSE. The issue I'm having is that there may be ACtion_Item inputs withOUT text; so I can't simply query the Status checkbox for false.how to get started.
View 2 Replies
View Related
Jan 13, 2014
i have a form to enter a new client if the client is existed then a message box appear and tell me that this client is existed and his number id is ## with two buttons yes and no.if i click yes i should go to a report that contains information about that user and this report take his data from a query
new client save code
---------------------
Dim MSG As Integer
Dim ExistentID As Long
ExistentID = Nz(DLookup("P_ID", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N"), 0)
If ExistentID > 0 Then
[code]....
when i click yes the report should open with the existent id but he open a input box to ente an id and his title is tbl_personal_information.PID it's the ID field that it's named P_ID in the query
View 4 Replies
View Related
