I have a function that capture a report activity ,open, close, updated by query, etc.... I have a report that has a inputbox on the open event that tell the user this date the report will display is from a previous update and ask if they want to run the query to update the data.
What I was hoping to do, is have this inputbox only fire if the date and time written when the last update occurs was 24 hours ago.
Can I capture the date and time that the function captures and writes to a table as a variable and you that date and time to detemine whether that inputbox fires?
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code: Option Compare Database ' Constant for maximum number of columns EmployeeSales query would ' create plus 1 for a Totals column. Here, you have 9 employees. Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
How does one have variables automatically appear if one is adding another record from a previous record.
IE. My first record has ClientID and date. If I hit the add record button then how do I make it so the next record has the exact same ClientId and date?
This is the first time that I have done any major work with forms. After I thought I had finished a problem came up. There are several forms that are use to input information into a table. When the form is opened it grabs an automated number for tracking. The problem is, that if the form is opened and then closed it creates a line of data with all null values.
What I would like to do is have the form open, the user fills out the information, and upon pressing the "save" button, the data will save to the table. I think I need to have each text box write to a variable and then save on the click event. Or I could be completely wrong and need to do something else.
i'm guessing this is something I would learn in Forms 101 if i had ever taken that type of course
I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product. Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales.
Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?
I'm trying to create a simple chart in Access but I can't seem to do it. I can create it very easily in Excel. It's just a simple posting of values that I want to give a report on with every record. I'm including an example of what I would like to do...(but with ACCESS, not Excel)
It's basically a set of scores for students. I remember reading somewhere how someone created a chart using VBA with boxes, and not the charting system in ACCESS but I can't seem to find that again.
Also, I want to be able to report on each student separately. That is, they are doing 6 tests and I want to show how they did on those tests with a simple bar graph.
Ok, so now this is where it's getting more complicated... (though I guess this is where you guys usually start)
When you have a query, and you type in your inputbox thingy like so: http://reenen.fateback.com/image.jpg
PeriodEnd: [Enter period end date dd/mm/yyyy: ]
And you can have multiple, then it will prompt you for several of them.
Now this is not very tidy, so I'd rather do it on a form. How can I display the form as the query/report runs, and insert the values entered into the form as the inputs into my variables? (This case PeriodEnd)
I am using vista and access 2010, what I want is to be able to email a report from access that was created by a form with DoCmd.OpenReport "ReportOrder", acViewReport. I have a button that when i click it it will send the report via email, but the email address has to be a variable so when the report is created i can use the email that is attached to the report data.
I have a form with 5 buttons on it. Each button is meant to select a warehouse location, so a query can be run to give an inventory report for that location. There is a separate query for each button and the OnClick event does properly modify the recordsource to give the appropriate data to the report for the location selected.
To this functionality I want the OnClick event VBA to pass the warehouse location to a textbox on the report, so the title of the report reflects that inventory location.
My code thus far is:
Private Sub Command5_Click() Dim mySQL As String Dim WHSE As String mySQL = "SELECT [Master Part List].[Part Number], [Master Part List].Category, [Master Part List].Description, [Master Part List].MaterialCost, [Master Part List].Inventory, [Master Part List].Update, [MaterialCost]*[Inventory] AS [Total Cost], [Master Part List].Warehouse"
[Code] ....
When I get the report, the textbox is empty, instead of containing the text value for the warehouse location.
I have developed a quote form that is working well. I want to print out a Quotation to send to my customer. Currently it shows the Qty, Item ID# and description in the detail portion of report. In the report footer, currently it will print out, Subtotal, Freight and the resulting total.
A problem arose when I tried to add the total weight of the items in a quote to the report form. I get a "compile error...variable undefined" msg when I try to open up the report by clicking a "Print Report" button I have on the form.Here is the code that is highlighted by the error msg. the Undefined variable is dWtTotal n red below.
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) Me!txtSubTotal = dSubTotal Me!txtDiscountValue = dDiscVal Me!txtTotal = dTotal Me.txtFreight = dFreight ' Me!txtTotalWithWork = dWithWork '12-21-14 Add Total Job Wt to Report Me.txtWttotal = dWtTotal
I feel like I have defined the variable with this code below which is in the first part of the VBC code that occurs when I click on the Print Quote button:
I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.
Table 1 has information with all clients query 1 has x, y, z being pulled > client used as parameter query 2 has the list of clients
Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.
Code: Private Sub CmdInternalReports_Click() Dim xlApp As Excel.Application Dim rstDetails As DAO.Recordset Dim strTab As String Dim strDir As String Set xlApp = Nothing Set rstDetails = Nothing
[code]...
There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.
PHP Code:
tblClientReport.Distro
Is where I have the report via client name linked the the distro list that should be pulled.
I have now a report that span around 2 pages. It has subreports in the report that depening how many rows there are they can grow or shrink.I have the problem that the second page the top margin is to close to the top of the pager. The paper has a logo on the right top side. I can make the first page look nice under the logo, but the second page prints to close to the edge that prints over the logo. I did add a page break, but when the page 1 has more data, it flushes this to the next page. The page 2 is now page 3. How can I avoid that, or make it so that the margin of the second page/next page is on the correct lenght of the top. I tried it with the page setup, but it does not work.
In the detail section of my report, I recently added a second row which has only 1 text field. When the value of the textbox in the 2nd row is null, I want the total row height equal to just the first row. When the value in the textbox in the 2nd row is not null, then the total row height is equal to row 1 and 2. I tried to make the textbox in the second row invisible when it had a null value thinking that when it was null, the report would shrink to the first row height, but that didn't seem to work.
I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct - SELECT * from tblIncidents WHERE [Nature] = 'Hover';
(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')
I want to pass the variable strSql to my report rptIncident in the following command:
Private Sub CmdPrintReport_Click() If Right(strsql, 1) <> "'" Then 'check if statement was built Else strsql = strsql & ";" 'add trailing ; to statement MsgBox strsql DoCmd.OpenReport "tblincidents", acViewNormal, , strsql End If End Sub
I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.
I have a report where I number pages based on the Store (muliple stores in report but page numbering resets when Store changes) I do this by manipulating the page number field on the report by code in the report sections to reset it and to increment it.
Code:
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = 0 End Sub Private Sub PageFooter_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = Me!PageNum + 1 End Sub Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = 0 End Sub
The report has a Page Header with various fields and labels and that's been fine however I've had a request to hide a field on subsequent pages (i.e. print it on the 1st page of each store then hide it).
So I added the following code to various sections, but can't get it to work correctly. The fields (label and text box) appear on Page 1 for the first store then get hidden but never return even though when I step through the code it is functioning 'correctly'. The GroupHeader code is performed but the fields don't get displayed on the report. I think is isn't re-painting that section but adding code to the Paint event does nothing, and in fact doesn't actually get run, so not sure when that event is triggered.
Code:
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer) Me!PageNum = 0 lblMemberNo.Visible = True MemberNo.Visible = True
(a) The On Open event of my report contains a VBA Sub that assigns a value to a variable named vShow. (Tracking the sub in VBA shows that vShow is correctly being assigned the desired value.)
(b) I then use vShow to try to control a calculation that occurs in one of the text boxes of the detail section in the report
(c) Basically, the control source of the textbox contains (in part) the statement (vShow>[fieldA]), which is embedded in a longer function.
(d) However, when I type this in at Control Source box of the Data tab of the the Property Sheet, Access always substitutes "[vShow]" for "vShow".
(e) So what I get is ([vShow]>[fieldA]), which treats vShow as the parameter of a parameter query.
ANY WAY TO OVERCOME THIS AND HAVE vShow recognized as the variable I defined at On Open? Perhaps there needs to be a variable declaration there, that I don't understand.
The basic form (vShow>[fieldA]) does not seem to be the problem, because I can enter, e.g. (500>[fieldA]) and everything works OK.
Please could somebody point me in the right direction of code.
I have a database which I would like to query using a form with a combobox or two, each of which containing a list of eg. Customer Names, or Reference Numbers, which will then produce a report which can be printed out.
I know basic ADO, like get the database, open it, find records, update and delete records, however I have been unsuccesfully searching for the code to open a report, which will respond to certain sql parameters that correspond with the values in the combobox. And then be able to print that report if possible.
I would be much appreciative if anyone would point me in the right direction, Thanks!!
I have a database with loads of different sales data from Jan 2014 with other product details as well. I also have a form where the user can input any of these data (this is based on a query):
- Campaign name - Exact Date - Month - Year - Category - Campaign Type
This should run the query, then open a report that's grouped by:
1. Year 2. Month 3. Exact Date 4. Dem name
Even if the user inputs the month and year on the form, when reopening the report, it prompts for inputting the year and month as well, but nothing else.
I have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String SampleID = InputBox("Enter Sample ID") If SampleID > 0 Then DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID Else DoCmd.Close End If End Sub
I have list of candidates shown in report. I need each candidate to have a unique number based on user input. And this number increases by 1 for the rest of the candidates in the report.
Example is below:
If user enters 1132 in the user input box then the numbers shown in report should be as below:
Name Assignment Number
Felicity Thomas 1132 Andrew Sen 1133 Andy Luker 1134
Similarly if number entered is 2345 then the numbers shown in the report will be:
Name Assignment Number Felicity Thomas 2345 Andrew Sen 2346 Andy Luker 2347
I'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables . Here's a quick outline of my general data organization:
Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer LR = 5 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wbRef = xlApp.Workbooks.Add With wbRef
wbRef.Activate .Worksheets("Sheet1").Activate With ActiveSheet .Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes End With end With
