:confused:
Hi,
I would like how I can generate automatic Id in a table with following structure. billid number, billdate date& time.
I don't like to use AutoNumber built in feature.
Regards.
Soumen.
I want to input a number value in [field1], then i want [field2] to generate an auto number BUT i want it to increase based on [field1] THEN combine them into [field3] creating a unique SKU for every item I have.
EXAMPLE1: I will type in [field1]: "001234". [field2]will enter "000001" because it is the first occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00001"
EXAMPLE2: I will type in [field1]: "002468". [field2]will enter "000001" because it is the first occurrence of "002468" in [field1]. [Field3] will then have a value of "002468-00001"
EXAMPLE3: I will type in [field1]: "001234". [field2]will enter "000002" because it is the second occurrence of "001234" in [field1]. [Field3] will then have a value of "001234-00002"
I have an order table [OrderMain], which contains [OrderNumber] "Auto Number" along with various customer related only info
Then i have a order line table [OrderLines] which contains the items ordered. Which looks like
OrderNumber
OrderLine
PartNumber
These tables are in a form, relationship of [OrderNumber]
This structure enables me to order as many parts as i'd like against 1 order number.
What i would like to do is set something similar to a auto number in the OrderLines table. This would be in the Lines Field.
The problem is, AutoNumber can't be used in the [Lines] field as each new order line # would continue from the last #.
The result would look like:
So I have decided that I want my ID's to be AutoNumbers, but at the moment they are currently set as Numbers. I have already inserted data, to test, which has been deleted, however I am now unable to change the ID field back to AutoNumber.
How can I duplicate the tables so that this field can be changed again?
I have like 10 tables with heaps of feild, so remaking them will take long, but I know there is a way using queries, I am just not sure how...
So I have a table tblPO2006 with a field PO that contains a number.
Currently I have 100 records and the curent number in this field is 10804
What i want to do is when a new record is created the field updates with a +1 increment to the previous records number.
Thus the new record would be 10805 and the next 10806 etc
I dont want to use autonumber... can someone point me in the write direction of code to do this.
thanks
Hi guys, im new to all this so take it easy
Im trying to create a document register in Access 97 to create and track document numbers, for this i want to assign a 6 number unique id to each record starting at 000001 going up in order to 999999 which will form part of a unique docuement number ...i know this may be a really easy thing to do but can anyone tell me how?
Cheers
Dave
Hello,
I have this query:
SELECT UNIT.Code, Rnd() AS Expr1
FROM UNIT;
The problem is that in the Expr1 field all the numbers are the same. I would like to have random numbers that are different for each record (generate new random number for each record)
Thanks for your help
Cheers
Hi Guys
Dont quite know if this is the right place to post this but here goes any way
Im creating a database where customers can submit a question, it centres around a form where a user enters various information such as initials, telephone number, and various selections from from down boxes.
I have placed a button at the bottom that saves the record, what i want is when that button is pressed for a box to pop up giving the user a unique reference number which is made up of the information they have entered, such as
FIRSTINITIAL-LASTINITIAL-DATE-INCREMENTALNUMBER
I want this number then to be stored along with the record so it can be searched for by this number.
I have done searches for similar questions but cant find one that matches what i want to do
Thanks in advance
I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0
Code:
Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub
invoice numbers are 04024, 04025 etc...how I keep the formatiing?
I am trying to automate the generation of a reference number incrementally by 1.
In my main table (tblBooking) I have my primary key field autBookingID which is an autonumber.
In another table (tblBookingStops) I have the primary key as autBookingStopsID as the autonumber and then numBookingID linked to the above table (tblBooking). I have another field called txtGPSID so this is the number I would like to automate.
So for example:
tblBooking:
autBookingID: 1234
tblBookingStops:
[Code]....
I want to put the code that would populate the txtGPSID on a form in datasheet view and would like to put the code on the load event.
Auto number will not work for what I want to do. I am creating a simple database that will assign the user with the next incremental number in a field. The intent of the database is for the user to enter three text fields and then obtain the number. I believe that Dmax would work but do not know how to make populate the table.
View 10 Replies View RelatedIs it possible or not but Iany way to set up a number field to autonumber on reviews of an application.
To elaborate I have a review table linked to the primary application table. The pk on the review table will be an actual autonumber to list all the reviews ever to happen in the database. There will be multiple reviews on one application and there will be a review number for each review (1, 2, 3, etc...). Is it possible to set up as a 'number' field that will add the next review # for that particular application, and then start again at 1 for the next application?
I have a table with an autonumber field, which of course is indexed with no duplicates. Twice in recent days it has attempted to add a record with an autonumber that is not the high number - it is about 20 numbers below the high number. So we get a 'can't add this record' error.
I can fix this by copying the table to a temporary table and then copying it it back. Then the autonumber works correctly.
What can I do to prevent this? By the way we updated to Access 2010 a couple of weeks ago, but the data is still Access 2003. We are reluctant to update the data yet in case it causes more problems.
I need a field to permit only numbers. Upon selecting number for the data type I select Long Integer for a longer number... But it only allows 9 digits.. I need it to allow to enter in longer numbers... Any help?
Thank you very much in avdance
I have created 2 tables:
StaffID
StaffTrainingID
Table 1 (StaffID) - contains all personal details in the form of forename + surname + position + email + contact numbers ect. Each field/person has an autonumber.
Table 2 (StaffTrainingID) - contains all mandatory training/lectures (18 fields in total). I don't wish to list each staff member again in table 2 so have put a StaffID field in (data type is number).
However, when I try to type in the StaffID number in that field so I can link that person to the relevant training he/she has taken, Access won't allow it.
I have an existing form where users type in information and it generates a couple of reports. In one of the fields, Customer PO Number, the user enters a number from a customer. Up until yesterday all of the customers we have been dealing with have used numbers only for their PO numbers. However, we have a new customer that requires alphanumeric PO's. Is there a simple way to change this field from a number to alphanumeric without having to redo each form, report and/or query. I am using MS Access 2010.
View 9 Replies View RelatedThe DolphinBatchNo has number data type but the following sql statement doesn't capture the ight records. it doesn't check id dolphinBatchNo is blank.
Code:
strsql="select * from `MasterTBL` where PolicyNumber>=" & TxtFPolNo & " and PolicyNumber<=" & TxtLPolNo & " and PolicyStatus='Live' and DolphinBatchNo is null order by PolicyNumber"
I just started my project database on my subject ITM4. My Database is entitled PCExpress Inventory System. I would like to know how to input (Text) in a Field where the Data Type is (Number) just like the North Wind.
View 3 Replies View RelatedI'm having trouble with a field on my DB. I have a field named zip code. It has a limit of 5 characters that are only number values, I'm not having any trouble with entering information at all, it's easiest when I just tab into the cell from the previous one. However, the part I am having trouble with is when I click within the field and the cursor, let's say, is at the second character line, and I enter the zipcode 15222, it'll only enter 1522 since my cursor is at the second line.
How can I fix this so that wherever my cursor is, within the box it'll type the full 5 digit zip code? My boss is insistent on making sure that even if he clicks towards the right of the box, it should type the full number.
Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.
I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.
Jim
Hi guys,
Just a quick one ( I think )
I have a table which already has an autonumber ( the id field )
But I need to create another field also with an auto generated number which will generate reference number which i need to start at a pre-defined number.
The problem is, im not allowed to use 2 auto number fields, and access is prompting me to use a number field instead.
The question is, is there a way around this ?
Can i create another field which works the same way as an auto number and increments the number by 1 for each new record entered ?
Many thanks in advance for any help.
Max
[edit: spelling mistakes]
The big issue is wanting to create the following records in a subform every time a new record in the subform is added. I can populate the Ticket field and know how to cast fields as strings to append them (so I can get the Cylinder field). The output is used to populate a table called Cylinders. My question is:
How do I create a field of letters (or numbers) corresponding to the row in the subform without continuing from the previous letter (or number) in the Cylinders table?
Ticket
Letter
Cylinder
1589
A
1589A
1589
B
1589B
1600
C
1600C
1600
D
1600D
1600
E
1600E
I have a primary key field called "Issue Number", with the properties set to auto-number.
After testing, I need to set it back to zero before going live.
I deleted all the records and did a compact and repair, but the first number that comes up is the one after the last one used during testing.
Help????
Thanks
Hi, basically what i want to do is append a record to a table using vba. I would like to take two values from my form (productID, supplierID) and insert them into a table (supplierProductsTBL) under the same headings. I've constructed an sql statement but am getting the following error:
runtime error '3346'
number of query values and destination fields are not the same'
I think this happens because the table also contains an autonumber field (supplierProductID) and i'm not referencing this in my sql statement (below):
mySQL = "INSERT INTO SupplierProductsTBL ( SupplierID, ProductID ) VALUES ('" & SupplierID & "'), ('" & ProductID & "')"
i wuld just get rid of the autonumber field but i need this.
Any help on this would be appreciated, cheers mark.
I don't know why is it so complicated to add an auto number field in a query. I would like to add a increment number (auto number) on each line and then an auto number on each product.
View 7 Replies View RelatedI have a table which will be completely emptied and refilled. The table has a field autonumbered it is also the primary key. When the table is refilled I want this field to start from 1. How can I do that. Yes some people will say this subject has been discussed search for it. But here the issue is somewhat different we have an existing field and I do not want to compact the database each time the procedure runs.
View 1 Replies View Related