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
graham
I have a music database in which I keep track of my CDs and how many times I've listened to them. I have a report that uses grouping to show me the total listens for each artist for each year - the totals are in a group footer, with nothing in the detail section of the report. It looks something like this:
-------94-95-96-97-98-99-00-01-02-03-04-05-06-07-08-09-10-11-12-13-14
BAND1--00-00-00-01-13-04-03-00-01-00-01-03-10-16-04-04-01-00-01-03-00
BAND2--00-00-00-00-00-00-00-33-31-14-15-07-02-00-02-01-11-05-01-00-01
(The dashes are blank spaces.)
The first line is the column header showing years (last 2 digits for space reasons).
The second and subsequent lines are created in the group footer section, first is the artist name, then the Sum of listens for all titles by that artist in the year given ([Sum of Listens1994]).
This report works perfectly.
Here's what I want to do, and I can't get it to work:
In the above lines of data, let's say that the earliest title for BAND1 is from 1975, and the earliest title for BAND2 is from 2001. My yearly counts start in 1994. Therefore, there were listens for BAND1 prior to 1994, but there weren't any listens for BAND2 prior to 2001.
GOAL: Instead of showing "00" on the BAND2 line for years 1994-2000, I want them blank.
I've put a conditional format code in OnFormat for the group footer section, telling Access to turn visibility off for the text box control ([Sum of Listens1994] etc) if the [Earliest] field (which contains 2001) is greater than the year being displayed (1994 first, and so on). This, which seems like it should work, makes no difference. Oddly, if I put a F9 code break on the If statement, it never breaks, which makes me believe the code is never even being executed.
I do have prior experience with controlling field visibility in forms and reports, so the concepts aren't new to me. I feel like the big difference with this one is that it's in the group footer section rather than the detail section. [Access 2013]
I have a form with an option group to select subforms to be displayed .To save space on the main form I have added a subform (frmSubService) to the option group to display some of the data that was originally on the main form. The subforms data source is the same as that of the main form but only displays five fields.
After editing data in the subform frmSubService, if I then click on a field on the main form no problem, however if I click on an option button to view another subform I get a messagebox pop up with the following message;
The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-Edit the record.
If I click OK,and without re-editing the record, I can open another subform.
I have checked and the data changes made on frmSubService, in both cases, has been saved.
How do I prevent the pop-up message?
Im not sure if this is the right place to put this but I was wondering if you could help me:
Say I have database with forms that display records or people ie name, address, postcode, etc. What I want to do is display a list of Names then from that list I can double, single click and open the record details.
Id call my self and amatuer at access and am probably guessing this is done via vscript or something else, but any help will do.
Thanks
Alex
I'm working on a project where I must send roughly a thousand individual reports to a thousand different email recipients with .pdf attachment.I know I'll need to add a table that designates the email address by SHIP_TO_CODE. Then create a form with the email subject, body, etc.. I don't neccessarily have to have the reports saved to a folder; I really just need them emailed to each account.
I was thinking I could modify the code some to accomplish my goal, but I'm not sure what to put. Maybe add a SendObject in there somewhere.CODE that I have now that saves the report to a folder. (I want to modify some to send email attachement instead)
''Module CODE:
Private Sub Form_Current()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData];", dbOpenSnapshot)
Do While Not rst.EOF
strRptFilter = "[SHIP_TO_CODE] = " & Chr(34) & rst![SHIP_TO_CODE] & Chr(34)
DoCmd.OutputTo acOutputReport, "rptDraft", acFormatPDF, "C:UsersmrutherfordDesktopASC Daily Reports" & "" & rst![SHIP_TO_CODE] & ".pdf"
[code]....
I'm working on a project where I must save roughly 1000 individual access reports based on the group to a specific folder in pdf format.
Problem:The code I found on this site works, but not exactly the way I need it to. Using the current code (pasted below) all of the pages of the report are being saved to pdf for each group, instead of just the single page. For example, all 1000 pages of the report are being saved to each pdf file, when I only want the 1 page for each of the groups.
I believe that the problem lies in the filter for the Open Event code, but I don't know how to modifiy it to make it work the way I need.
CODE:
Option Compare Database
Public strRptFilter As String
Private Sub Report_Click()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SHIP_TO_CODE] FROM [qryWty&PendingData] ORDER BY
[code]....
I have a database where I record grades for my students. I would like to create a macro so that every time I record a grade as an "F", the student will receive an email that will contain an attachment of their scores. A lot of the information I want to include in this email is in several different tables. So to make it easier, I have created a query that lists the following:
Students name (SName)
Student's email (SEmail)
ID# of the form I use to record their grades (ID)
ID# of the studen'ts work (WorkID)
Date of their work (CreatedDate)
Date I graded their work (AuditDate)
Grade="F" (Accuracy)
How can I create something that will automate emailing of this information--to the individual student with only their information? I am using Access 2010. If I can't automatically send the emails, I would like to create a button in my audit screen that will send a report containing that student's information to them.
I have created a database that generates a report with customer debts. I have a lot of customers and in a report each page is a different customer with individual debts info. What i would like to do is to create a button and distribute individual report page as pdf to individual customer by email. I'm using Lotus notes. I have searched the forums i found something but it was not what i was looking for.
View 1 Replies View RelatedI have a continuous form based on a query. I would like to have a button or control that performs some command on only one certain record. In other words, the user sees the record he wants, clicks a button and it changes the value or changes the color. When I add a button to the form, it appears next to each record and performs the same command to each record. I have tried different controls, but get similar results with each one. Even a single form seems to do the same thing, but on different pages.
Am I missing something? Or maybe I am not following the concept of the form.
Man I thought this would be simple, but it quite clearly isn't!
I have a .csv file that contains around 250 email addresses. I simply want to import these into an Access table, each email creating a new record!
NO using the import function only seems to allow you to import data as long as each separate piece of data is on field in a record! I understand whent this might be the case, but why the hell can I not import it so each piece of data is a separate record in the table!!!!!!!!!!!
Please note I understand some sort of complex query may do it, but I am not that experienced with that yet and so don't know how it's done!
Please can anyone offer a simple solution? From where I am sitting, there does not appear to be one!!!!
Thanks.
Man I thought this would be simple, but it quite clearly isn't!
I have a .csv file that contains around 250 email addresses. I simply want to import these into an Access table, each email creating a new record!
NO using the import function only seems to allow you to import data as long as each separate piece of data is on field in a record! I understand whent this might be the case, but why the hell can I not import it so each piece of data is a separate record in the table!!!!!!!!!!!
Please note I understand some sort of complex query may do it, but I am not that experienced with that yet and so don't know how it's done!
Please can anyone offer a simple solution? From where I am sitting, there does not appear to be one!!!!
All my records are displayed on a form. Upon clicking on their respective 'View' button, I would like to open that particular record. How can I do that? I have attached a screenshot for better clarity. Thanks in advance for the help!
Regards,
Swee
Not sure if it's possible but I'm trying to create individual calendar's for each staff member and client to put availability and bookings on to them. I've tried using the active x control but it's not really what I want, not sure how else to approach this as my access skills aren't that great.
View 3 Replies View RelatedI am having trouble getting the ID of the current record displayed on a form. The code I have written is this:
Code:
Dim ProjID As Long
ProjID = Forms![ProjectDetailsAll].CurrentRecord
The problem is that this code returns the record number relative to other records (so, this record is number 2634 of 2634). However, I am trying to get the ID of the record, which is an autonumber and is not the same as the record's order amongst other records. There have been record deletions ahead of this record, so the ID might be 3096 even though CurrentRecord tells me that this record is number 2634. Is there another way of writing this to get what I need?
I wanted to know if it is possible to set a rolling year based on the first recorded date for an individual. In other words, my table is updated every time an employee receives a point. The record includes the employee's name (empName), the date (dateOfOccurrence), and the point (occurrencePoint).
I would like to create a query that would be based on a rolling year from the very first record of an employee based on the first dateOfOccurrence. The first date/record of this employee would be the start of the rolling year. I am writing this in SQL in Access 2013.
I want to lock an individual record when a certain check box is ticked so it can't be edited again. I've tried setting the form to read only when this check box is ticked on the form OnCurrent event, but this locked every record and I had to remove the code to untick the check box and be able to edit the record again.
View 2 Replies View RelatedI am working on a website which has over 1000 pages that has the same code, but only a link that is different on each page. So, I thought that I will create a database and work my way from there. I was able to work the code and combine everything. Now would it be possible to export individual record from a query to a file with .html extension? If not, .txt will work as well. However, I want to have another field that would have the file names and the file is saved under that name.The query name is "Code Query" and the field name is "Final Code".
View 2 Replies View RelatedI'm trying to find the best way to organize a report that will have ALOT of fields for an individual record. Because it is for an individual record not a group of records (and I want it to be dynamic) I'm thinking of doing a filter & query to fill in report with necessary details. This report is going to contain ALOT of fields, even the wizard that I used based on the query said that there were too many fields, but I can't cut down.
View 4 Replies View RelatedWhat event would you use to update a label caption relative to a number field in the same form, so that each time a new record is displayed, it goes through my complicated "if" bva code. I want the letter "th", "st", "rd" to display beside a text box relative to the value displayed.
I suppose I could do this in the query. :confused:
I have a form that is linked to a table. tblContacts
On the form I have added a listbox which has every possible Contact (Name) listed. I wanted to be able to click on a contact name and then have the form bring up all the information related to the contact.
right now i have on the click of the listbox a msgbox that gives me the contact id associated with the contact name.
BTW, this is a project i am taking over from someone. If it was my choice I would be creating a web app.
I want the lookup to be performed with criteria based off of the same row that record being selected is on.
I wrote typed out the Columns and rows that my queries return, and attached a screenshot as well.
You enter your phone number which provides you with an indexed location. Based off of that location only certain products are available and I want them to be filtered accordingly. I have it working to some extent, here is an example below:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521| KNNRLABR
The lookup displays products available where the indexed products match the same location. Here is what the query returns:
| Location |Products Available|
| KNNRLABR| Product 21 |
| KNNRLABR| Product 9|
| KNNRLABR| Product 7 |
| KNNRLABR| Product 3 |
This is the affect that I want to achieve but, if I add another location like so:
|Site Name|Phone Number| Location |Products Available|
| Store 1 | 5044645521 | KNNRLABR |
| Store 2 | 3609061624 | VANCWA01|
The lookup returns all products available for all the records for each location entered rather than based off of the selected record.
| Location |Products Available|
| KNNRLABR | Product 21 |
| KNNRLABR | Product 9 |
| KNNRLABR | Product 7 |
| KNNRLABR | Product 3 |
| VANCWA 01 | Product 31 |
| VANCWA 01 | Product 8 |
| VANCWA 01 | Product 4 |
| VANCWA 01 | Product 3 |
| VANCWA 01 | Product 1 |
Is there a way I can make the lookup run off of the location specific to the current row that I am working on?
P.S. Here is a copy of the SQL statement I'm using right now.
SELECT DISTINCTROW [DSL Product CLLI's].CLLI, [DSL Product CLLI's].Speed, [DSL Product CLLI's].CLLI
FROM [DSL Product CLLI's], [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet]
WHERE ((([DSL Product CLLI's].CLLI) In (SELECT DISTINCTROW [Location] FROM [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet] WHERE [Copy Of Copy Of Customer NPA-NXX's - ADSL - Data - Internet].[Location]=[DSL Product CLLI's].[CLLI])));
Which Returns the Following in my lookup field for Products Available.
I am having some problems with an Access database, obviously.. relatively new at the whole thing, so it's quite a patched-up mishmash, and I would be happy to expand on the intended structure, but it would all come together if there's a way to use a form-displayed GUID as a record source criteria.
Essentially, I can do the following:
Form 1 with autonumber GUID 1 has a text field on it which is equal to the autonumber GUID 1 field for the current record. This looks like a series of random characters, nothing like the long number, and it's set to be invisible. It then works, on a subform, to set record source criteria to include this text field as a way to delimit subform 2 by (non-autonumbered) GUID1.
However, what does not work is:
Form1 has an unbound lookup list that includes GUIDs as one of the things displayed. These are displayed properly, as a long number. I can set a text field equal to the value of this on the main form, and see the same long number. But I cannot then reference this text field as a criteria for the subform record source, and neither can I reference the lookup list directly. Essentially, it works when the text field is showing squiggly numbers, but not when showing the 'real' GUID.
Is there a way to convert the nice-and-tidy looking GUID from an unbound lookup list to something usable as a record source criteria? Or is this always possible, and I am just missing something elsewhere? I can do this through VBA for a _filter_ criteria with the ={guid " & guidvariable & "}))" thing, but haven't found a way for record sources.
The structure is rather convoluted.. would be happy to explain if there's no other way to do it. Replies greatly appreciated.
On a form (default view: Single), I have a bunch of bound controls that display one record at a time from tblMain. I have a bunch of unbound controls as well (buttons).
If the user tabs through all the controls, reaches the last one (btnLast), and presses tab again, the focus is set to the first control (btnFirst) and the bound controls change to display the next record. I just want the focus to be set to the first control without the displayed record being changed.So I set up an "If" statement in Private Sub btnFirst_GotFocus()
Code:
If Screen.PreviousControl.Name = "btnLast" Then
[code to change record back to previous record]
End If
But btnFirst gets the focus when the form opens, so Screen.PreviousControl spits out an error.I've accommodated this like so:
Code:
On Error GoTo ErrorHandler
If Screen.PreviousControl.Name = "btnLast" Then
[code to change record back to previous record]
End If
Exit Sub
ErrorHandler:
Exit Sub
I created a query that shows everyone who has a specific date field blank. Now want to make those names clickable so that it opens a specific form with a certain record related to that individual in the query. Each line with a different individual should open a different person on the form and their corresponding record.
View 3 Replies View RelatedI've created a button on my ACCESS 2010 form that will send a PDF via email. However I only need to send the single record displayed on the form not all records.
The on click command of the button sends the PDF of the report. (I read that this is what should be sent; but still get the same results.)
I don't understand how and where to attach VBA code:
Me.Filter = "RequestsID=" & Forms![OD Reversal Requests]![RequestsID]
Me.FilterOn = True
I wish to change the backcolor of a field on a current record being dispalyed in a 'multiple items' form. However when using me.A.backcolor = makes refrence to all fields called A on the 'multiple items' form and therefore all fields change colour not just the current record.
View 2 Replies View Related