Append to Table with Auto Number

Hi All,

I need to paste or use an append query to append a number of rows to a table 
that contains an autonumber field. Attempts so far have failed because the 
autonumber will not accept a null value.

Any ideas please.

Ta.
Bill. 

0
Bill
12/18/2007 12:43:52 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
996 Views

Similar Articles

[PageSpeed] 9

Sorry, I don't think it is the autonumber field that is the issue after all. 
its another field that requires a value. Any idea how I populate that with a 
default value?

Cheers.
Bill.


"Bill" <bill@bill.com> wrote in message 
news:%23cMKsOXQIHA.1184@TK2MSFTNGP04.phx.gbl...
> Hi All,
>
> I need to paste or use an append query to append a number of rows to a 
> table that contains an autonumber field. Attempts so far have failed 
> because the autonumber will not accept a null value.
>
> Any ideas please.
>
> Ta.
> Bill. 

0
Bill
12/18/2007 12:58:21 PM
Bill

In your query, once you've identified which field contains the null(s), use
an IIF() statement to either "keep" the value in the field (if not null) or
use a default (you decide) if the underlying field does contain a null.

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Bill" <bill@bill.com> wrote in message
news:%23LaG0WXQIHA.5400@TK2MSFTNGP04.phx.gbl...
> Sorry, I don't think it is the autonumber field that is the issue after
all.
> its another field that requires a value. Any idea how I populate that with
a
> default value?
>
> Cheers.
> Bill.
>
>
> "Bill" <bill@bill.com> wrote in message
> news:%23cMKsOXQIHA.1184@TK2MSFTNGP04.phx.gbl...
> > Hi All,
> >
> > I need to paste or use an append query to append a number of rows to a
> > table that contains an autonumber field. Attempts so far have failed
> > because the autonumber will not accept a null value.
> >
> > Any ideas please.
> >
> > Ta.
> > Bill.
>

0
Jeff
12/18/2007 1:21:58 PM
Reply:

Similar Artilces:

pivot table #36
Dear Debra, Could you please help me to solve this problem? I need to make a 4 quarts rolling chart. The chart data source is from a Pivot table. Pivot table data source is updated via a link with external data source. When External Data Source is updated, a new quarter Q2-07 comes in ,Q2-06 will be out, so there will be always 4 quarts data in the external data source. Pivot Table Data Source will be updated via a link with External data. My problem is every time new colum of quartly data comes in, pivot table is not showing the new data. I have to manuelly go into pivot table lay...

Display lists in an arbitrary number of columns
I have written and posted the following articles which will prove useful = to some: Display Lists in Columns Horizontally Using One Cell per Column http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D34 Display Lists in Columns Horizontally Using Individual Cells http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D33 Display Lists in Columns Vertically Using One Cells per Column http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D32 Display Lists in Columns Vertically Using Individual Cells http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D31 ...

editing table frames
I downloaded something that was in table frame format. Now each section of the page is in table frames. I want to convert them to text or merge them into one continuous page in text format. This is driving me nuts because I can't find a way to do it. If I highlight the text in a frame and cut it to the clipboard, the whole page disappears and I'm left with this annoying blank grey page. At this point, I'm thinking that I will have to print the page, create a new textbox at the end of my story then type the page into it, but if I have to do that, I'm not going to be happy using ...

Excel 2003 random number generator
Hi, I'm using Excel 2003 to do some actuarial work. I notice that if a very large number of random number is generated(say 10000), and the spreadsheet is run by Macro for a large number of times(say 1000), sometimes, Excel 2003 will generate some negative random numbers, which is nonsense. Some of my friends told me earlier versions of Excel didn't have that problem. I'm sort of wondering what is going on with Excel 2003 random number generator and if Microsoft is going to develop a update to fix this problem. ---------------- This post is a suggestion for Microsoft, and Mic...

Find Missing Number?
Ok probably a easy answer here, but I'm a excel Novice. what I have is a series of numbers and what I want to find is what Number are missing in between all the different numbers to complete a whole set of number. Example I have: 1 2 6 7 9 13 How could I find whats missing as to make it straight count up from 1 to 13 And return from this example 3 4 5 8 10 11 12 Thanks for any help Hi there, one possibility is in an additional column or sheet, i would create the whole series (type A1: 1, A2: = A1+1, then copy downwards until you have the required series) then, next to this, so...

