UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> DoCmd.RunSQL ("INSERT INTO...) Newly created ID into another tab    
 
   
zedleb
post Aug 14 2008, 04:52 AM
Post#1



Posts: 54
Joined: 18-July 08
From: Cumbria, UK


Hi there - not sure if this should be in Forms or Tables forum, but the code's on a form so I'll make a stab here!
I am trying to populate two tables following an onclick on a form's command button. The user will have entered a (purchase order) number into a text box and clicked the command button.
This will run a couple of checks then if all OK will create two new records in
1. tblReceivedNote
2. tblReceived
In tblReceivedNote a new record is created with autonumber (ReceivedNoteID), a delivery reference that is provided via an inputbox and a date. (I've got this bit working!)
In tblReceived I'd like the newly created ReceivedNoteID to be part of a new record along with the purchase order number that the user had entered earlier. I've got as far as creating the record with the purchase order number - but just don't know how to go about capturing the ReceivedNoteID to populate this new record.
The ReceivedNoteID is the key between the two tables. I'm keeping things in two tables in case of incomplete orders when an orderline may be only be completed via two deliveries (two ReceivedNoteIDs).
Can anyone suggest a way of doing this? If you think I'm going about this the wrong way - I'm open to hear that too sad.gif
My code so far is below - the bit I'm struggling with in red:
-------------------------------------------------------------
Private Sub cmdCheckOff_Click()
Dim cn As ADODB.Connection
Dim OrderID As String
Dim Ref As String

Set cn = CurrentProject.Connection

OrderID = Me.txSearchCheckOff.Value

'check to see whether this is local delivery
If DLookup("[Stores_Delivery]", "tblOrder", "[Order_ID] = " & CStr(OrderID)) = False Then
myDisplayInfoMessage ("This is not a Stores delivery")
Exit Sub


Else

'check to see whether tblReceived already has record of Order_ID matching OrderID
If DLookup("[Order_ID]", "[tblReceived]", "[Order_ID] = " & CStr(OrderID)) Then
'Record exists
'open form for checking off
DoCmd.OpenForm "frmReceived", acNormal, , "[tblReceived.Order_ID] = " & CStr(OrderID)
Exit Sub

Else
'create new record in tblReceivedNote
Ref = InputBox("Delivery Note Reference")

DoCmd.RunSQL ("INSERT INTO [tblReceivedNote]([Received_Note_Reference]) " & _
"VALUES ('" & Ref & "'); ")

DoCmd.RunSQL ("INSERT INTO [tblReceived] ([Order_Detail_ID], [Order_ID]) " & _
"SELECT [tblOrderDetails].[Order_Detail_ID], [tblOrderDetails].[Order_ID] " & _
"FROM [tblOrderDetails] " & _
"WHERE ([tblOrderDetails].[Order_ID])= " & CStr(OrderID) & "; ")


'open form for checking off
DoCmd.OpenForm "frmReceived", acNormal, , "[tblReceived.Order_ID] = " & CStr(OrderID)

End If
End If
End Sub
--------------------------------------------------
Thank you, anyone, in advance!
Go to the top of the page
 
niesz
post Aug 14 2008, 07:55 AM
Post#2


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


Consider the following function and see if you can adapt it to fit your needs. Let us know if you need additional assistance.
!--c1-->
CODE
Public Function test()

    Dim sql As String
    Dim MyLastId As Long
    
    sql = "INSERT INTO YourTable (YourField) VALUES ('xxx')"
    
    With CurrentProject.Connection
        .Execute sql
        MyLastId = .Execute("SELECT @@IDENTITY")(0)
    End With
    
    Debug.Print MyLastId
            
End Function
Go to the top of the page
 
niesz
post Aug 14 2008, 07:59 AM
Post#3


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


Also, there appears to be some unnecessary code in your example.

The DIM'ing of an ADODB recordset and the setting of it are not needed.
Go to the top of the page
 
zedleb
post Aug 14 2008, 08:40 AM
Post#4



Posts: 54
Joined: 18-July 08
From: Cumbria, UK


Hi Walter
ey thank you so much for replying - I was watching my sorry little post sitting with no replies and thinking I'd done a Wrong Thing sad.gif
I've had a shot with your code - I'd never heard of SELECT @@IDENTITY before. It's picking up the ID no problem! I'm just stuck now with how I get MyLastId into tblReceived. If MyLastId is found via this Public Function - is it carried back into the Private Sub where my DoCmd is?
Otried putting it in the code below (in red), but this just gives me a popup requesting MyLastId. Do you know how I can get MyLastId into tblReceived?
-------------------
DoCmd.RunSQL ("INSERT INTO [tblReceived] ([Order_Detail_ID], [Order_ID], [Received_Note_ID]) " & _
"SELECT [tblOrderDetails].[Order_Detail_ID], [tblOrderDetails].[Order_ID], MyLastId " & _
"FROM [tblOrderDetails] " & _
"WHERE ([tblOrderDetails].[Order_ID])= " & CStr(OrderID) & "; ")
--------------------
Cheers
Zoe
Go to the top of the page
 
niesz
post Aug 14 2008, 08:47 AM
Post#5


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


The only reason I made it a Public Function was to test it in my environment.

You can simply place that code within your Sub and the MyLastId variable will be available to the rest of your code. You'll need to have the variable outside of the string.

FWIW, I would not use DoCmd.RunSQL, but would execute this second statement exactly like the first.
Go to the top of the page
 
zedleb
post Aug 14 2008, 09:19 AM
Post#6



Posts: 54
Joined: 18-July 08
From: Cumbria, UK


Ah-ha, I see! It's a nightmare to expose my total muppetry for all to see sad.gif
I have done what you suggested and it's all running happily in the sub. The tables are being populated just as required. Brilliant!
Thank you so much for all your help.
Just as a consideration - in a multi-user environment do you think there's a possibility that the wrong identity could be picked up? It's not so likely at the moment, but if there's a risk, I might need to think of another way of dealing with this issue.
Zoe
Go to the top of the page
 
niesz
post Aug 14 2008, 09:23 AM
Post#7


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


I believe because we're using the WITH...END WITH construct of the CurrentProject.Connection object, that no overlap is possible. The IDENTITY that is retrieved is only the one from this current instance, and not from any others.
Go to the top of the page
 
zedleb
post Aug 14 2008, 09:33 AM
Post#8



Posts: 54
Joined: 18-July 08
From: Cumbria, UK


Good enough for me!
And thanks again for your time and expertise - this forum is fantastic sad.gif
Zoe
<><
Go to the top of the page
 
niesz
post Aug 14 2008, 09:35 AM
Post#9


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


thumbup.gif
Go to the top of the page
 
LPurvis
post Aug 14 2008, 10:04 AM
Post#10


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


>> "I believe because we're using the WITH...END WITH construct of the CurrentProject.Connection object, that no overlap is possible. The IDENTITY that is retrieved is only the one from this current instance, and not from any others. "
orrectamundo! :-)
(Sorry for brevity - low on time ;-)
Go to the top of the page
 
niesz
post Aug 14 2008, 10:11 AM
Post#11


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


Thanks for the confirmation! cool.gif
Go to the top of the page
 
dromorkid
post Mar 24 2009, 07:54 AM
Post#12



Posts: 373
Joined: 4-November 08



I'm trying to work this code in order to insert a newly created PK into a related table.
I'm most certainly doing it wrong as its not working!! I'm also not pretending to be a VBA author, therefore I know the problem lies within the code and how I'm trying to work it.
I'm trying to perform 2 inserts on the same Command (OnClick). Is this wrong?
What I'm doing is inserting 2 values into 2 fields (ommitting the PK as it will be generated automatically as these 2 fields create a new record).
This works. I have a new record.
Now on the same Command button I'm trying to insert another 3 fields into a related table.This could be wrong to try and do it on the same Command button, and also I've defined 2 strings, strSQL and strSQL2 to take care of both these inserts seperately. This may also be wrong.
The resulting problem on the second insert is
CODE
Syntax Error (missing operator) in query expression '#1/1/2018#0'.

Ocan tell its not picking up the newly created PK from the first insert as when I hover over MyLastID, it reads MyLastID = 0.
I'm not sure of the problem and hope you can assist!
Thanks,
MY CODE
CODE
Private Sub Command24_Click()
Dim strSQL As String
Dim strSQL2 As String
Dim MyLastID As Long
strSQL = "INSERT INTO [tblJobDrawingsInfo] " _
& " ( [JobDrawings_Name], [Job_ID] )" _
& " SELECT '" & JobDrawings_Name & "', " _
& Job_ID & "" _
& ";"
CurrentDb.Execute strSQL, dbFailOnError
CurrentDb.TableDefs.Refresh
strSQL2 = "Insert Into [tblVersionInfo] " _
& " ( [Version_Name], [Version_Date], [JobDrawings_ID])" _
& " SELECT '" & Version_Name & "', " _
& "#" & Version_Date & "#" _
& MyLastID & "" _
& ";"
With CurrentProject.Connection
.Execute strSQL2
MyLastID = .Execute("SELECT @@IDENTITY")(0)
End With
Debug.Print MyLastID
End Sub
Go to the top of the page
 
niesz
post Mar 28 2009, 06:10 PM
Post#13


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


I think you want something like this (I edited the SQL to be a bit more readable and removed all of the "extra things" the query builder creates):

CODE
Private Sub Command24_Click()
nbsp;   
    Dim strSQL As String
    Dim strSQL2 As String
    Dim MyLastID As Long
    
    strSQL = "INSERT INTO tblJobDrawingsInfo (JobDrawings_Name, Job_ID) SELECT '" & JobDrawings_Name & "', " & Job_ID
    
    With CurrentProject.Connection
        .Execute strSQL
        MyLastID = .Execute("SELECT @@IDENTITY")(0)
    End With
    
    strSQL2 = "INSERT INTO tblVersionInfo (Version_Name, Version_Date, JobDrawings_ID) SELECT '" & Version_Name & "', #" & Version_Date & "#, " & MyLastID
    CurrentProject.Connection.Execute strSQL2
    
End Sub


FYI, the syntax error was because of a missed comma in the SQL after the date.

Also, if JobDrawings_Name, Job_ID, Version_Name, and Version_Date are controls on your form, you should prefix them with ME.

(Ex. Me.Job_ID)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 01:16 AM