I've been working on a database for the last month or so. It's been a slow process since I've been learning Access and VBA in the process.
But my supervisor wanted a copy of it as a progress check to send to his boss. So I sent an email with a copy of the database as an attachment.
THe email went through, but when my supervisor tried to open said email, a dialogue came up saying that Access couldn't open the file because it was "out of the intranet or on an insecure site" or something along those lines
I was just wondering what this meant and how I would be able to send my boss a copy of the database so that it can be reviewed and such. Would this require splitting it?
I'm having trouble creating my query I have a table representing the company's products. Ever once in a while the products get replaced by a newer product. Like below _____________________________ Product - Replaces - Replaced By a..................................b --------|----------|----------- b.................a...............e --------|----------|------------ c --------|----------|------------ d ------------------------------ e.................b
Can someone help me write a query so the result below will be shown in three fields.
Product - Replaced by level 1 - Replaced by Level 2-Replaced by level 3 ....a..................b.......................... . e......................and so on
Hi, I’ve been having some problems being able to do some things on my database, I was doing ok creating my system but now I’m a bit confused about what to do and I’ve been trying but getting no where. Firstly on my order form I need a total for the products which are in an subform and after the order is complete I need to be able to create an invoice and keep all the order information in the 'invoice' and 'invoice details' tables. I also need it to update the stock levels after the order is complete. Then I had created a tab in 'view customers' and I wanted to have it so that past customer orders could be viewed. Any help is appreciated; here is a link to my system: 'Gamez System' (http://www.savefile.com/files/5877934)
Hi! I have a problem with my form [EDITAR], the problem is that i did a form basing me on a table, but now i want to create a command that when i press it, it has to let me edit my data. i don't know how to explain me very well. i want to change my data but changing it with my form and save that information.
the other problem is that i want to create the same form for but only to add new data on the same table, i want to create a command that i will press if i want to add new records.... help i really need help:confused:
I have a table with 2 fields (pr_row, pr_col). All i want to do is: I want to update the table so that every 21 records, the pr_col field will increase from 1 to 21 and so on, and the pr_row field will remain with the same value (but increasing by 1 every 21 records) Explaining pr_row-pr_col =========== 1 - 1 1 - 2 1 - 3 . . . . 1 - 21 2 - 1 2 - 2 2 - 3 . . . What is the update query that i must execute?
I am having difficulties with working out how to create a correct query from my database. :confused:
These are the two results I require:
What strength of whisky sells the best at Christmas (How do I do an 'In-Between' date condition regardless of the year? i.e. >= #01/12/____# AND <= #31/12____#) Which companies have not purchased at least one bottle from each region?
However, I am not sure how I would go about getting these specific results from the database. I have done serveral previous questions based on the database (which can be seen in the .mdb file) however these two are proving troublesome. I have attached the database to this post if anyone who is at ease with queries would be kind enough to have a quick peeky at it.
Any help would be greatly appreciated !
Note. the database information is fictisious. :rolleyes:
Hey, I have an access database that imports a certain range of a certain sheet from 30 files all of which are in the same folder and I am trying to get that BOTTOM MOST peice of code to automate the process.
Now one of my many complications was that the excel files are workbook protected, and so it would give the 'cannot decrypt error' that’s why I have all that code regarding unprotecting it... but for some reason its not working
It gets to the line where it gives it the password and it tells me the password is unaccepted, and yes the password is correct...
It must have something to do with the do loop, because it works fine whenever I used it over one import file and thus without the loop... Like this for ex works perfectly fine
So Please tell me what is wrong with this bottom piece of code...
And secondly, I have a table which contains a list of names, whether they are active (checkbox) and their filenames each on a column of its own...
Can anyone show me how i can include into my code (assuming the password problem is fixed) that would allow me to place an if statement that checks whether the user is active or not... IF he is then it imports his file (using the filename column next to his name).... If he is not active then it just goes onto the next person without importing him/her
Right now I have it importing all the files in that folder
Code:Option Compare DatabasePublic xlapp As New Excel.ApplicationPublic Sub ImportAll() Dim strPath As String Dim strFileName As String strPath = "G:CBT" 'Set Path strFileName = Dir(strPath & "*.xls") 'Set first file Do On Error GoTo ErrTrp DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" ErrTrp: If Err.Number = 3161 Then 'Encription error so unprotect workbook xlapp.Visible = False 'Open Excel xlapp.EnableEvents = False 'Disable Events (Macro's) xlapp.workbooks.Open strPath & strFileName 'Open File xlapp.ActiveWorkbook.Unprotect (blah) 'Unprotect 'Try and Import again DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" xlapp.ActiveWorkbook.Save 'Save xlapp.EnableEvents = True 'Enable Events xlapp.ActiveWorkbook.Close 'Close File xlapp.Quit 'Quit Excel Else End If strFileName = Dir() 'look for next file If strFileName = "" Then 'no more files Exit Do End If LoopEnd Sub
Please let me know what I can do with this I am a novice at coding...Just tell me where to put the code
i stumbled upon this site via google while i was trying to find a solution to my problems! i'm a pretty proficient pl/sql writer and this is my first time trying to learn jet sql so a lot of the things i'm used to in pl/sql can't be done in jet sql!
1) i'm trying to create a summary record from RR_FT_COMPONENT to RR_FT_TOT_PAYOUT. this is basically summing some payout fields by grouping by payee_id and period_id. from my research i've seen that jet sql doesn't like sum or group by's for updates. is it the same for doing select statements for insert into?
when i run the query i get the following error: "ms access can't append all the records in the append query. MS access set 0 fields to null due to a type conversion failure"
all of the fields on both tables have the same type. is this a group by and sum problem? i ran the select statement separately and it worked fine.
SELECT max(rc.market), max(rc.region), max(rc.financial_center), rc.payee_id, max(rc.employee_name), max(rc.manager_flg), max(rc.lic_code), max(rc.job_code), rc.period_id, sum(rc.volume), sum(rc.cp_spread), sum(rc.ytd_spread), sum(cp_comm), sum(ytd_comm), max(prior_yr_flag) FROM RR_FT_COMPONENT AS RC GROUP BY rc.payee_id, rc.period_id;
2) in this next code i'm trying to sum spread information in the ft_txn_summary table for a given payee_id, market and period_id. i'm using dsum and have the appropriate joins to keys on the tables, yet this update sums ALL records in the ft_txn_summary table rather than summing the specific payees in the join. any idea on what's wrong or how to debug dsums?
code: UPDATE rr_ft_component AS rc SET rc.cp_spread = Dsum("txn_spread","ft_txn_summary","ft_txn_summary.payee_id= " & [rc.payee_id] AND "ft_txn_summary.market= " & [rc.market] AND "ft_txn_summary.period_id= " & [rc.period_id]) WHERE rc.component_name='Total Revenue';
I am a newbie at Access and am basically designing my first database. This database is for a short term project and is designed to track the attendance of employees at acompany. Here is a brief description of the tables and forms in question.
EmployeeInformation - this table stores records of each employee, their ID, DOB and SIN.
AttendanceProfile- this table stores records of prolonged absence periods for each employee. A given employee can have many attendance profiles, meaning that this table is linked to the EmployeeInformation table via a one-many relationship. This table will provide detailed information about the status and history of each Profile. So for example if an employee misses work for 12 days due to an appendicitis operation, the exact nature and dates of this absence would be listed as a new profile. The primary key for this table is an autonumber field called ProfileID
ActionLog - this table stores the actions taken by company employees (if any) in response to each AttendanceProfile. It is connected to the profiles table via a one-one relationship with ProfileID being the foreign key in this table. Another main field is the ProcedureNum field which will list the number of each procedure taken for a given ActionLog record. This table is needed to track what the company has done/is doing to track an employees prolonged absence.
Allow me to illustrate with an example. An employee, John Doe misses 15 days in June 2001 due to an illness in the family. An AttendanceProfile record is then made for this period illustrating the exact nature of Mr. Doe's absence as well as tracking what official documents he has submitted (eg medical note, official company documents). Another matching record containing the same ProfileID is also made in the ActionLog table describing what the company has done so far to track this absence. So if an HR employee calls Mr Doe's physician to verify this illness or sends a letter requiring further documentation, each of these steps is listed in this log for this absence profile.
Sorry for the long-winded explanation, but better now than to have to clarify myself later. Here is where I'm stuck. I would like to set up two data entry forms, to create new AttendanceProfile records and related ActionLog records. Obviously, these corresponding records are linked directly by the ProfileID field. After completing the AttendanceProfile form, I would like the user to be able to click a command button to open another form (I am avoiding a subform in this case because my attendanceProfile form is huge and I cannot conveniantly fit a subform on it) to open a new form where they can enter Procedure descriptions and dates on the ACtion log. For each new procedure the user enters, an autonumber fields automatically increments the procedureNumber for that specific ProfileID.
Finally, my question to any of you is, assuming that I have already designed the AttendanceProfile table and form, how shall I design the ActionLog table AND forms to accomplish this exact task.
Once again, sorry for the excessive detail. Your assistance is greatlys appreciated.
Sorry to drag this forum back to the nursery school but.....
We have set up a database for recording manufacturing trials. The trial details (such as date, product, nature of trial, person responsible etc) are entered into a form. At present this form is then printed and distributed for authorisation and to inform people of the trial. It would be much more convenient to be able to email the completed form to interested parties so they could review it in advance.
I have seen references to emailing tables etc from Access but do not know how it is done. Can someone explain it to me please in terms an idiot could understand!
I have a combobox named: Champion the control source is EmailChamptionID, based on SQL statement SELECT tblChampions.EmailChamptionID, tblChampions.Champions, tblChampions.cemailaddress FROM tblChampions;
I want the user to be able to select the persons name from the box, which has an email name associated with it cemailaddress. When a command button is selected the person is supose to get emailed, but I keep getting a compile error "method or data member not found" .cemailaddress
the line is: Private Sub cmdNotifyChampion_Click() DoCmd.SendObject , , , Me.EmailChamptionID.cemailaddress, , , "TS16949 Non-Conformance #" & Me.Text19 & "", "hello", False, ""
I've also tried
Docmd.SendObject , , , Me.cemailaddress, etc
I guess I'm refering to the email address incorrecly, how would I write it?
I apologies for this thread, I was desperately attempting to avoid the need to post for help, especially as this is a much discussed subject. However, after three days of reading hundreds of threads and searching the web (not to mention winding the wife up) I am more confused than ever.
So, please bear with me, I have tried but everything I've attempted so far has failed.
My query centres around multiple e-mailing. I have a table from which a query runs. The query is called QryAddressBookEmail. The query will bring back anywhere from ten to thirty plus results, dependant upon the search parameters entered. From the query results I would like to e-mail each record. The email address is contained in the query field Email.
I'll be using Outlook Express for the emailing.
Rather than sending each e-mail separately I would prefer if they went as one, (IE Pete@..... , Joh@.... etc) thus avoiding the 'silent email warning - confirm to send' that appears to accompany each e-mail
For the subject and body of the e-mail, if possible, I would like two fields on the form that are populated by the user. These are named 'Subject' and 'Body'.
Again, if possible, the email is to be sent by clicking a command button.
So.. in theory, the user will select their search parameters which brings them back a list of hits. They then enter their subject line and the body of the e-mail directly onto the form and then hit the command button which will send a single e-mail to all those identified.
Also........ Whilst its not essential, anticipating future developments and other peoples wants, could an option be added for the user to attach a document by providing a path for it via a field on the form?
And..... if possible and time permits. Again, anticipating other peoples questions and problems, could an alternative be provided that e-mails each record seperately.
As you've probably already guessed, my VB is limited but I will give it my best shot.
Thanks in anticipation to anyone that can help. It is appreciated the time it takes to answer SFQ's from people like me.
Now this works but it opens an individual email per email address into the BCC part. That a major problem if you have over 1000 contacts to send to. How do i get all my email addresses from my DB into the BCC of an email?
Hello, I am looking for some help on what appears to me to be a very large problem. Hopefully, to others, it's an easy fix. I have a very large database that has several details, of which one is email address.
My report is structure to group first on the email address, 2nd on the cost center, and third on the exeption. I am emailing the report to each individual email on the report. I can get it this far, what I can't get is for the email to only mail out each part of the report that is strictly for that particular email address.
I know this isn't very clear, but right now the entire report goes to each email address, I only want specific pages to go. The kicker to it all is that if I set specific parameters the first time, then I would have to set them every time, because the report varies in length each time it is pulled.
I have set up a database that holds wedding list details for our shop. The product images are not held in the database itself - I have linked to them using VBA as outlined in the microsoft access help pages.
Now I can print a report absoluely fine, but I can't capture the report to send as an email to wedding guests. A snapshot isn't good enough as I don't want our customers having to download software. I thought maybe I could export the report to Word and then email that as an attachment. However, when I export the report, there are no images in the resulting Word document.
Does anyone have any ideas? I primarily need ease of use for the customers picking up the email at the other end.
I'm a student It's just my first time to program in MS access and my project requires to automatically send email reports from MS Access...how can i do it??
I've read through many of the threads relating to sending Email from Access but can't seem to find quite what I'm looking for. I work as a Tutor Coordinator at a local college and I've set up Access to handle a lot of the day-to-day chores that our administrative assistant has to deal with.
One feature that would be absolutely wonderful would be to create and send Emails to students at the click of a button. I have several types of Email I'd like to be able to set up, but the one most needed (and probably most complex) is this:
Students come in to the office and request a tutor. If we have a tutor available, we assign them to an available tutor. If there is no tutor availabe, then every Friday we send out an Email to each individual student saying something like this:
"Dear John Doe - We currently do not have a tutor for MATH 101 at this time, but when one becomes available we will let you know... blah, blah, blah..."
I'd like to set it up so that the body of the message comes from a template - such as a text file (that is easily edited by a user) and populated with fields from a query.
Probably do something like this:
Query the Student_First_Name_field, Student_Last_Name_field, Department_Name_field, Course_Number_field, and Student_Email_field. Send an Email to each Email address pulled from the Student_Email_field and tell them the following - "Dear <first name, last name> we currently do not have a tutor for <department name, course number> at this time... blah, blah, blah..."
Currently the way the administrative assistant handles this is to send each student an Email individually - manually creating each Email with the specific data needed. As you can guess, this is quite time-consuming.
I'm rather new to Access but have been a RDMS programmer (in PICK) for several years. I know what it is I want to do, but don't know how to do it in Access.
Any help?
Would the best way be to use text files for templates?
Can text files be set up with field codes (Access recognizes "<LAST NAME>" to be Student_Master_Table.Last_Name_field)?
I'm guessing this will likely have to be a Visual Basic loop that works with data from a query.
I have a form that the user will fill, then email it to a customer. I dont want to send the whole table, just the values that the user just inserted in the form.
Any idea on how should i do that using Access?
I am aware of the SendObject method, but i never used it, and dont know how. Any help will be very apreciated.
I am using Access 2000, Windows XP and Groupwise 6.5. I am trying to email an employee leave report using the SendObject method. I would like to use the SnapShot format. Must I save the report before I send it? I tried acFormatSNP with no success. Thanks for all your help.
Im building a database that is required to send out a email once a week to selected parties informing them of what items will go out of date this week.
The main problem im trying to over come is the Automatic email.
Access 2000 Wondering if there is anyway to setup a report to automatically run and e-mail out at a certain time each day? I currently have a button on a form, based on a macro, that when clicked, will e-mail the report to a certain user. Just wondering if there is anyway to set it to send at a certain time, without having to open the database and manually run it.
We are a facility maintenance company that do a variety of jobs for many commercial companies with a several different trade groups we have (electrical, hvac, plumbing, painting, masonry, etc...) Our Trade Group Managers submit estimates and budgets for the jobs they are bidding to. I created a database to store all those jobs so we can track what manager has bid to any certain job, what trades are involved, if it has been accepted-rejected-in progress-and such. As of now the managers turn a written (paper) form into me to submit an estimating inquiry request and I put it in the database and print them out a receipt of all the information they submitted to me along with an Inquiry Number (which is the primary key in the database)-then that is how we keep track of that particular bid-with that unique Inquiry Number assigned to it. Now we are wanting to be able to have this all electronically. So the managers can submit an inquiry request electronically and get a receipt back electronically-either by email or online or whatever. On the form they fill out there are several different trades that are able to be checked off. After they are finished filling out the form I would like for there to be a button at the bottom to email that form to all the checked off trades. How do I go about doing that?
..any suggestions would be greatly appreciated. Thank you so much!!
From the searches of this site Ive got it down and working to send an email with attachment via access. Thanks
The one thing I cant find is how to change the "From". I don't want these emails send via my name but my department. I usually just change the <From> to the department name in Outlook and send.
I cant seem to figure out how to make it work in access.
I am trying to automate the process of sending a query result via email.
I have code that will export a query as an excel document, using DoCmd.SendObject, but it has the name of the query.
DoCmd.SendObject acSendQuery, "Dailyrpt", acFormatXLSX, email.address, , , "Daily Report", "This is today's report", -1, False (email.address would actually be the email address it is being sent to.)
I also have code that will export the query to excel and save it as a file, using DoCmd.OutputTo, where I was able to name it something meaningful to the end user, and include the date.
DoCmd.OutputTo acOutputQuery, "Dailyrpt", acFormatXLSX, "Z:FolderComplete as of " & Format(Date, "yyyymmdd") & ".xlsx" Dim Foldername As String Foldername = "Z:FolderComplete as of " & Format(Date, "yyyymmdd") & ".xlsx" Shell "C:Program Files (x86)Microsoft OfficeOffice14EXCEL.exe """ & Foldername & "", vbNormalFocus
Is there a way to either send the saved (renamed) file or to include the current date as part of the SendObject command?
