Appending to a table + Number

[triplell89]

So I have a table, and I want to add some information from it to the bottom of another table. I'm using the append function, and I want one of the fields to be incremented for the additional rows. IE, my current table ends at 12000, and I want to append 147 rows to this table. For the first record I append, I want the ID to be 12001, and the second 12002, and so on.

I tried using an expression that took the max value of the field, and then add 1 to it, however it just does it for every field (all 147 fields assigned the value 12001). Next I tried to have that expression increment itself, and I got a circular reference error.

I was hoping someone could help me figure this out.

Appreciated

 

[triplell89]

Is there a reason there is 15 views and no replies?

 

[Kryst51]

Generally when people view, and either don't want to answer, or don't know the answer, as in my case, they don't post. Also, I want to know the answer, also, people may look at a post, even if they don't intend on answering, to see what's going on with it, maybe learn something from it, which is also what I want to do.

At least I assume this is what is going on.... But we all know what assuming does.

 

[triplell89]

I might just end up writting some vba code that will increment it for me...however I've never used VB along with access.

 

[Kryst51]

Oh, just a thought. Is your key an autonumber if so, wouldn't it just give the next number in the sequence to the appended records?

 

[Scooterbug]

Well, off the top of my head, I would say you would append the data without the ID number. Then loop through the records that have a null value on the ID number, incrementing the ID number by 1 with each record that doesn't have the ID value.

Looping isn't my strong point. I have the general idea, but for me, I like to use trial and error when figuring it out.

 

[Kryst51]

??? Scooterbug, I don't understand. If the table he's appending to has an autoID originally wouldn't the records being added also need an autoID number when they are added to the table? and not only need it, but automatically be assigned it?

 

[Scooterbug]

??? Scooterbug, I don't understand. If the table he's appending to has an autoID originally wouldn't the records being added also need an autoID number when they are added to the table? and not only need it, but automatically be assigned it?

Based off of what he is wanting to do, I am assuming the the ID he wants isn't an autonumber. An autonumber should only really be used to mark a record with an unique identifer. I am also running under the assumption that the ID he wants to incriment isn't a primary key. I'm sure he has a reson for wanting the numbers to start at 12000. Which made me think and have a couple of questions for the orgininal poster.

What is the number being used for? Also, where is this data that is being appended coming from. If it's from another table, the easiest thing to do is to have the number generated at the time of the record. If it's a file you are importing, using excel it would be easy to add the number into it's own column before importing.

 

[Kryst51]

OK, Thanks for answering my question. I was assuming too much.

 

[Scooterbug]

No problem...I try and not read too much into what other are describing. I know everybody has their own way of doing things, so I try not to assume that the way they want to do things is the way I would. Done that a few times and had to do the old "Doh!" afterwards

 

[triplell89]

I know how I could do it in excel, however I know you should be able to do it in access, and I don't want have to import to excel and back to access every time I do this.

The number's are not unique, and I can't simply go back and change it to an auto number. They are used in a heiarchy that associates with a 1 to many relationship.

In other words, I am trying to append the new children to the table.

here is the sql

INSERT INTO [Entity List] ( Name, EntityID )
SELECT DISTINCT [GS listing].GSName, [12267]+1 AS Expr1
FROM [SRT Counts 2009] INNER JOIN [GS listing] ON [SRT Counts 2009].[Sender Code] = [GS listing].GS
WHERE ((([GS listing].EntityID) Is Null) AND (Not ([GS listing].GSName) Is Null));

I would rather have expr1 as expr1+1 as expr1, however this causes an error (circular reference) most likely because of the which came first dilema. So if I could assign a starting value to expression 1 for the first cell, and then have it increment for every cell after that...that would be great

 

[Scooterbug]

I mentioned excel in case you were importing the data before you appended. Since you are not, then that is not an option.

In your case, I would go with the loop option. Like I said, I'm not too proficent with looping. First, append the data to the Entity List (not a good idea to have spaces in table names btw) without the EntityID. Then, run a loop like this:

dim db as dao.database
dim rs as dao.recordset
dim stSql as string
dim lngNextEntityID as long
 
stsql = "SELECT [Entity List].* FROM [Entity List] WHERE ((([Entity List].[EntityID] is Null));"
 
lngNextEntityID = dmax("[EntityID]","Entity List") + 1
 
set db as CurrentDB
set rs as db.Openrecordset (stSql)
 
with rs
    Do
        do until .eof
        .Edit
        .Fields("EntityID") = lngNextEntityID
        lngNextEntityID = lngNextEntityID + 1
        .MoveNext
            if .EOF then
              Exit Do
            end if
   Loop
Loop until .eof
 
.close
 
End With

Now, like I said, i'm not too proficient with Loops...so there may be errors in this. If anybody else wants to pipe in...feel free.

 

[triplell89]

yeah...I knew I could use some vba code to do it, and I know it's not a good idea to have spaces in table names/field names...I didn't make the table ...

Thanks for your help though. I'm pretty good with VB, so I should be able to handle it. Btw, you're code looks pretty good.

 

[Scooterbug]

Hope it works out...and make sure you smack the person who made the table with a wet noodle for his error