Form+Table, 2nd Autonumber Help

Apr 10, 2008

I have a table that stores orders in. There is a form to input data into this table, that I choose the products from a products table (using a subform) for the order. Once I submit the form, it submits several rows according to how many products I chose (a row for each product and the order) to the orders table.

The orders table has a autonumber primary key ID, so each of these rows has a different ID number.

I also want a column to store an order number (field: OrderNo) that is the same for each row (each product) for this order in question and not be different, allowing me to do a query for that order number and see all the products under it.

So I need the form to lookup the last highest order number from the OrderNo field, add +1 then when I add a product, it gives the SAME number to each one.

I hope this makes sense and I hope you can explain to how I do this? :)

Thanks,
Nick.

View Replies


ADVERTISEMENT

AutoNumber Not Matching Between Table And Form

Aug 5, 2005

I have a form, its based on a query. That query contains an Autonumber(intID) from a Table(customers). I also have another table(dealers), that uses the ID number as well. Next to the dealer info, I can click the "+" and it'll show all customers pertaining to that dealer.

I've seen some that have had different ID numbers. In the form, one has the ID of 222...while in the Dealer Table it has the ID of 227.

Does anyone know what the problem is?

Also...I have a subform where you can add a dealer to this Dealer Table if it does not exist in the combo box. Here's the code:

If MsgBox("Add New Dealer Number?", vbYesNo, "Warning") = vbYes Then
DoCmd.OpenForm "DealerContact", acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If

Thanks for the help.

View 2 Replies View Related

Controlling Autonumber Field - Start Autonumber From 1 Everyday (Composite Key)

Nov 21, 2013

I have two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,

Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key

Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key

The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.

View 4 Replies View Related

Add New Autonumber Column To Table

Oct 19, 2006

How do I add a new column to an existing table, and fill it with number (starting at 1) so I can use that column as the PK? Step-by-step instructions would be great, I'm having a lot of trouble with this!

View 4 Replies View Related

Viewing Next Autonumber In Table

Aug 13, 2012

Is there a way to see what the next autonumber is in my table?

View 8 Replies View Related

Copy Autonumber To Another Field In Same Table

Jul 31, 2006

I need to make changes to a table already containing data. Currently the autonumber field is simply there as a primary key, but I now wish to use it for another number field (Accession No) which was previously completed manually.

As this doesn't seem possible or practical (as I need the flexibility to overwrite the number sometimes), I just want the autonumber to also appear in the Accesion No field when a new record is added (but be manually overwriteable).

I've had a play with default values and lookups, but with no success, and haven't found anything on the forum. Where am I going wrong, or is this just not possible?

Thanks for your help.
Alison.

View 1 Replies View Related

Table With Autonumber And Need Date Code

Mar 15, 2007

I have a table named tblProjectNumber that has a autonumber field. In design view under the format property I have "NC07-"00000, this returns the a new project number with the text in front of it. What I would really want is for the text the change dynamically with the year. So an example would be NC07-00000 for project numbers created this year but I will manually have to change it for next year and the all of the previous project numbers will change accordingly. I have tried "DateSerial(Year(Date())-"00000 , Date("yy") and I can't get it tto work.Any help would be great.

Tim

View 1 Replies View Related

Junction Table -number Or Autonumber?

Dec 21, 2007

Hi :)
I have solved a m:n relationship by adding a junction table. The composite primary key in the junction table is made up of primary keys from the other two tables, and both of theses primary keys are set to autonumber.

My question is this: Is it correct to set both parts f the composite key in he junction table to number rather than autonumber?
I was thinking that since autonumber should be set only once, the junction table should just take on what ever value there is in the primary keys of the other two tables.

Kind Regards
Marie

View 3 Replies View Related

Join AutoNumber With Other Table Field

May 17, 2006

Hi, I just a fresh user for the Microsoft Access. I have some question on the forms. I created a form and there is a field name Membership Id. In this field I created using AutoNumber format. I was requested this membership number needed to add also some Area Code and BranchCode infront of it. How could I add this into this membershipID so I could have this few info combine and shown in my form(textlabel) ? :p :p

View 1 Replies View Related

How To Create A Table With An Autonumber Field

Dec 4, 2004

Hi,
I'm new to this forum and have a question:
I want to create a table with an Autonumber field using a SQL statement,
in Microsoft Access Database, something like this:
Cnn.execute "CREATE TABLE newtable (id Long, Name Char(100)) "
is there something to put in place of "long" to make the field autonumber?
I tried the word "autonumber", but did not work.


