I have a form with 48 unbound text fields name txt1 to txt48.
These are to represent 24 hrs in half hours, to visually represent the hours capacity of my workshop. Now if I choose a date, which is say Monday, I check my settings, and see that the workshop has a capacity of 15 hrs on a monday. Now I would like to in a loop set the backcolour of the remaining fields to red.
Something like for I = 15 (workshop capacity) to 48
I have got a query which gives me the following output;
Nr ----------- area ------- area 2 ---------- holler 14-1096-------1------------1-----------------5.9 14-1097-------2------------2-----------------7.8 14-1100-------1------------1-----------------13.4 14-1101-------2------------2-----------------7.8
What i would like to do is to calculate the sum of holler when they are in the same area.So the sum of nr 14-1096 + 14-1100 and 14-1097 + 14-1101. Ive tried to do the following;I tried to do the following just to check it would even work;
Code: test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=("1")))
Which worked perfectly, it gave me 19.3..
Code: test: (SELECT Sum([holler]) FROM querytoetsn2hr_gemiddelde_filter WHERE ((querytoetsn2hr_gemiddelde_filter.area)=(querytoetsn2hr_gemiddelde_filter.area2)))
That gave me the sum of all 4 the Nrs. Which makes sense, because you basically say that as long as area and area are the same calculate the sum of holler.if there is a way to say "sum of holler when area has the same value".
I ahve in error typed the above - however it compiles and compacts and repairs without throwing an error. Should this be spotted before I actually run the line of code??
I am trying to create a query that can be customised by a combobox.
I have a combobox that lists the fieldnames from table (rather than records)
I want to be able to run a query that updates the field selected from combobox with the vaule from another txtBox
What I want to be able to write in SQL is;
UPDATE Products SET [Forms]![Master_Form]![Combobox] = [Forms]![Master_Form]![Qty] WHERE (((Products.ProductName)=[Forms]![Master_Form]![ProductName]));
I have a form with numerous fields and would like to be able to move the focus to a selected field by typing the field name into a control box. How can I do this? TIA
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'?
As example, I have a table with an Item number, introduction year and a number of historical and future Sales periods set per year, these sales columns are listed Y1990, Y1991, Y1992....... Y2015.
Based on each items introduction year, I want to list the first 5 years of sales.
I wanted to create a dimensional fieldname eg: FirstYear: "Y"&[introduction year] to get the value of that respective year. (I currently just get a text saying "Y1995", and not the content )
Any help is appreciated!:)
(Note: I can't transpose the data in the tables for other reason)
I have a database where I would like to change a fieldname to something different.
I know I can use edit replace in the code and that works ok, but is there a way to change the fieldname in the code, the Table, the queries, and sql all in 1 foul swoop or do I have to do them all seperately by hand.
I've read some really funny stuff while searching for an answer to this. You're a bunch of really witty types :)
So I'm sure someone can help me - why would this entered data not make it into the table?
I have a form that has a subform. After the subform I have a button to continue. If this is clicked I change the visible property for another field to true. This field appears to work fine, but the data doesn't make it to the underlying table, in fact it sometimes seems to be included in the next record.
Hi, Self learning trying to modify a query fieldname and criteria thru code.
Have a small form with a button making a copy of a query/s (eventually making about 50 copies). Once these have been made, would like to open the query up, which I can do, then modify both the fieldname and the field criteria to suit my needs from parameters set in the form.
Im running a query and normally there is only a field-name in heading. I have multiple tables with equal field names. Now I want to get table names in heading too (Tablename.Fieldname) so I can make difference between fields on each tables when previewing query. Is this possible in access? I don't want to change all field-names manually, I know it's possible and done easily, but there are almost hundred fieldnames...
Im finding a solution. Help me please. Thanks :eek:
Hey guys, I am trying to input data into a form, the form consist of mulitiple tables. Once I set it up I get a "#Name?" error in the text box, and when I try to input data into the text box, at the bottom of the form it keeps saying "control can't be edited; it's bound to unknown field 'FieldName'."
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.
Hello friends, I am having trouble displaying or updating data through all my subforms.
So im just trying to isolate the issue, I went into my table definition and changed fields named with seemingly reserved keywords as follows:
name = personName userId = personId
Now, i have hundreds of report and queries based on these tables (3+) with these fields. What do i need to do to automatically update my reports and queries without going into each one to manually update :eek:
Is that what the autocorrect option does? I have been advised to always have it turned off as it causes database corruption.
So how do i update my all things with these field name change. Any suggestions, comments would be greatly appreciated :)
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
:D I have a form set up where a user chooses a page tab. Either statistical reports or detail reports. Then they have to choose between 2 toggle buttons. Candidate or hire.
So a user chooses detail, then candidate. An option group below that shows the different groupings of detail candidate reports that they can choose.
A couple of examples - detail report of candidates by office applied to, department, application source etc.
Once that is chosen a button is visible to continue. Now they are taken to the appropriate form. This is done by a global variable gstrformname defined in the afterupdate of the group option that they chose (office etc.)
Once they click to continue to the criteria form, they are choosing the criteria. (for example purposes) the choice below takes the user to a criteria form for choosing an application source ( email, monster.com etc) or click a button and all types are given. I have all of that set up. But now I want to change what report the command button is opening, to a variable. A variable that would be set by the below form when the type of report and grouping for the report are chosen...that defines a certain report.
I specified on this main report menu that it is a candidate report that I want, but when I get to the criteria form, I need a way to pass that choice on. I am not sure how to best do that.
I am trying to avoid making 2 copies of the same form 1 for candidates and 1 for hire. :crazy: I undertstand that I should pass a variable here.
So basically on the main form they choose candidate or hire report, then they choose group. The grouping information tells the command button what form to open (the application source criteria form, the office criteria form, etc). The thing is, this form is the criteria for the candidate report and the hire report. The command button on the criteria form would ideally be -
docmd.previewreport gstrreportname, acpreview
my question is, how or where do I define, from the point that they choose candidate or hire, to the next form, that they want the candidate by office or hire by office report? (define the gstrreportname variable as one of those too.)
Make sense?
Here is a pick of the report form (menu) http://www.geocities.com/misscrf/reportmenu.jpg
For the afterupdate of the frame for the grouping of the report, I have a select case. In that I define gstrreportname for the report it is, if the choice goes right to a report. If it goes to a form first (for criteria), I define that form with a global variable gstrformname. Can I also define the report there (gstrreportname)? If I do that, will the criteria form know that? Even if I close the main report form?
I'm trying to pass a variable called MyFilter between forms but am having problems. I have created a Module and declared MyFilter as a public string.
The original code in my first form is:
Private Sub Command65_Click() Dim MyFilter As String If Me.Filter = "" Then MsgBox "Please apply a filter to the form first." ElseIf Me.Dirty Then ' Make sure the record is saved RunCommand acCmdSaveRecord Else MyFilter = Me.Filter DoCmd.OpenReport "Temp", acViewPreview, , MyFilter
End If End Sub
How to I change this so that it now stores the value in the Public variable instead of the Private one which it is doing above?
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.
I have this declaration in a module called Global Code Option Compare Database Public currentCustomerId As Long Option Explicit
There is one report that simply prints a the firstname of current customer and the amount of each of his invoices. The current displayed customer's ID is always copied into the variable currentCustomerId. Below is the SELECT code of the data source of the report. But whenever I open this report it asks for currentCustomerId. Is my decalartion not global enough?
SELECT tblCustomers.FirstName, tblInvoice.Amount, FROM tblCustomers INNER JOIN tblInvoices ON tblCustomers.CustomerID=tblInvoices.CustomerID WHERE tblInvoices.CustomerID=currentCustomerId;
Note: I chose to save the current customer's ID in a global variable because, for some reason, when I select the report the Customer form loses the current customer record and goes to the end of the table as if creating a new customer record. This would not be an issue as long as I can retrieve the value in a variable.
I have defined a recordsource based on a predefined query. Now I intend to use the SQL statement which works to produce other recordsources based on other queries. More concretely here is my code as of now:
Code: Dim J1 As Long Dim SQLRecordSource As String J1 = Forms(ParName).MemID SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _ "INNER JOIN [ActiveMembersQy] ON " & _ "[MembersTbl].[MemID] = [ActiveMembersQy].[MemID] " & _ " WHERE [ActiveMembersQy].[MemID] = " & J1 & ";"
In the future instead of writing it like this:
Code: Select Case QryName Case "NonMembersQy" SQLRecordSource = "SELECT DISTINCTROW [MembersTbl].* FROM [MembersTbl]" & _ "INNER JOIN [NonMembersQy] ON " & _ "[MembersTbl].[MemID] = [NonMembersQy].[MemID] " & _ " WHERE [NonMembersQy].[MemID] = " & J1 & ";"
[Code] ....
I wish to write it like this (which does not work):
Is this possible ie put the name of the variable that holds the name of the query in quotes or sometimes put the name of the query instead of the contents of the name. If there is a contents function for variables in VBA that can be used by I doubt whether it exists.
I have been given a form that you pass a parameter into and on the basis of this it performs certain amendments to the particular data AND also runs 2 stored procedures on this data. I need to get the data to display on the form so that the user can visually verify if it is the correct data and then they will on click to execute the amendments and stored procedures.
I cannot get the data to display on the screen? I have tried to create a variable in the VBA code to store the data in and then used a message box to try to display it but to no avail?
