Maintaining Unique CustomerID With Multiple Purchases
Mar 28, 2015
I am trying to make a basic database system to track orders and contacts and the like for my small business. I have created a series of databases that look pretty standard, something like this example:
I want to create a form that I will open and complete every time I process an order. I want to fill out both the customer info and the order details at one time.
Assuming that the "CustomerID" in the "Customers" table is set to Autonumber in order to create a unique ID, how do I keep from creating multiple new "customers" every time I enter a new order regardless of whether or not that customer already has an ID number from a prior order?
It seems that I would need to make some sort of Macro that would say something like, if the "CompanyName" entered is already listed in the 'Customer' table, use the existing CustomerID, if it is not listed in the Customer table create a new CustomerID.
Should I be looking into Macros, and if so should the macro be built into the form or the database?
Hello, Im having a bit of a problem. I have created a form with 2 sub forms. The function of the form is to purchase ammunition and/or item. I need to have 2 different sub forms as ammunition and items have their own regulations and rules.
the code I have used works fine if the user only makes one ammunition and item purchase, but as soon as the user inputs 2 items or 2 ammunitions it dosnt remove the items from stock.
here is the code I have used:
Private Sub Command10_Click() // this is on main form used to make the purchase
If [Forms]![purchase].[Form]![item purchase subform]![item number] <> (" ") Then If [Forms]![purchase].[Form]![ammunition purchase subform]![ammunition number] <> (" ") Then
Dim Counter As Integer Counter = DCount("*", "Qry check amo")
If Counter <> 0 Then
If [Forms]![purchase].[Form]![item purchase subform]![Purchase Quantity] > [Forms]![purchase].[Form]![item purchase subform]![Stock Quantity] Or [Forms]![purchase].[Form]![ammunition purchase subform]![Purchase Quantity] > [Forms]![purchase].[Form]![ammunition purchase subform]![Stock Quantity] Then MsgBox "Not enough items in stock" Else [Forms]![purchase].[Form]![ammunition purchase subform]![Stock Quantity] = [Forms]![purchase].[Form]![ammunition purchase subform]![Stock Quantity] - [Forms]![purchase].[Form]![ammunition purchase subform]![Purchase Quantity] [Forms]![purchase].[Form]![item purchase subform]![Stock Quantity] = [Forms]![purchase].[Form]![item purchase subform]![Stock Quantity] - [Forms]![purchase].[Form]![item purchase subform]![Purchase Quantity] MsgBox "Purchase made" DoCmd.Close acForm, "purchase", acSaveYes End If Else: MsgBox "You are not entitled to buy this ammunition type" End If
Else If [Forms]![purchase].[Form]![item purchase subform]![Purchase Quantity] > [Forms]![purchase].[Form]![item purchase subform]![Stock Quantity] Then MsgBox "Not enough items in stock" Else [Forms]![purchase].[Form]![item purchase subform]![Stock Quantity] = [Forms]![purchase].[Form]![item purchase subform]![Stock Quantity] - [Forms]![purchase].[Form]![item purchase subform]![Purchase Quantity] MsgBox "Item purchase made" DoCmd.Close acForm, "purchase", acSaveYes End If End If
Else If [Forms]![purchase].[Form]![ammunition purchase subform]![Purchase Quantity] > [Forms]![purchase].[Form]![ammunition purchase subform]![Stock Quantity] Then MsgBox "Not enough items in stock" Else [Forms]![purchase].[Form]![ammunition purchase subform]![Stock Quantity] = [Forms]![purchase].[Form]![ammunition purchase subform]![Stock Quantity] - [Forms]![purchase].[Form]![ammunition purchase subform]![Purchase Quantity] MsgBox "Purchase made" DoCmd.Close acForm, "purchase", acSaveYes End If End If
I have a form with a "WorkerID" field. Once the worker enters his ID once, I want the same ID to show up for all the subsequent records so that he doesn't have to type it over and over again. Is there any way to do this? Sorry if I sound totally new to this (I am)!
In a school, a customer ID (in a canteen for example) would be either student administration number or staff initials.... how can i make this customerID field so that it accepts either all numbers (for student admin. no.) or all text with only 3 letters (for staff initials)?
I am working on a database of biological samples and test data. A problem is that many times the same subject has multiple ID's. For example, one subject may have the ID "ID234" but they previously had the ID "Sub84" or something like that. I want to be able to have 3 or 4 fields that have ID's for a subject, and I don't want a single one of them to be repeated. So I have column a, b, c, and d, and I don't want to have one record to have the same ID in column a as another record has in column d and so on. All I have been seeing is a way to make sure all of a, b, c, and d are unique combinations, but I want none of the fields to be repeated.
Two tables, in one there are Items and services, in the other purchases.
1st table: ID - autonumber primary key Product Name - Text Quantity - Number Barcode - Text
2ndt table: ID - autonumber primary key Product Name - Text Quantity - Number Barcode - Text Client - Text Puchase Date - Date
How can I enter data to my 2nd table using unique fields (barcode, Product name actually ID in 1st table?) Problem Is Access alows only 1 unique key field in table and that is undertandable. Is there any way out in my situation.
barcode will be entered using barcode scanner it can be only text field but information like product name must fill in automatically, Product name must be drop down box if one wan't to select it manually then barcode should fill in automatically.
Please Attach your DB if you have time to look into this issue.
I have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.
I already have created & run an Access database for around 12 users. I now am looking to create a database that can have at least 30 users. Each one with a unique user ID & password (Using a security file ".mdw") Users log into a front end on a network environment. Now...My question...Is 30 to large of a user base for Access in a network environment??
I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.
I tried and failed to get this to work using a multiselect listbox..I have a list of departments in tblFunctionalArea...My main table is tblStatic..I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.After looking around i couldn't find many people successfully maanging to store listbox values in a table...
I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields.I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.
I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc. But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks
I've set up a database for product tracking. It is going to be used by several users at one time. I'm going to implement it in stages as inevitably there is going to be some fine tuning to be done etc. I have a few questions with regards to editing.
1, I presume that I cant alter the database while it is being accessed by others?
2, Is it better to alter another copy of the database and the import the data being generated and then copy it to back the server in one go?
3, If so how do i do this as I’ve had a trial go and failed miserably?
4, Is there another way of doing this minimising the down time of the database?
Being relatively new to access your help would be gratefully appreciated.
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.)
I have an issue using the Simple Query Wizard in Access.
I am attempting to Group by the field 'HouseName' and to group by the Max 'Area'. However I also want to retrieve from the query the County which is associated with this.
I have included a sample table below. I wish for my output table to be as follows;
HouseName: County: Area: Park 1 A 100 Park 2 C 78 Park 3 A 70
(Where Park 1 is HouseName attribute, A is County attribute and 100 is Area attribute)
Sample Input Table:
HouseName: County: Area: Park 1 A 100 Park 1 B 60 Park 1 A 85 Park 2 C 78 Park 2 D 34 Park 3 A 70
In SQL View my query looks like this;
SELECT TableExample.[HouseName], Max(TableExample.Area) AS MaxOfArea FROM TableExample GROUP BY Table.[HouseName];
i inherited a database created i believe in access 2000 everytime i need to update a report or what not i must find a machine runnning 2000 how do i go about working on this database with access 2003
How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?
Here's the situation: I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity enforced.
I have a main form based upon tblContracts (the "one" side of the relationship) that has an embedded subform based upon tblPayments. the two forms are linked Parent/Child on the IDKey field.
Here's the problem: If a user goes to a new record in the main form, it allows them to enter information in the subform without entering information in the main form. This means that a new record (and its corresponding autonumber IDKey field value) does not get generated in tblContracts and I have an orphan record in tblPayments that is not linked to any record in tblContracts - which violates the referential integrity that is supposedly enforced between the two tables.
Any guidance on how to deal with this would be greatly appreciated.
I have an application that is used by individual teachers to generate reports for central admin. It is not secured except I have locked out access to all objects, Navigation pane is hidden, etc to protect the integrity of the tool. I must also maintain this as changes are propagated. I am trying to find a way to upgrade forms, functions, queries, etc without manually having to unlock and relock every db for each school site when changes are required. I have tried importing and exporting from a master db, turning objects on and off by recognizing my password, splitting code and data (db's are on flash drives and path changes every time they are inserted, many teachers cannot handle refreshing table links), I have tried writing code to import the changes at next startup, etc. It is written on Access 2003.
I have a table for logging experience for employees related to a given requirement.
The requirement is that any employee, to maintain proficiency ("be current") , must maintain an event count of minimum 5 within last 3 months. Or else the employee is not current and other measures must be taken.
I want to know when the currency expires for each employee (= at which date does the employee no longer have a count of minimum 5 within last 3 months).
I need to create buyer and seller invoices within my access 2003 databases, my sister in law runs an antiques auction house and I'm working on a database to capture all their information.
I've created a 2 queries (a buyer invoice and seller invoice), show all unsettled items with the relevant item information and fee's.
However, I would like it to automatically allocate an invoice number, and store the information back to a table (seller and buyer ID, total number of items on the invoice, total fee etc)
I'd like store the invoices as well, so within the customer record form, I can include a box showing a list of the buyer invoices on one side and seller invoices in another (not all customers are buyers and sellers).
Once I've issued an invoice, can I automate it to show that item as then as invoiced?
Can the invoices be editable at all?
When they come in and settle their account, I also need to feed this information back in as well.
Can i arrange an invoice for seller's particularly, to show all sold items, and the fee's associated with them
Returned items as well as any associated fee's with them?
I have been looking at the Northwind example.
I see how they have an orders details table and orders table, I could replicate this.
I could have an Buyers Invoice table and Buyers Invoice Details table, but how do I generate an invoice, pull the next Invoice number from my (currently blank) invoice table, and pull in the outstanding items for that buyer for that specific auction date (I have a query), and then populate back information from the invoice?
I like the fact that their order form is editable, do any changes go back to the query, that then populate the invoice when you click print invoice?
I need to be able to (at the end of the auction) pull all items from my item log, for that buyer, and print them an invoice, automatically saving all the invoice details back into my database.
I need be able to automatically flag the items in my items table, as invoiced and pull the invoice number in?
I can see how all the Northwind tables, queries, and forms relate to each other and subforms, but I'm not sure how to actually create an invoice, get invoice number, merge with my query, and then feedback in.
I've been using MS Access 2007 for years to manage some Excel data. running some queries etc. Just recently I've been encountering problems when importing data into an existing table. When I do it now, I get a "Subscript out of Range" error. To troubleshoot, I imported into a new table and when doing so, the fields no longer match the column order of the spreadsheet. They all get imported but appears in a different order. I think this is why I am getting the error message. How can I go about ensuring that the data gets imported properly into my already existing table? My fields in "Design View" will match the order of the Columns in the Excel spreadsheet.
I have read through just about every post on this forum related to "Unrecognized Database errors" and have not found a solution to my direct problem.
Stats: Database is running Access2K on all machines; db is on network location, no sharing issues or permission issues have been identified, no other databases in this network location have had this problem. also, we have tried compact-repair and also have tried re-creating the database and neither have proved successful.
I can open the database just fine if I am the only one opening it. If someone else tries to open it once I have it opened one of the following occurs:
1. Nothing. Hourglass appears for a split second, then nothing happens. 2. Error message "unrecognized database format "mydb.mdb"" appears. If I click OK and both users close the db, it can be opened just fine again by the first user. It doesn't matterwho opens it first, but the second user gets this message.
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time End Date/Time Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee 12/06/2014 01:00--12/06/2014 03:00------John Smith 12/06/2014 04:00--12/06/2014 06:00------Jane Doe 13/06/2014 02:00--13/06/2014 05:00------John Smith 13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
I have an ordering db that utilyzes the typical Form / Subform layout. I would like to be able to delete a record from the Main form and maintain the same postion relative to the other records. For example, if I am scrolling through the records and want to delete record #45, After I delete it I would like to be able to have record #44 visible to the user.
I am currently using (2) SQL statements to delete the records from the main Order table and the Order Details table which are linked without Cascade Delete Related Records being on.
I had set-up a recordset procedure to try and find the bookmark set before deletion but the only way I can get the sub-form to not show a blank form (no controls visible for the record just deleted and the #deleted in controls on the main form) is to requery after the delete, which loses the bookmark. If i place the ReQuery at the end it displays the records properly, (without the blank record) but returns the record to the first record. I have tried turning off any sorting references to OrderBy, etc. but it still returns to the first record.
I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.
I am just about to start a new DB, but there are a few things I need to sort before i start.
I am working with a quotation system etc which will require the use of unique IDs...
eg 000123 then 000124 etc, this bit is not a problem. The thing I would like to know how to do is when someone makes an order, I would like the quotation number to be changed slightly to add more detail. For eg, If the quotation number that is allocated is 00123 and they then order a Conservatory, I would need to have this 00123 become 00123CNS, but this can be in a seperate field, Which I would need anyway.
The question is, How Would I go about this. I have a table that describrives the product, TBL_PRODUCTS; ID, Product_Type, Product_ABBR
Product_Type = for e.g Conservatory Product_ABBR = for e.g CNS
I would need the ref number and product abbr combined to create an order number. [note] There will be a check box to say of ordered ot not, so I assume there will be an If statement somewhere?
What I've done is created a form based on the tbl_customer table and used tbl_rates as a sub form. tbl_destination is used to populate a combo box that is situated in the sub form.
Each customer has their own rate sheet. Each rate sheet has different destinations and rates. Rates for the same destination are constantly changing. I need to use this spreadsheet to record the history of every change made to the rates of a particular destination. To do this I've just added the same destination with a new rate.
Now, what I want to do is to create a query that will only show the latest entry of a particular entry.