Query Delete and append
I am trying to add information to a table with an append query and that works fine since I duplicated the table. The information comes from a linked XLS sheet, I need to delete the information and replace it every day. When I created a delete query it works fine but when I try to add the information again using the append query it does not work unless I open the query in design view, save it and run it. I have dozens of other queries doing the same and they all work fine. What am I doing wrong? I looks like I am getting a 3349 error but why does it work once and then when I delete the ...

Appending worksheets
Hello, I have a huge workbook with some 200 worksheets (Excel 2002) and wouldlike to create one single worksheet by appending the data on all worksheets one after another. Is there an easy way to do this without programming a macro? Thanks so much. Provided the total data rows do not exceed 65535 (assuming a standard top row for col labels), one way would be sequential manual copy > paste into a single new sheet placed to the left of the 200 source sheets. At an est 15 sec per manual op, 200 sheets would only take roughly an hour of work to accomplish. Thereafter, to clean up, jus...

locked table error
I am trying to open a report via a control button on a form. The form is based off of tblCustomers and the report is based off of a query which includes tblCustomers (I think the problem is due to that). The error I'm getting is "Run-time error '3211': The database engine could not lock 'tblCustomers' because it is already in use by another person or process." The db is local so I'm the only one using it right now. Any suggestions on how I can fix this problem? -- TIA have you tried closing that form before the report is open; as a sanity check? eve...

Number of copies when printing
Can anybody help...? We are currently printing off a form that has been duplicated many times to obtain answers to many questions in a way of collecting clients requirements. The problem is that whenever we print the form with the answers, the default setting is 10 copies. This is not the default setting on the printer and all other documents from word, outlook etc, print just one copy. Is there a setting somewhere within the excel file that is forcing the 10 copies...? We did not generate the form originally and the company who did are unable to answer our question. Any help would be app...

Append and Append To priviliges
Anybody know what these two priviliges do for a record?? Jay Append allows a user to be able to add items to an object for example you can append an address to an account. Append to allows a user to append this object to something else. You would need append to priv on the address object. They work together. "Jay Mehta" <jay.mehta@conexio.com> wrote in message news:b6caea5f.0310311043.682d756c@posting.google.com... > Anybody know what these two priviliges do for a record?? > > Jay So, in the example you gave of appending an Address object to an Account object,...

append query with dtae and username
I am using an append query to add a new line to my table - one of the field in this table is "Update_Date" and one is "Updated_By". How via this query I can populated those two fields with the current date&Time and current username of the user . Many thanks, Dan Please ignore- wrong Group, sorry "Dan" wrote: > I am using an append query to add a new line to my table - one of the field > in this table is "Update_Date" and one is "Updated_By". > How via this query I can populated those two fields with the current ...

Find value in Multiple Table witn 2 condition
Hi Expert Did anyone know how to search a value in the multiple table ex Table Mexico USA Canada Apple $1 Manggo $1 Grape $2 Hongkong China Thailan Banana $33 $3 Orange $23 $25 Question, If I given a condition that I want a price for Banana Hongkong, how the formula it is I try to use combination formula of index and match dpes only work in one table, but not in multiple table Anyone with...

