Modules & VBA :: Database For Quotations - Modifying Sequential Numbers

May 29, 2015

I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.

This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?


View Replies


Assigning Sequential Numbers.

Feb 12, 2007

Yo there - thought Id fire a question out regardin numbering:

I have a table players and a table team.

Players sign up to teams, and I have a report which shows each team, and the players underneath.

Player 1
Player 2

Now, after all the players are assigned to a team, I want to assign each player within each team a random number, sequential.

So when I run the report, it will be like:

Team A
1. Player 1
2. Player 2

Team B
1. Player 1
2. Player 2

Any pointers!?

Cheeas -

View 9 Replies View Related

Unique Sequential Numbers

Nov 22, 2004


I have a field called "Project #" that needs to generate a unique number each time a command button is clicked. For example, when the button is clicked the blank "Project #" field is filled in with "653001". After the "Project #" field is filled in the button also runs a code to create a report exported into Excel. This report will only have the records with the Project Number of "653001".
Then, when more records are added and it's the end of the next week, the button is clicked again and the "new" blank "Project #" fields will be filled with the next Project number which would be "643002". A report will again be created but now with only the records with the Project number of "643002".
The first three characters will always be the same for the Project Number "653" but the last three will increase whenever the button is clicked to update any new records that have a blank "Project #". So, it should look like: 653001, 653002, 653003, 653004, etc.

I know it sounds confusing but I hope someone can help.


View 1 Replies View Related

Reports :: Sequential Numbers For Records

Mar 10, 2015

Need a sequential number on report. Have an unbound text box and set it to list a running sum over group. The twist is that I'd like to have it start with an assigned starting number [Starting Number] and then add 1 to each subsequent record. It works for the first record but then the next record is last value + [Starting Number]. Basically it is a check run where I would list the starting number and the report would do the rest.

View 2 Replies View Related

Queries :: Missing Sequential Numbers

Feb 25, 2014

I have a list of what should be sequentaia numbers, but I am finding instances were a number seems to be missing..Is there a way to query the whole dataset and find all missing instances without having to trawl he list?

View 4 Replies View Related

Auto Enter Sequential Numbers?

Apr 2, 2012

Ineed to track Trailer security seals that are sequentially numbered with 8 digit numbers...They are physical seals and no efile with the numbers to upload into there a way I can put in a range and Access will populate the rest in a table?

View 5 Replies View Related

How To Isolate Records Based On Sequential Numbers

Dec 27, 2011

I am trying to create a database that will have records that include dates and a number assigned to that date. ex., the 3rd day of the quarter will be numbered 3 and the 43rd day numbered 43. I will do this via table.

After running a query that will identify a subset of records, I only want to see those records that are part of a 10-day or greater span. There could be multiple 10-day+ spans over a given quarter.

View 2 Replies View Related

Entering Multiple Identical Orders With Sequential Numbers

Jul 12, 2006

Hello all.

I need to be able to enter a number of orders that would all be the same, but they will have different order numbers, in sequence.

I'd like to enter all the information in a form, then have "First Order Number" and "How Many?" fields, and have the system grab the detail, apply it to the order number entered, create a record in the table, then reapply the detail to the next sequential number, repeating the process until it has created the number of orders shown in the "How Many?" field.

I am at a total loss here, and any help would be GREATLY APPRECIATED!!



View 3 Replies View Related

Queries :: DCount To Add Sequential Numbers By Group Only Works In Some Instances

Jun 24, 2014

I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.

This query works, on anything that starts with the letter D or later... A-C will not work.

Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.

This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.

SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;

The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.

Running Access 2013.

View 2 Replies View Related

Queries :: Unable To Find Missing Sequential Numbers In A Text Field

May 5, 2013

I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.

The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.

My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".

View 9 Replies View Related

Queries :: Adding Sequential Numbers To Generate A Unique Reference Number

Oct 5, 2013

Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.

two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship

I also have a query (Named: FullLog) that picks up the following data from the tables:

Name - Description - Size - TrackingInfo' - Staff - DateReceived - Count

The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:

Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]

This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.

Effectively what I want to be able to get is something of that resembles the following

DateReceived - ReferenceNo

051013 051013001
051013 051013002
051013 051013003
061013 061013001
061013 061013002
071013 071013001
071013 071013002
071013 071013003
071013 071013004
071013 071013005
081013 081013001
081013 081013002
091013 091013001
101013 101013001

View 6 Replies View Related

Queries :: Quotations Database - Queries Showing Too Many Records

Jun 25, 2015

I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.

View 1 Replies View Related

Modifying Database From Excel File

Nov 4, 2004

I have a a database with a large number of email addresses in it.
I also have a list of email addresses stored in an excel file.

I would like to take the excel file and use it to check for the same email addresses in the access database and if they are present in the database add content to another field in the database to flag it.

Can anyone help?


View 4 Replies View Related

General :: Creating A Database For Creating Quotations