Many Thanx.
Hadi.

View 3 Replies View Related

Create Calculated Autonumber In A Table

Mar 17, 2008

I have a table where one field needs to be an autonumber, however, that autonumber needs to be calculated. This field is not based on any other information in the database, but there's a very complicated mathematical process behind it, which I'm figuring out...I just need to know once I get that code complete, how do I tie it into the table?

View 7 Replies View Related

Table With Primary Key - Autonumber In Increments

Jun 25, 2014

I have a table that has a primary key that does an autonumber in increments. Then I made some queries that will append records by date and then also another query that will delete what was appended. Then I created a reverse sequence of this process in case I change my mind and would like to put back the records I archived, however, when I do this, the primary key is now thrown off and will still revert to the last primary key that was appended before the archive ever took place. In other words it still thinks that the records I appended before I did the reverse, are still there. So now it will not let me save the record because it is a duplicate.

Is there a way to set the autonumber for the primary key to where I want it to begin?

View 14 Replies View Related

Append A Field Of Autonumber Into New Table

Nov 15, 2011

I have the field called subject_id which is autonumber in the form of 10001,10002,... And I want the field with the same format to appear in another table. But appending it leads to different numbering, i.e. the filed becomes 1, 2, ... instead of 10001, 10002 as I've formatted it in its original table. How could I append/copy such a field?

View 1 Replies View Related

Import Text File To A Table With Autonumber

Jul 19, 2005

Hi, I'm trying to import a text file (just for a test at the moment) with the fields seperated by |'s.

The table I'm trying to import to has the fields -
ID, First Name, Last Name, Gender.

The ID is an autonumber though so what can I put there in the text file because I'm getting an error? :confused:

View 3 Replies View Related

How To Create A Access Table Having Autonumber Type With Sql?

May 20, 2005

hi,
field type of autonumber can not be created with sql,why?is autonumber a data type like int,text ect.?but int or text field can be created

View 3 Replies View Related

Access 97 + Linked Table Oracle + Autonumber

Mar 24, 2006

Hello,

I'm making a MS Access frontend for some tables on the Oracle 8 database at work. The tables are linked ofcourse.

One table has an AUTONUMBER field on the Oracle and it seems to give me trouble to insert new records.
When I try to insert a new record (leave the autonumber field blank) I get the following error: "ODBC — insert on a linked table <table> failed. (Error 3155)" followed by the error "[Microsoft][ODBC Driver for Oracle][Oracle]ORA-01722: invalid number (#1722)".

When I look at the Oracle documentation I got this:
ORA-01722 invalid number
Cause: The attempted conversion of a character string to a number failed
because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that
they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
I checked the INSERT statement: "INSERT INTO AFM_HV_PROP_VALUE (HV_INST_ID, HV_PROPERTY_ID, TABLE_NAME, HV_PROPERTY_VALUE) VALUES (4, 'V_TESTJE', 'hv_inst', '123465')" and everything seems to be allright. The value that's causing the error is the "4" that gets in the "HV_INST_ID"-field.
Using TOAD to execute the SQL Statement, there is no problem at all.

When I look at the table design in MS Access I see that the Autonumber field is of the type "Double". That doesn't seem right to me...

Anyone some suggestions? I'm running out of courage :s

Greetings,
Niels R.

View 1 Replies View Related

Make Table: Create AutoNumber Field

Feb 19, 2007

Hi All,

I am trying to create a make-table query, with a new AutoNumber field.

I know that if you are creating a new Text field you type FieldName: "" in Field and for a Number field you would type FieldName: [], but what do you type for an AutoNumber field?

View 2 Replies View Related

Tables :: Autonumber Order In Existing Table

Jul 9, 2015

I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.

View 9 Replies View Related

Tables :: Set Autonumber To Start From Last Record On The Table

Jun 10, 2015

I had a backup from a table that I saved to excel. Somehow we lost all the records from the table 10 minutes later. There where gaps in the id numbers due to some delete records in the past, and when I did try to put it back in the access table the records some how shift. is there a way of still using the ID nr that is an autonumber in the table and when I set the data back from excel and use the append when paste that they show up correctly? Maybe I can set the autonumber to start from the last record on the table?

View 14 Replies View Related

Adding AutoNumber As Primary ID To Existing Table

Jan 31, 2012

I have a simple database that I inherited, that contains basic customer contact info. We want to start adding more functionality to the database, so I've done alot of research to learn about how to begin normalizing my data.The existing table does not contain a useful primary key, since we may have multiple individuals from the same company, companies with very similar names, etc.

I would like to add an AutoNumbered field to use as a CustomerID/primary key, but I can't seem to find a way to do so. Here is what I've tried so far:

I have tried to add a field to the table, and make it an AutoNumber data type. When I do so, I can error message saying that I cannot make a field AutoNumber if any other field in the table already has data entered in it.I have tried to create a new table containing only an AutoNumber PrimaryID field, and then import data from the existing table, but that just creates a 3rd table.

View 1 Replies View Related

Create New Record & Pass Autonumber To Separate Table

Mar 10, 2008

I have a form, frmSub, that contains the combo box comProducts. I also have two tables, Products and PurchaseDetail. Both tables have the field ProductID.

I want comProducts to create a new record in the Products table, using the input in a field called Product and then to use the value of ProductID to create a new record in the PurchaseDetail table. Ie, so the PurchaseDetail table has a record that links to another record in the Products table via the feild ProductID.

I hope I was semi-clear.

View 4 Replies View Related

Modules & VBA :: Autonumber Based On A Table Content With A Prefix

Oct 15, 2014

I have a module which generates an autonumber based on a table content (Tbl:Numbervalues) and a prefix (prefix held in Tbl: TextValues)

the module itself works fine and after every getprefix & getnextnumber it updates the number within numbervalues table to the next unique figure.

However what i want it to do is within my database i want to loop through all the records and update a column based on the getprefix() & getnextnumber()

so at the moment lets say column A has values of SHA000001 (in all 100 records) i want it to loop through those 100 records and update to be SHA(from the prefix table) and 000001 all the way to 000100.

Code:
Public Function GetPrefix() As String
On Error GoTo GetPrefix_ErrorHandler
GetPrefix = DLookup("Value", "TextValues", "Description = ""InvoicePrefix""")
If Len(GetPrefix) <> 2 Then
msgbox "The Invoice Prefix Value in the Text Values Table is not 2 characters long!", vbCritical, "Critical Warning"
End If

[Code] ....

View 5 Replies View Related

Modules & VBA :: Reset Autonumber Each Time Delete / Append Records In A Table?

Oct 25, 2013

I need to reset the autonumber each time I delete/append records in a table. Best way?

View 5 Replies View Related

Getting The Autonumber Into The Form After Inserting New Record

May 14, 2005

I really hope I can explain this right :o

When a customer makes a purchase I INSERT the transaction into the 'transactions' table, which I have no problems with. My problem stems from that I can't seem to get the 'OrderNumber' back from the 'transaction' table (OrderNumber is an AutoNumber by the way).

I have tried the following code -
TempOrder = DLookup("OrderNumber", "transactions", "customerID = '" & Forms![frmOrder]!TempID & "'")
"Forms![frmOrder]!TempID" is the value I use from another form (to see who the customer is). I want "TempOrder" to be the OrderNumber that is created in the transactions table.

Regards, James

View 6 Replies View Related

Assign Autonumber & Form Copy

Feb 8, 2006

I don't know if this is possible or not. I know you can assign your own autonumber by working w/ an append query. But my question is, if I have a number that is 1.0 - can I change the 10th spot after the decimal? I tried it myself w/ the append query but it changes the 1 to a 2.0. Is there a way to do that?

My next question is - I know there is a copyobject. I want to be able to click on a button, & it copies a form. BUT, you have to enter either a destination for the form or a new name for the form. But the problem is, I want to keep the current destination and the form name will change each time there is a new form created. Is there a way to have something pop up for me to rename the form?

Thanks in advanced.

View 3 Replies View Related

General :: Listbox - Use Autonumber To Filter The Form?

Jul 29, 2013

So i have a form with 3 fields. (This is just a test form/table):

ID: autonumber
Fullname: text field
Last4: number field

I have the following code:

Code:
DoCmd.OpenForm "test", , , "Id = '" & Me.List14 & "'"

It keeps giving a mismatch error. How do i get it so I can use the autonumber to filter the form?

Code:
DoCmd.OpenForm "test", , , "Fullname = '" & Me.List14 & "'"

When I do this it works just fine.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved