Reports :: Save Access Form Based On Order Number And Client Name Using VBA
Apr 28, 2013
I have an acces form to input client orders. Each order has its own order number a different client. On the same form I have a button to generate a report based on the inpuuted data on the same form which works perfectly fine.
Now I need to use same report to save each order in a specific folder on my computer in pdf format. However I would like to have the file generated as follows ordernumber "-" clientname.pdf. This way each order is saved in the same folder without overwriting the previous one.
I am looking into re-creating something I have created in Microsoft Excel into Microsoft Access.What I do in the Excel spreadsheet, is when I type in an order number, various fields in the spreadsheet are populated using vlookups and ODBC's.
I wish to re-create this in Access but not sure how. I have a basic form, with various fields such as, order number, part number, description, quantity.What I have in my mind is that I would like to be able to type in an order number, then the rest of the fields would be populated by a query to a SQL Server database.i can see a way to do this using VBA but would rather look for a more direct way if possible.
I've been asked to edit a current report that lists our production in week number order. I need to look at way we can 'flag up' orders that are within a 4 week period from the current date.
For example,
10 Orders in total in our database. 5 of which are due the drawings back within 4 weeks from today (26/02/15). I'm looking for a way for the report to show the 5 orders as priority, either by formatting the orders in bold, a different colour or under their own heading/group.
I have report which record source is a Union Query, The query contain Order Details and a single row for "Freight Charges", I just want to set Freight Charges show in Report at last row.
I have created a small database, and created a Main Menu (form), the main menu starts as soon as someone start my database, i have linked up all my forms & reports into this main menu. now i want to hide left panel bar that shows all the table, quires, forms & reports files (so nobody can access tables, quires, etc from that panel).
When I make the column graph the data seems to not be in any order. The report is in order by the number of days in ascending order.
How can I have the columns with the largest number show up first and the columns get smaller. I would like the largest column all the way to the left, proceeding the next one in size so it goes from largest to smallest.
This error occurs at the OutputTo line. This code is part of an user form with a button “save as PDF”. When this code is run, the report is visible as an example (as meant to be) on the screen. The code stops at the OutputTo line.
When I use DoCmd.OutputTo acOutputReport, StrReport, acFormatPDF, StrDocumentName,, False the problem still exists.
I tried to save the report manually by Preview > Export > PDF. The preview is OK, saving as PDF results in the same error 2501. This may lead to the conclusion that this problem is not caused by the VBA code.
I verified whether user write rights are OK, and whether a default printer is available. Both are OK. The report consists of 1 page. When this very same application is used with Access 2010, everything functions as expected.
For my database the business has Clients who they conduct Needs Assessments for, but they only conduct 3 needs assessments. I have a Needs Assessment table with Client ID as the foreign key. how I can limit the number of times a Client ID shows up in the foreign key field to three?
I have two tables(see below). I want to set up a query, link these 2 tables together. I set a one-to-one relationship between Client ID in two table. But got error message :"Type mismatch in expression".
I tried to change Client ID data type from "Text" to "Number", then Access deleted some data under Client ID in Order table.How can I make this work, but not having to re-type in all data?
Client Table:
Client ID(Autonumber) Client Name (Text) Client Address (Text)
Order Table:
Order ID(Autonumber) Client ID(Short Text) Unit Order(Number) Unit Price
We have Cardiac Rehab patients that come 3 times a week (M-W-F). Once they are on program they stay there for 3 months. There are classes given hourly from 8am thru 6pm. There are 12 available slots per class. Right now this is being handled in Excel, but I thought I might improve things by putting it in Access.
The Excel "form" I built has fields for PatientName, MedicalNotes, DoctorName, and Notes. Because the names rarely move (a patient gets a slot and stays there) the user opens Excel, reviews the form to make sure no one cancelled-is out sick-etc, then prints the schedule. When the day is complete I have a macro that takes this information and saves it in a tablelike manner on another sheet. A dynamic pivot table references this info to give the user quick answers as to daily volume, etc.
The thing I can't get my head around is how to make this as easy in Access. My thought was I could have a PatientT, listing all of the fields mentioned above. I could have DailyF open, bound to PatientT, with additional unbound textboxes to add "today's date", cancellations or no shows, and then Save all of this in another table and print out the daily schedule.
I currently have details of my clients in a table called tblSites, this tble includes a field with an email address. I currently produce a invoice which is automatically saved as a pdf and an email generated with the invoice attached. However, a few clients do not have email addresses (these are sent by post). My code (see below) will successfully produce the email with the attachment, but if the client does NOT have an email address in the tblSites it comes up with the "Invalid use of Null" error. What I want it to do is continue the process of just saving the invoice with a Msgbox stating "This Client does not have an email address etc etc.
'Save Invoice as PDF and annotate Invoice Number, Date and site name
Dim StrFile As String Dim slSQLString As String Dim rsEmailAddress As Recordset Dim slEmailAddress As String StrFile = "C:Redwatch Invoices" & Forms!frminvoice!txtInvoiceNr.Value _ & "-" & Format(Date, "dd-mmm-yy") & "-" & Forms!frminvoice!cboSiteName & ".pdf"
I am trying to print a report for a specific Client. There are several routing records with dates. What I want to do is print the report for a specific Client that is in the primary table and a Date within the routing table. Each routing record has a Date field.
One client can have many routing records. Example: John Smith in client Routing record in the Routing table. I want to report on one Routing record with a specific date.
Brand new on here and desparate for some help and guidence.
So far with Access I have just used it as a store of addresses to mailshot prospective clients.
However, I now need a more complex database and this is where you might be able to help.
First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.
Does this mean I need to do three tables:
1/ "University Details" which gives the address details 2/ "Department" storing the departments of the unis we work for 3/ "Client" Name of the client(s) in that department.
I have a table called Client Info which stores the clients name and their requirements (Gas safe etc). And I have a table "Job List" where i select from a drop down menu the clients and it auto fills all the requirement check boxes based on what is stored in the Client Info table. However I can't get the code to work.
Private Sub Client_Click() [job_list].[Gas_Safe].Value = [client_info].[Gas_Safe].Value End Sub
I have a few selected reports on an Access 2007 database that users can run. Is there a way for users to view the report, save as a PDF and automatically save a copy to a shared drive by modules/vba coding as an On Click event procedure?
I have an access DB.I have an XLSM Data Sheet.In the XLSM I have A few Rows with Fields.In the Fields I have a few matching fields as in the Access DB.I also have a few extra fields that i want to add to the Access DB
I want the new fields of the ADB to Populate from the XMLS in the proper rows based on the ID number in the ADB.
I put the formula in the Text box field in the control source in the property's tool box and i don't know if it will save that calculation to the table. I was wondering if i can do VBA code to save the the calculation in the table.I have a field called CA Weigt and i put the formula in there as =[Weight] * 0.4536 and it works. How would i put it in a vba code.
Im trying to print a report from a form. I want the report to have the contents of the forms current record.
The underlying table has an autonumber as it's index/primary key and it's this im referencing. What happens is a record is saved and the autonumber is generated.
DoCmd.OpenReport stDocName, acPreview, , Me.RefID.Value ' the value of the forms id box
= [RefId]' the value on the report
The problem i have is this:
when i ask it to return the record which shares the id number of the current form record i get a blank report with no error.
when i ask it to return the record equal to a specific record number - by swapping the varibale for a fixed value i get all records- can anyone suggets what is going on here?
I've made an adp (access project ) file. I want to permit some users to look in the data but i'm not certain they have access installed, can i give them read-only rights by placing a data-access page in a network map? Can they use the functionality of a data-access page without having access installed?
My Access 2003 Database contains the following objects:
1. tblTrackerData - this contains over 1,000 student enrollment records. One of the fields "QualCourseName" (text) contains the name of the Training Course that the student has enrolled in.
2. qryCourseNamesGrouped is a query based upon the above table with one field only - QualCourseName. This includes the names of the training courses and has been grouped so that only 17 training courses appear, not over 1,000.
3. frmParameter is a form that includes a combo box cboFiltered based on the above query AND a command button CmdFiltered to open a report rptCourseNamesGrouped and filter the report based on the selection made from the combo box.
I have added the following code to the OnClick event attached to the cmd button" ================================================== ======
Private Sub cboFiltered_Click() On Error GoTo Err_cboFiltered_Click Dim stDocName As String
[Code].....
When I click on the cmd button, the entire report is opened, instead of the selection that I made in the dropdown list.
This post is really aimed at someone that has experience of network databases and the problems they can pose but if you can help at all that would be great!
I work for an insurance company and we have created a database for registering complaints on. The one we currently use is a single .mdb file which can be shared by up to 60 people at any one time. This is creating a lot of problems when the queries and some VBA code are run. I therefore made a server file which is just an mdb file with data tables in and NO forms or queries. This file is stored on a shared drive on the network server. We then created a client file which contains all of the forms and queries and code. This file contains linked tables to the server so when data is entered into the form, it "Sends" it to the server mdb file. The client is installed on each of the local machines c:. The problem with this was that if we made a change to the database, we would need to reinstall the client on every single user pc. This would of took ages. I therefore made an auto-update function that checked the version number on the server and if the server number was greater than the client number, a simple .bat fiile was run which copied the updated client file from the network to the relevant users local disk. The problem we have now is that our IT department are concered that if we make a change to the client and all the staff log in at 9am for example, it will start doing multiple copying of a file around 8mb in size to around 60 machines. They are only running on a 2meg pipe so this could cause some problems. We are not looking to change the client & server idea but does anyone know if this will have a big impact and infact if the updates for 60 machine is the equivalent or lesser of 60 people sharing the single file i mentioned earlier. If the IT dept are happy with 60 users accessing the same file at the same time, which they currently are, why are they unhappy with it updating these machine using the new method.
Would really apopreciate any thoughts anyone has..
I have DB in access 2007. I have a report that is uses a select query to generate the information for the report. It has been working great, But however lately like maybe with in the last month, it has been causeing Access 2007 to crash. I am having the same issue with another DB that uses the same information but that information is imported in. both Databases have worked great up until two months ago. Microsoft states that it is because of the program. I have tried to repair the DB by using the Repair option. I am confused as to why this would be happening. I can create a new report and it seems to work. but I do not want to change all the DB on everyone's computer just for this reason. I also have two buttons on my report that utilize macros to close or print the report.
My problem is the following: when I receive say 5 computers in a purchase form, I want to register the serial number of each of them in another form, bound to another table.If I receive 2 units in the purchase form, my user should only be able to input 2 records in the serial form (a continuous form), if we receive 3, then only 3 records,I cannot quite figure out how to build this second (serial number registration form), so that it refuses input after the correct number has been reached.