May 20, 2015

I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?

View 3 Replies View Related

General :: Prevent Users From Modifying Database

Aug 6, 2013

Is there away in which I can protect an access 2007 database modification with a password.I'm doing this because I would like to prevent my client from modifying or having acces to the database's back end system (db), I would just like him to use forms and nothing else.

View 3 Replies View Related

Access 2010 - Modifying Forms In Split Database

May 11, 2012

I have a access 2010 split database and i need to modify one form on the front end (it is an accde file). However, when i open the Front end, i cannot find the possibility to go into the form design mode.

How do i need to do it? do i need to restart from the unsplit database?

The both the front end and the back end are password protected.

View 1 Replies View Related

Tables :: Modifying MS Access Database - Direct Order Or Placement Of Fields Within Table

Feb 5, 2013

Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?

I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.

For example - if TableOne has 3 fields - Field10, Field20 and Field30.

I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.

I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.

View 13 Replies View Related

Modules & VBA :: Automate Line Numbers And PO Numbers

Aug 24, 2014

I have 2 fields that I would like to automate if possible

One field is called "p/o number" and another field called "line no"

These fields are part of an ordering database

Let say I have 200 items to purchase form 10 suppliers

And form example 20 items from each supplier

What I do at present is put the order number on each line item and the line number


p/o number line no

1 1
1 2
1 3

2 1
2 2
2 3
2 4

What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.


1 1
2 2
3 3

2 1
2 2
2 3

View 5 Replies View Related

Modules & VBA :: Update Column With Sequential Numbering

Jan 8, 2014

I have column called "order" in table called "mov" and this column has this layout


And I want to update this column to be corrected numbering from 1 to the last cell number - lets say it 17540 - this update has no criteria conditions, just this field.

View 4 Replies View Related

Quotations In DLookup Statement

Oct 14, 2012

Table1 has three fields. I'd like to use the following DLookup statement:


Note that in the criteria, I want Field2 = the first 4 characters of Field 3 + the string value 55.

I am sure I've got the quotation marks wrong.

View 1 Replies View Related

Modules & VBA :: Sequential Numbering Dcount Dmax Composite Primary Key

Sep 23, 2014

I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.

ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field

[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:

1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.

However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:

1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.

If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1

View 3 Replies View Related

Modules & VBA :: Adding Additional Sequential Number To Avoid Duplicate Value

Jun 10, 2013

I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days.

Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.

For example, we take "1791" as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table "tblRDEF" (record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to "1791-1" since "1791" already exists in the database. When we get the next file, the number should change to "1791-2" and then "1791-3" and on.

View 13 Replies View Related

Tables :: Calculate Some Additional Data And Generate Quotations - Too Many Fields

Jun 2, 2015

I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.

Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG

View 14 Replies View Related

Filling Database With 2000 Numbers

Feb 9, 2007

I have one question
I have simple table in Access 2003 with three fields
One is (field name), type - autonumber
one is (field name), type - number
one is (field name), type - text

my problem is that i need in number field nubers between1 and 2000 and in text fields same numbers between 1 and 2000 with letter G at the end.

that will look like
Autonumber 1 ... 574 ... 2000
Number 1 ... 574 ... 2000
Text 1G ... 574G ... 2000G

Problem is that I dont know how to write a code for that i think that there would be a macro.
So can someone help me please!

View 7 Replies View Related

Automating ID Numbers Before Handing Over Database

May 19, 2012

I've got a database, that I'm trying to update for my sister in law.I've got 2 tables, Items and Customers.As I've been working with the id's they have used so far, these aren't currently automated.

for customers, they use their number in the auctions, to place bids etc, so I can't change them.But once I hand over the database, I want to automate the generate of Customer ID numbers from that point on, but not override the history.With the items, what I have done, as an item can be relisted up to 3 times, I created a LogID auto number which is also my key number.

Before I hand it over, I need to do a big update there with them, to pull in the latest customer and item information.But after that, I'd like the new items added, to be given a new automated number, but the logid still needs to be generated automatically as well, as the login allows no duplicates, whereas the itemID does allow duplicates.

Its the item ID that is used to generate an invoice and show how many times its been listed to pick up correct number of fee charges.Customers can be both buyers and sellers, and I had to allocate 2 different fields, and the buyerID just replicates the sellerId number. So the Seller number is the main number on the table, I didn't want 2 tables of buyers and sellers separately, its one table.

View 1 Replies View Related

How To Import Phone Numbers In Access Database

Oct 31, 2012

I imported an Office 2010 excel file into Access 2010. All the headers match.

All the data transfered, but the phone numbers.

In Design View, the phone numbers are listed as numbers, not text.

Then I tried to copy the two columns from Excel and past into the two columns in the database. No go. It did create a new table called Paste Errors.

How do I move that into the Table that I first created?

I need to know how to get the phone numbers in their because I need to import several other excel files into this table.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved