ODBC error - SQL backend cannot see newly inserted ID field. 1

[mrsbean]

I have an Access mdb which was created with a considerable amount of
development before the back end was switched to SQL Server. The
following code worked when the backend was Jet:

Private Sub Form_AfterInsert()
On Error GoTo ErrorHandler

'Lookup record in inventory.
'if an inventory record with this pickid already exists, update it
'if it doesn't exist yet, add it to inventory

cmdSaveRec_Click

Dim ThisPick As Long
Dim strSQL As String

DoCmd.SetWarnings (0)
ThisPick = DCount("PickID", "tblInventory", "[PickID] = " &
Me.PickID)

If ThisPick = 1 Then
strSQL = "Update tblInventory set CatalogueID = " & Me.CatalogueID
& ", Quantity = (" & Me.QtyPicked & "* -1) where PickID = " &
Me.PickID
DoCmd.RunSQL strSQL
End If

If ThisPick = 0 Then
'insert a matching record for this PickID into inventory table
strSQL = "INSERT INTO tblInventory ( CatalogueID, Quantity, invDate,
PickID ) " & _
"SELECT " & Me.CatalogueID & " AS Expr1, (" & Me.QtyPicked & " * -1)
AS Expr2, #" & Now() & "# AS Expr3, " & Me.PickID & " as Expr4 ;"
Me.SQLStatement = strSQL
Me.SQLStatement.Requery

DoCmd.RunSQL strSQL
End If

DoCmd.SetWarnings (1)


ErrorHandlerExit:
     Exit Sub
ErrorHandler:
     msgbox err.number & " : " & err.message
     Resume ErrorHandlerExit

End Sub

Problem now is that the form cannot see the PickID, so it cannot use
it to perform the insert statement into tblInventory. I get an ODBC
error. I have tried a few things which didn't work. What can I do
to allow my form to see the PickID which was just inserted into the
SQL table, tblPick?

Thanks in advance,

MrsBean

 

[ItIsHardToProgram]

I am not quite sure why you are getting an error.... does it appear on your dcount, on when you are trying to update or insert..... when you say just inserted into the SQL table, do you mean that it was in the same event, or on 2 sequential events?

 

[mrsbean]

Since this code is an AfterInsert event, it is 2 sequential events. A record was inserted. The insert fired this event.

MrsBean

 

[ItIsHardToProgram]

Have you try a refresh or any familiar command, and is there an ADODB connection to your database, those might do it...

 

[CaptainD]

try changing the "#" signs to single quotes.

 

[mrsbean]

ItIsHardToProgram,

In the code, I have a little trick which drives the SQL string out to a text box so I can see what it is doing. The problem is not with the date portion of the SQL statement. It is with the PickID. The form cannot see the PickID, and therefore, there is nothing there for the Insert Statement. The insert statement tries to insert a null value and it fails.

MrsBean

 

[mrsbean]

ItIsHardToProgram,

I have tried Me.Requery and Me.Refresh. I have also tried Me.PickID.Requery and Me.PickID.Refresh, but it is as if the requery didn't occur ... nothing happens.

MrsBean

 

[ItIsHardToProgram]

So basically, it can't see the pick ID on the form? try a Me.Recalc before you call in the 2nd routine. Ive had familiar problems...

Try this test. write in this code
Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
msgbox me.pickID
End Sub

Then move around with the mouse, see if it always returns a null value or if it takes a couple of seconds. if the recalc dosnt work, theres always the option of a timer...?

 

[PHV]

Was PickID an AutoNumber ?
If yes then do a search for @@identity here:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrsbean]

phv,

Yes, PickID is an autonumber. At first glance, the article you gave seemed to be more technical than I could handle today, so with the information you gave, I was able to work toward a solution. I made triggers in the SQL back-end which would automatically update the tblInventory when something is added, updated or deleted from tblPick. Thanks!

MrsBean