I have an orders form that looks up data from 3 tables (projects, orders and orderDetail) with a subform that looks up data from a query.
The form has several unbound comboboxes relating to the project that synchronise with VBA code
Projects are setup in another form and this form is only used to lookup or add orders to existing projects, so project data will remain the same on the form
At the moment when I press the 'add new order' button, it does not clear the unbound combo boxes and all the data in bound fields and subform is cleared.
When I press the 'Add new Order' button I want the form to clear all the data on the form and subform relating to the order_table, but to retain, or automatically update(with the same data), the data in the fields relating to the project table.
I have a database where I have 3 tables. first is client data, second orders data and third the products data. What I would like, is to have a button on my form that after selecting from a combobox a product, it wil enter it in the tabel orders in the next available free record (row). I created a button and the combobox in the client form. The Products table has also the price per unit in it. It should take the whole row of data and put it in the Orders table.
Is there a way to do this? I guess it needs to be done with vba, but not sure.
Just curious as to what others are working on. Here is what I'm in the middle of and what the basic purpose is:
It's a db to track parts we swap with our customers. It's primary table is on a sql sever so that the anyone in our company can view only the data via DAP's. The other tables are in the .mdb on the file sever. I plan on having it up and running in the next week or so. It will be used (add/edit,etc data), by approx 8-15 users in three different buildings off a file server share folder...
Hello, So far you guys have been helping me through my database that i am building. Thanks so far!But at the moment i need help on one more aspect of this Database Job. Currently i have a database that supports and holds records for jobs that are under contract. That means we have certain numbers and statistics of Jobs and there properties.what i have right now is a table with a couple fields. (there are more than this but this is just for example) project number "primary key" aerial units buried units planning units ICGS unitsThat part works fine with the current table. But since jobs are under contract the number of units might change further on during the design and construction. There may be more than one mod.Now the part that im stuck on is the most important. I need a table/query/form/report that i can input the changes to particular units. Like i said before there might be more than 1 mod. So i need to make it show ALL the mods ive done to a project.This is a huge part, and any help would be very much appriciated. ~Thanks so muchJon
I need to be able to add a feature to an Access project that will select a group of records based on an area code( user will have to be able to select a desired number like 100, or 1000), mark those as selected, and create a single csv file...The area codes are stored in the SQL SERVER database...right now they just export groups of #'s based on area code to excel select the amount they want and cut and paste into a flat file for another application to use...they would like to be able to select a number from a list box or type it in...like 100 ( which means take a 100 #'s from all different area codes) then have them automatically noted in database as being selected..and then export all selections to one csv file... should I do part of the work in the Sql Server behind the scenes..the format of the csv file also needs to be specific in that the first row is the columns separated by commas and then the data follows all text fields enclosed by qoutes.. Thanks for anyone's help ..Im very inexperienced with VB and Access
I have two tables. The first is a list of users. The second is a list of projects. I need to loop through the user list and assign each one to a project. At the end of the user list it will just start over at the top until all the projects have someone assigned to it. What is the best way to do this?
Table1 Users
User1 User2 User3
Assignments
ProjectAssigned To Project1 Project2 Project3 Project4 Project5 Project6
After processing.
ProjectAssigned To Project1User1 Project2User2 Project3User3 Project4User1 Project5User2 Project6User3
My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.
Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".
Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.
Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.
I'm aiming to create a stacked area chart to display the progress of multiple projects over time, so we can review and forecast project load over time.
A project passes through the statuses Prospect, Confirmed, Started, Complete. The current status of each is calculated in qryStatus from the dates in tblProject. The past and forecast statuses for each project will probably be calculated each time the chart is created (or maybe not necessarily so?)
To create the stacked area chart, the x-axis will be months (e.g. Sept 2013 - July 2015). On the y-axis will be the count of ProjectID, and the series will be the different project statuses.
The sticking point is getting from the Date* fields to the past and forecast statuses in each month. After that I imagine it'll be reasonably straightforward to put into a pivot/chart.
I am creating a database that tracks current projects for my team at work.
Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)
Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.
I've attached a rudimentary example db to give an example of what I'm trying to achieve.
There is a form on the db called Resourcing with several combos.
When I choose a Training_Type from the first combo it automatically filters the Project_Title combo to show only the projects that match that training type. When I choose a Trainer_Name the text box for team automatically completes.
The bit I'm struggling with is Trainer_Name. As you will see from the table Course_Details not every trainer can deliver every Project_Title. So what I want is to display only the trainers associated with the project title chosen rather than displaying the whole team in the Trainer_Name combo.
Someone suggested a junction table but I'm not sure how this would answer my question. I'm thinking some sort of Dlookup but don't know how I would write it when it has to include data from four fields.
I am new to access and have a form set for processing orders. How do I process multiple orders i.e a customer orders an onion and a pepper. Currently I have to create 2 orders and would like 1 order with 2 items. I am sure this is very simple. Hope someone can help
I know this is probably very simple but I do not know how to do it. I have: Table 1 - customers Table 2 - Orders
At the moment, when a customer orders 2 items, I have to create 2 seperate order numbers. I would like to have both items under one order number. Can anyone help with an easy solution.
All of your PCs are sold as a complete product with 10 or so components
Your choices are to reenter every item each time you make a new quote, or automate the process.
This is the current method
Step1: Create multiple tables identical with the orders detail table, and create all of the records needed to satisfy the order packages. Each table is one order package.
Step 2: Create an order as you normally would.
Step 3: Change the linking number in the package table (to the appropriate order number)
Step 4: Paste the contents if the table into the Order Detail Table.
This works well but is labor intensive, and is not error proof.
So finely the question. Is there a way to automate this process with the selection being made form either a drop down list or a query .
My gut tells me it a macro thing but I don’t use macros for much and my VBA skills are limited to adapting code that I pick up here and there.
I'm sort of new to Access, but over the past few months I've been able to develop an inventory database for my work which turned out quite nicely and does everything I need it to, except for one thing.
I want to also create invoices for my customers, and after looking at examples I understand that most people link the "order" with the invoice, so that there is one invoice per order. However, I need the ability to combine many orders into one invoice. That's where I'm stuck.
I don't know how to arrange my relationships and forms so that I can "pick" the orders I want on my invoice, and not at the time of the Order, but at some point after. Basically my orders will be established, but their allocation to invoices won't, until I come along and put them into their rightful bill.
Does anyone have any thoughts on this? I'm pretty stumped.
i've now converted a large database to run with sql as well as linked jet tables. The main issue was checking every recordset to ensure it opened properly. I also found that sql returns a few different errors code from Jet, that needed trapping differently = all OK.
Now, in some cases I had opened the backend Jet table directly in order to use seek on a particular index, so this needed changing, as this is not available with SQL
I may well be wrong but i couldn't see an easy way of traversing a recordset table in SQL/ODBC according to a particular segmented key
Instead I was able to modify my process to extract the data I wanted with a query, and I then used a recordset on the query, which is fine.
HOWEVER, to emulate the index I was using, the query is sorted on about 6 or 7 different segments, the first of which is CustomerCode. Now when I opened the query to view it, I changed the sort order by clicking on a date column (as it happens) to sort it. When I then closed and reopened the query it still opened sorted on the date column, which is not right, as the customners are now all out of sequence. When the whole dataset was re-extracted it did return to the original and correct order
THEREFORE Is there a way of forcing a query to reset itself to its original ordering sequence. I think it is not likely that any user would ever open a query directly anyway, but it would be nice to ensure the query is always processed in the intended order. Perhaps it isn't a problem at all - maybe when a query recordset is opened in code, the sort order is reset anyway, but I would appreciate some views on this.
I am creating a database for a company that sells a product with a variety of options.
They have all their previous orders in a works spreadsheet file. Each customer has their own file with every order for the past 15 years. There are probably about 1.5 million records.
The company wants all those 1.5 million records accessible in their access database.
I've brought in about 20 records for the history and they can run a query to see a customer's past orders by their account number.
My question: Should I put all the history into one gigantic table or would it be best to try and create separate tables for the history? (Maybe history by state.)
The history table then will take the new orders each year and add to that table.
(Eventually, once the database gets done I'm thinking we'll have to step up to SQL but not sure.)
How can I preserve the user-defined sort order in a column in a table?
Every time I exit the table, and go back, my items have been alphabetically sorted. This results in my Combo Box displaying the list in alphabetical order, rather than my custom order.
I’m trying to make a simple program form y Company as enclosed. But since I am new about Access I don’t know how I can solve my problems certainly so, I need your help about.
We are getting orders from the customers and we are buying from domestic market. I want to make easier to send purchasing orders to my suppliers. Normally, as you can see from the enclosed file sales person enter the order to the system (Orders TARIK KARABAY form). We want to use these data while preparing purchasing orders but we are buying some of the goods completely manufactured by our suppliers. We mean we buy the item and sell directly to the customer. This step is easy. At the same time we are buying parts from suppliers and assemble in our warehouse. I mean we produce them. This is the problem I have. One of the goods we buy is HTS3; we buy directly as HTS3 from the supplier or we buy HT3 from one supplier and R3 from another one and KD3 from a third one and we assemble HT3, R3 and KD3 in warehouse and we obtain HTS3.
While buying as HTS3 it is easy as the only thing is to add Supplier name to the system. But while it would be produced in house, the system has to prepare purchasing form for HT3, R3 and KD3.
Purchasing department will see the Order Form, they would prepare the purchasing easily without writing again.
I don’t know what kind of tables and forms I have to open to solve this problem.
I have a database of submitted orders that were temporarily held by our Order Management group for any one or more than one reason categories. There are 20 possible errors that could cause this temporary hold to occur. Once the error(s) is/are fixed, usually by getting missing information, then the order is released to the fulfillment group.
The basic information needed is the average duration between the Hold Date and the Release Date for a particular reason. So, in the table, the fields of concern are:
HoldDate ReleaseDate Reason1 Reason2 Reason3
etc. The Reason fields are Yes/No.
I want to be able to get a report that shows the average duration for each reason, but all in the same report. I can get it for a single reason, but without writing 20 separate queries or putting 20 formulas in my report, I can't seem to get what I need.
Hi I am looking for a formula for my query which filters out only the orders raised in the current week.(5day week Monday to Friday) I did not want to enter dates manualy. Can this be done ? Can anyone help me with this.
is there away to filter rows when certain values in three different tables are the same. e.g. it is a stock control db - so when goods orded = goods in = goods taken - can this then be filtered automatically to hide rows and avoids congestion on the user interface (form)?
I have an orders form, which makes the orders, but I would also like to be able to search back through old orders to append them in the same form.
Currently I have frmOrders, which has a combo for the customersID (which then adds their details to fields). In this form I also have frmOrderDetails as a subform, which is used to input the products.
Basically I would like to add a combo box which displays the order no. and the customer who's order it is. By clicking it all the order details will appear in the form. I've tried the way I thought, which does bring these details up in the combo box but it doesn't change the rest of the form when I click it!
Is this possible, and what's the best way? Thanks.
I am new to this forum :) and learning Access :confused: . The Database Tables: Employees Products ExpectedQuantity PartQTY Parts
Most of the products are similar so they have the same parts for the majority. However they do not all have the same parts. So I created a parts table that contains all the parts needed for any product. Now each Product also can contain multiples of one part (such as wheels) where I would want to list the Product and the parts list and quantity needed of those individual parts for that product. Hence the PartQTY table.
Now what I want to be able to do, is go into a form where each record is one of the products. A subform (datasheet view) for that product that shows all the parts and the quantity of each part that make up the product. Such as 4 wheels, 5 screws, etc for Product A.
I would like there to be an ExpectedQauntity field in the main form which once you found the product you can enter in the number of that product expected. Then this Qauntity would be multiplied by the qauntity of each part required (in the datasheet subform a column with =[expectedQTY]*[PartQTY]) and list next to each part the number needed to order. So if for product A the expected quantity is 50 I would have to order 200 wheels and 250 screws for that product.
A report would Sum each part qauntity after the expectedqauntity has been entered for each product. So I could print a report that tells me how many of each part we need to order based on the expected qauntity of all the products.
I think I am complicating things and would like a fresh mind to assist me in which direction to go.
