INSERTing a new record

[dsmaj]

Hey, I've got a form that a user can "Create New" item effectively adding it to a table, however, lots of supporting data needs to be filled out in the form, and some supporting forms as well (not subforms) before the user can ultimately "Save" or "Cancel" the Add-item form. In the CreateNew_Click event I want to create the new record in the table, which has an auto-numbering ID field which is the key. Anyhow, in the Click event I'm running the SQL code

INSERT INTO tbl_Initiative (InitiativeName)
VALUES ('*******NEWINITIATIVE*******');

Do I have to now run a separate SELECT statement to find the the InitiativeID for the record with InitiativeName='*******NEWINITIATIVE*******' ? Or is there a way for me to return the autonumbered InitiativeID field from the newly created record?

Thanks in advance,
Sam.

 

[Kodo]

After your Insert statement run this sql code

SQL="@@IDENTITY"
set rs=connection.execute(SQL)
LastID=RS(0)

'close/clean up

 

[dsmaj]

After your Insert statement run this sql code

SQL="@@IDENTITY"
set rs=connection.execute(SQL)
LastID=RS(0)

'close/clean up

Thanks Kodo, I'm noticing now that my INSERT INTO statement is in fact not inserting a new record into my table. Is there some special way of doing this when the key field for the table in question is autonumbered?

Edit: Additionally, here's a dumb question: I've got my INSERT INTO query as a QueryDef (is it worth doing this for speed in this situation?) Is there an easy way for me to run it in code? I.e. can I just go CurrentDB.execute("qry_CreateNewInitiative") or something? Or do I have to go through the process of creating a querydef object and do it that way?


Thanks,
Sam

 

[Kodo]

lets see what you have and we'll fix it.

 

[dsmaj]

lets see what you have and we'll fix it.

I've just got a saved query called qryCreateNewInitiative that has the following sql in it:
INSERT INTO tbl_Initiative ( InitiativeName )
VALUES ('*******NEWINITIATIVE*******');

Then I've got a form that needs to run that query. I know that I can use a QueryDef object to open and run that query, but I'm just wondering if there's a quick one-liner to run a saved query like that.

Through testing that query though (via the QueryDef method), I'm getting an error due to a key violation.

FYI, tbl_Initiative has about 10 fields in it, the first one is InitiativeID, which is the keyfield, and is autonumbered. The only field I want to set is InitiativeName as I have done in the INSERT INTO code (however, I may not even need to do this if I can access the the InitiativeID of the newly created row using the code you supplied above). Anyway, any ideas?

Thanks for your help,
Sam.

 

[Kodo]

you kind of lost me here:
however, I may not even need to do this if I can access the the InitiativeID of the newly created row using the code you supplied above

is that query the ONLY way a new record is created?

 

[dsmaj]

you kind of lost me here:
however, I may not even need to do this if I can access the the InitiativeID of the newly created row using the code you supplied above

is that query the ONLY way a new record is created?

Yes, that is the code I will use to create a new record. After it runs, I need another query to fetch the InitiativeID of the record that was just created (unless there is some way to get it via the UPDATE query?) After the new record is created, the user performs a bunch of data entry via related forms which deal with related tables (hence why I need the InitiativeID of the initiative that was just created). I hope this makes some sense?

Thanks,
Sam

 

[Kodo]

ok, here's what my code would look like(ADO).

Dim your variables.

'MAKE NEW RECORD
set conn=currentproject.connecton
SQL="INSERT INTO tbl_Initiative ( InitiativeName )
VALUES ('*******NEWINITIATIVE*******')"
conn.execute(SQL)

'GET ID OF NEWLY CREATED RECORD
SQL="@@IDENTITY"
set rs=conn.execute(SQL)
LastID=RS(0)

rs.close
set rs=nothing
conn.close
set conn=nothing

 

[dsmaj]

ok, here's what my code would look like(ADO).

Dim your variables.

'MAKE NEW RECORD
set conn=currentproject.connecton
SQL="INSERT INTO tbl_Initiative ( InitiativeName )
VALUES ('*******NEWINITIATIVE*******')"
conn.execute(SQL)

'GET ID OF NEWLY CREATED RECORD
SQL="@@IDENTITY"
set rs=conn.execute(SQL)
LastID=RS(0)

rs.close
set rs=nothing
conn.close
set conn=nothing

Two things---
1) I'm using DAO, but that's no biggie, I can change the syntax
2) My INSERT INTO query is stored in access so that I can take advantage of the speed of a stored QueryDef. The query is called qry_CreateNewInitiative...is there an easy one liner way of calling this query, so that I don't have to create a QueryDef object and deal with it that way?

Also, with the INSERT INTO query just specifying an InitiativeName value, I'm still getting a keyfield violation when I try and run the query...my keyfield is autonumbered remember--is there some special way of dealing with this?

Thanks,
Sam

 

[Kodo]

sounds like that keyfield is related to another that has to have an existing record first.


Conn.execute(qry_CreateNewInitiative)

 

[dsmaj]

sounds like that keyfield is related to another that has to have an existing record first.


Conn.execute(qry_CreateNewInitiative)

Ah! That was a slight brain-fart...thanks Kodo.

Sam.

 

[dsmaj]

edit: nevermind...fixed.

 

[dsmaj]

Hey Kodo, that "@@IDENTITY" doesn't seem to be recognized as valid SQL...What's going on with that statement, and where can I find out more about it?

Thanks,
Sam

 

[Kodo]

http://support.microsoft.com/defaul...port/kb/articles/Q232/1/44.ASP&NoWebContent=1

 

[dsmaj]

Alternatively, if your tables are Jet, you can use the .AddNew Method of DAO or ADO and after the .AddNew and before the .Update, you will be able to copy the newly assigned autonumber to a variable so it will be available elsewhere.

If use the recordset objects .AddNew method, is the current record of the recordset object then the newly added record? Or do I need to .MoveLast first?

EDIT: Also, wouldn't it be much faster to use a stored querydef to INSERT a new record rather than opening the table in a recordset in code in order to use .AddNew? Or is there a way to do it without opening the whole table?

Thanks for the replies,
Sam.