Form and tables not connecting
I have a database project that I was trying to make more efficient (I've been doing the dangerous thing of reading about Database Normalization). I have one form that was based on the single table I started with. I've now broken the table into two separate tables. All of the fields from the original table still work fine. I tried to re-link the fields in the new table to the corresponding items on the form. I did this by changing the Control Source of the Text Box to point to the new field in the new table, but when I try to use the form I just get an error in the Text Box ...

Pivot table & chart size
I have a spreadsheet that starts at 327 KB. When I add just two pivot tables (and their associated charts) onto a new worksheet, the file size increases to a whopping 12.2MB!!!! Is this normal? ...

Pivot table calulation
I am a relatively new user of pivot tables and have run across a problem I need some help with. I have created a pivot table with the row labels as follows: SpecialistName Physician HospitalName The Column is: YearQTR which has two quarters in it. So the pivot table makes two columns � 2003Q1 and 2003Q2 I am then counting a field called TotalDischarges so I will get the total discharges for each physician and hospital they are using. I created a calculated item calling it differences 2003Q1 � 2003Q2 to obtain the increase or decrease from 1st and 2nd quarter. Here comes my problem � t...

Excel if then formulas auto copying
I am using IF/Then formulas in a worksheet; however, they seem to auto filling in some of the cells below as I enter data. I have cleared all the cells that do not contain the formula but it continues to happen. How can I correct this? Tools>Options, Edit tab, uncheck Extend data range formats and formulas -- Kind regards, Niek Otten Microsoft MVP - Excel "bf" <bf@discussions.microsoft.com> wrote in message news:300608C9-E57A-4AE0-8AFF-82AA8BC344F9@microsoft.com... >I am using IF/Then formulas in a worksheet; however, they seem to auto > filling in some of th...

Number Format 02-19-08
Hi every one, I am using M.S Access Xp, I am trying to multiply a number (Currency Format) with another number for the currency rate (Number format �Single). When the currency is a large number, the result will not be accurate?? Example: 100000 * 0.72 = 72000.0028 100000 * 0.70 = 69999.9988 It does not give a fixed number as expected ??? I have to use many decimal places for the currency rate to get accurate results. Please Advise� Thanks� Dear Jeff Boyce Thanks, infact I need many decimal places for the currency rate (more than 4) so I didn't use currency, I use...

Table Help
Hello Everyone, I know I have seen this problem before and that I have corrected it in the past, but for some reason I can't figure it out this time or find something similar online. The problem is simply, when I view a certain table one of its columns is completely filled in with "##########". The data is still there because when I click on it the data appears, and the data is visible in a justified form. It can't stay like this but I can't figure how I did it in the first place or how to change it back. Any help would be greatly appreciated. Thank You This ...

conditional formatting in pivot table
Hi everyone, I'm trying to conditionally format dates in a pivot table which all come under 1 row. If the date is less that today i want the cell to be red, if its equal to or between 5 days before today i want it to be orange, and if its over today i want it to be green. Thanks Hi Hover over the Date field until you see a solid black downward pointing arrow. That will select the whole of the date field Using the cell reference for the first cell in the date range, e.g. C4 Format>Conditional Formatting>Formula is =C4=Today() format Red =And(C4<today(),C4>=today()-5) ...

Auto generate numbers in decending order in a column
I just want to know how I can set up a column to auto generate numbers in decsending order? You could add a formula: =if(isblank(b2),"",a2+1) would be it for row 3 if you, for instance, type a date in column B. But you'd need a macro to make it happen automatically. ************ Anne Troy www.OfficeArticles.com "JsJ" <JsJ@discussions.microsoft.com> wrote in message news:FAF083BF-4AD3-4387-A1EA-84E01EC33538@microsoft.com... >I just want to know how I can set up a column to auto generate numbers in > decsending order? In article <FAF083BF-4AD3-4...

Re-Post Append to Append Query
Hi there, Awhile back, I posted about the following: Have a main form and sub form. When generate a new form, fill in the main form and tab to sub-form, an append query is executed that populates the questions and possible answers for the questionnaire selected in the main form. The user then selects there response. Now, however, additional questions have been added. This means I need to execute some kind of additional append query that will ignore question ids that have already been populated, and add new ones. As he so often does, Allen Brown very kindly posted responses and I ha...

Relinking tables
I develop in 1 frontend which is linked to backend 1 (all users have their own frontend). Another department uses the exact same application but with their own backend, backend 2. Whenever the frontend is updated I would like to run a routine that relinks a copy of the frontend to backend 2. I'd like the code to run without prompting. I tried (modifying) this code http://www.mvps.org/access/tables/tbl0009.htm but it's over my head. I can't get it to work. I don't really get what all the code does. Is there some more simple code or other ideas to make this routine? Thank ...

Sharing lead & opportunity tables
Hi, Does anyone know which tables store info regarding lead/opportunity that are being shared to other users? Thanks Carrie Hi Carrie, The lead and opportunity information is stored in the leadbase and opportunitybase tables. Information regarding the sharing with other users, is stored in the principalobjectaccess table. The settings in this table are reflected in the SecurityDescriptor field with every lead and opportunity. Hope this helps, Ronald Lemmen Avanade Netherlands "Carrie" wrote: > Hi, > > Does anyone know which tables store info regarding lead/oppor...

what is the perl table ??? and how i can use this
...

auto email worksheet
Is there a macro I can write that would email my worksheet 6 days a week (no Sunday) Cheers!!! Use Windows Scheduled Tasks to run your macro at specific times/days. The basics of the macro would be something like this.... '/========================================/ ' Sub Purpose: example of how to send a file attachment ' Requires reference to Microsoft Outlook '/========================================/ ' Public Sub SendTheEmail() Dim olApp As Outlook.Application Dim objMail As Outlook.MailItem Dim objAttachments As Outlook.Attach...