I currently have a report that generates information based upon a query. The report is broken down into 3 subgroups (Temperature --> Vzip --> VDD). The information within each subgroup seems to be correct as the report cascades through each grouping, however the graph does not update with the new information and instead just copies a clone of itself throughout each Temperature group in the report.
That being said, the first graph that is generated is correct for that group (Temp: -55)... but the report simply copies that graph into the next temperature categories.
I've attached a .pdf of a report generated to visually show what I am referring to, ** notice it replicates the same graph for each group **
I have a form where the users need to update an option group when they enter any information on the form.
The problem is that the users don't always update this option group.
The option group does not have a default value and has only two options.
When the user updates a form they need to choose a value.
One problem though, they may have to go in and update the form later and this action may cause them to change the option selected within the group.
Is there code I can use to alert them to first update the option group when updating the form and second to change the value within the option group when they need to.
Option Group --- Incomplete and Complete Date Resolved can trigger the Complete option to be updated
I am seriously thinking we should make Incomplete the default value and then I would only need code for changing the option group from Incomplete to Complete.
However, Management wants the Option Group so we can run a report that lists all of the Incomplete and Null values within it.
I was wondering if anyone can help. Please see attached jpg I need to change the background color of the lead status box when one of the options is selected. i.e. when warm (amber) when Hot (red)
I have an simple option group (3 options 1,2 and3!!)I am building a query where I need to display records whose option group field in the table is empty. I have tried:
field = Int(0) field = "" field = null isempty(field)
I am wondering if it is possible using VBA to update using either an option group or check boxes as shown in the frmDefaultValue in the attached file to update two tables tblLabelNumber and tblMediaType.
The form frmMedia contains two combo boxes. I am trying to use the form frmDefaultValue with an option group and check boxes as shown as not sure what is the best method here to update the values in the two table tblLabelNumber & tblMediaType at the push of a button Update as in the form?
I have an application that enters candidates in, who apply for a job.
I found that I can enter a candidate in, and if I dont enter at least an address type or phone type ( giving that candidate an address record, albiet a blank one (just a type of address) and a phone record) then the candidate wont have a sub record for addres or phone at all.
This is hurting my reports. They are set to pull the primary address and phone only, as we dont need to have 2-3 contact information for a candidate on a report This is how I found out that I had blank address and phone for some test records. I need to be able to check when a person does a number of things, that at least the combos for phone type and address type have been chosen as something. This will ensure that they have a primary address and phone, as I have code that takes care of all that. I tried putting the following code in the form's before update, but since it is calling to a subform, the minute I tab TO the address combo, I get the message. The form is updating before I get the chance to skip the field.
Here is the lay out. A user can enter in candidate, their address, phone, application and activities. Address, phone, applications and activities are all sub reports on a tab control. The user should be able to move freely within any record, and only be bothered when they try to leave, if important information is still empty. I dont want to control how they enter ('you must enter this first!' lol) just that they do enter it in the end.
What I want to do is check when the user is leaving a record.(closing form, going to another record, clicking the search button that opens another form etc) if either combo is empty (no primary contact info) then it gives the message and goes back to the combo that is empty.
Here is the code I have. If anyone can help me find the right place to put it, I would really appreciate it. Thank you,
If IsNull(Me.[subAddressEntry].Form![cmbAddressTypeID]) Then msgbox "You must enter an Address" Me.[subAddressEntry].Form![cmbAddressTypeID].SetFocus Cancel = True ElseIf IsNull(Me.[subPhoneEntry].Form![cmbPhoneTypeID]) Then msgbox "You must enter a Phone" Me.[subPhoneEntry].Form![cmbPhoneTypeID].SetFocus Cancel = True Else: Cancel = False End If
If I can't figure this out, I am thinking of making a new address type and phone type. Call it "none chosen" and then make that a default selection. Then one would always be chosen...I think. I would like to avoid that though.
I would like to calculate each item's price only once in the Access report footer. However, when the SUM formula is entered I get the cumulative price for each item (i.e. $70 as opposed to $35).
Is there any way I could just show the price of the first item of the group only? I'm thinking of creating a new field in a query and adding 1s and 0s, but don't know exactly how.
FYI: I'm new to Access, but have some knowledge of SQL and VBA. I'm using Access 2000. I've looked through past posts to no avail.
I have a form which allows users to supply one or more criteria to subset a recordset. The "Execute" button on the form kicks off VBA which builds and runs a SQL statement using the selections made in the form.
The recordset that the SQL runs against can have multiple rows for each ProjectID -- based on a combination of a couple of fields. For instance, the following is possible:
If the user wants to select ProjectIDs where Employee="Herman" -- without making a selection on ProjectCategory -- I want only one of the two "Herman" rows above to be returned. And I don't really care which one. (Similarly if the selection is only on ProjectCategory)
The problem is that, using the code below on the example above, both "Herman" rows are returned. I've tried numerous approaches -- this being the most recent. The SQL statement is being built as I expect, and it's executing. It's just not giving the results I want/expect. Also, I've hardcoded selections into a stored query similar to the one below, and it works. Any ideas on what am I doing wrong?
Private Sub cmdExecuteQuery_Click() Dim strSQL As String
strSQL = "SELECT ProjectID, first(ProjectName), " & _ "first(StartDate), first(EndDate), first(ProjectActive), " & _ "first(Sector1), first(Sector2), first(Sector3), first(ClientShortName), " & _ "first(Employee), first(ProjectCategory) " & _ "FROM qryProjectsForReport WHERE (ProjectActive = " Select Case optStatus Case 1 strSQL = strSQL & "True) " Case 2 strSQL = strSQL & "False) " Case 3 strSQL = strSQL & "True or ProjectActive = false) " End Select If Len(cmbCategory) Then strSQL = strSQL & " AND ProjectCategory = " & cmbCategory If Len(cmbMember) Then strSQL = strSQL & " AND Employee = " & cmbEmp If Len(dtStartDate) Then strSQL = strSQL & " AND StartDate >= #" & dtStartDate & "#" If Len(dtEndDate) Then strSQL = strSQL & " AND EndDate <= #" & dtEndDate & "#" If Len(cmbClient) Then strSQL = strSQL & " AND ClientID= " & cmbClient If Len(cmbSector) Then Select Case cmbSector Case 1 strSQL = strSQL & " AND Sector1 = True" Case 2 strSQL = strSQL & " AND Sector2 = True" Case 3 strSQL = strSQL & " AND Sector3 = True" End Select End If strSQL = strSQL & " GROUP BY ProjectID ORDER BY ProjectID;" MsgBox strSQL OpenReport strSQL, chkDatasheet
hi. first of all, i am glad to join this lovely forum. i've read some threads, people here are very very helpful.. now the question (please bear with me if it's too basic, i am relatively new to Access): i've created a 'select' query which does everything for me, except one thing - it doesn't mark one case for a certain group of records. here is the excerpt of how it looks, the last column being the one i can't figure out how to do:
In have a table which contains customer address details. I simply want to display a 2-column list. the first column to display a disnct list of city's from the main table. the corresponding column must give the no. of records that contain the entry in the first column.
eg if the main table had 5 customers that lived in london and 6 in Maidstone then
I have a database in Access 2000 that contains multiple entries for each individual - in this case they are 'visits'. I wish to create a query that shows the first and last visit only on any particular day for each individual.
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Hi I'm a teacher and have to set targets for classes, I have created a database which allows me to input targets for the classes. I now need to then display the class target for individual pupils in a new layout. I am sure there is an easy solution and I am looking at the problem from the wrong direction. Thank you for any help
Note: Dates are in "dd/mm/yyyy" format. Also, not all data is in order in the table. It’s not how I would have designed it but that’s what I need to work with!
I would like to run a query that groups by custID & itemcode and also the most recent date for any price change. I would then like the query to display the relating price for the record it pulls out. (EG for custID 'AAA' and item 21, it would display the price of £5 as that is the value for the most recent price as of 01/06/2005) I then want to link the custID & itemcode to another table that has sales in, to check that we are using the most recent price.
Is there a way I can do this via a query or do I need to put some code together?
I would like to use a query to show only the records where "SRG" is not listed and where there is another group. So in the example data above, I would like the only record to show in the query to be the last record because it's the only one where SRG is not listed along with another group.
The logic behind this is when a problem is initiated and a group assigned, we do some things and document the results. So I am only interested in the records where we have not documented our results too.
Does anyone know how I can do this with a query?
Thanks, Jim
I want the query to only show the data where there is a user group
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
I have a report with field "Plot" as Group Header. I need to print the report which lists the owners of each Plot. When the number of owners is large, it takes more than one page for the listing. How can I force a new page for each new Plot ?
I tried using an IIF statement in a text box in the mainform branch header section to return the branch expense if subform branch = mainform branch.......When I run the report I get all of the Employee overhead but only the last record for Branch 2 branch expense displays.
I have a subreport and would like to have a header that repeats on each new page if it extends to multiple pages.I tried the suggestion I have seen to create a dummy group header based on an expression of =1 and set the group header "Repeat Section" property to "Yes".header now repeats before every record, instead of just once at the top of each new page.
Greetings, I have a form with a subform(sub1) that contains records. Also on the parent form is a subform that contains subtotals of the records on the (sub1) subform. When I add a record to the (sub1) form, I can't get the code to update on the subtotal subform unless I go to another record. what am I missing here?
I'm sure the coding is very simple, the question is .... can I build an expression using an IIF statement that checks whether the current field is the same as the same field in the next record? If it is put a 1, if not put a 0. Easy in Excel, not sure about Access??
i would like to check a record exits before a new entry is saved. However i would like to check on three criterias and i am not sure on how to write the if statement.
IF Dcount "CompanyName" & "FirstName" & "Last Name" from "tblcustomer" is the same as "frmCompanyName" & "frmFirstName" & "frmLastName" > 0
I am running a check for checking whether a value already exists in the database table. But the problem is that the control passes right through this check.
It does not give an error too...
If txt_id.Value = DLookup("id", "indicateur", "id=" & txt_id.Value) Then info = MsgBox("Cette 'Id' déjà existe !", vbInformation)
Is there any way to update the same record from two forms? The requirement is that in a form I have a checkbox. If I select the checkbox, it will open another form, in which I can update some fields which are of the same record. When I tried, it is updating on another record. Is that possible to do so?
i am attaching a sample db . If anyone knows how to do it, please update the sample db or give me advice.