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
> Insert, Access 2016    
 
   
mike60smart
post Jan 20 2020, 03:30 PM
Post#1


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am getting the following error when I run the following Insert On Click Event:-

Attached File  error.PNG ( 3.17K )Number of downloads: 0


The On Click Event Code is as follows:-
CODE
Private Sub cmdInsert_Click()

10        On Error GoTo cmdInsert_Click_Error
      Dim strSQL As String
      Dim lngCustomerPurchaseOrderID As Long
      Dim lngSupplierID As Long
      Dim lngMaterialID As Long
      Dim dtMaterialDate As Date
      Dim strItemDescription As String
      Dim strItemSKU As String
      Dim curItemUnitPrice As Currency
      Dim lngMarkupTypeID As Long

20    lngCustomerPurchaseOrderID = Me.CustomerPurchaseOrderID
30    lngSupplierID = Me.txtSupplier
40    lngMaterialID = Me.txtMaterials
50    dtMaterialDate = Date
60    strItemDescription = Me.txtDesc
70    strItemSKU = Me.txtSKU
80    curItemUnitPrice = Me.txtPrice
90    lngMarkupTypeID = Me.txtMU


      ' add new record in tblPOMaterialsDetailsTable for Supplier, Materials, Description,SkU,Item Price and Markup
100            strSQL = "INSERT INTO tblPOMaterialsDetailsTable (CustomerPurchaseOrderID,MaterialDate,SupplierID,ItemSKU,MaterialID,ItemDescr
iption,ItemUnitPrice,MarkupTypeID ) " _
                  & "Values (" & lngCustomerPurchaseOrderID & ", " & Format(dtMaterialDate, "\#mm\-dd\-yyyy\#") & "," & lngSupplierID & ",'" & strItemSKU & "'," & lngMaterialID & ",'" & strItemDescription _
                  & "', '" & curItemUnitPrice & "'," & lngMarkupTypeID & ";"
110         Debug.Print strSQL
120         CurrentDb.Execute strSQL, dbFailOnError


130       Me!frmPOMaterialsDetailsTablesubform.Form.Requery


          
140       On Error GoTo 0
150       Exit Sub

cmdInsert_Click_Error:

160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInsert_Click, line " & Erl & "."

End Sub

The Immediate window displays the correct values as shown:-

[Code]
INSERT INTO tblPOMaterialsDetailsTable (CustomerPurchaseOrderID,MaterialDate,SupplierID,ItemSKU,MaterialID,ItemDescr
iption,ItemUnitPrice,MarkupTypeID ) Values (18, #01-20-2020#,5,'BALLVALVE',43,'3/8" IPS Ball Valve', '8.48',4;


[/Code]

Any Help appreciated.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 20 2020, 03:39 PM
Post#2


UtterAccess Moderator
Posts: 12,291
Joined: 6-December 03
From: Telegraph Hill


Good evening, Mike.

You have omitted the closing bracket after your VALUES list.

Amend the last continuation of line 100 to:
CODE
                  & "', '" & curItemUnitPrice & "'," & lngMarkupTypeID & ");"


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 20 2020, 04:14 PM
Post#3


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Good Evening David

Now get this error:-

Attached File  error.PNG ( 5.82K )Number of downloads: 0


Have double checked spelling and all ok ?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 20 2020, 04:27 PM
Post#4


UtterAccess Moderator
Posts: 12,291
Joined: 6-December 03
From: Telegraph Hill


Can you post a screenshot of table tblPOMaterialsDetailsTable in design view?

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Jan 20 2020, 04:27 PM
Post#5



Posts: 3,472
Joined: 27-February 09



Mike,

Whenever I write code like this and it fails, the first thing I do is print the SQL string to the immediate window and paste it into a new query and try running it. Then I have at least a clue of what's wrong.

Pieter
Go to the top of the page
 
mike60smart
post Jan 20 2020, 04:42 PM
Post#6


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

Here with the structure

I have corrected the MaterialsDate field in the SQL

Attached File  Table.PNG ( 10.18K )Number of downloads: 0

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Jan 20 2020, 04:44 PM
Post#7


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Hi Pieter

Tried that and it still errors on the name CustomerPurchaceOrderID ?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 20 2020, 04:46 PM
Post#8


UtterAccess Moderator
Posts: 12,291
Joined: 6-December 03
From: Telegraph Hill


CODE
CustomerPurchaseOrderID <> CustomerPurchaceOrderID
              ^                          ^
              |                          |


;-)

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Jan 21 2020, 08:15 AM
Post#9


UtterAccess VIP
Posts: 13,658
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

God the number of times I looked at that.

Many thanks yet again
cheers.gif


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Jan 21 2020, 08:17 AM
Post#10


UtterAccess Moderator
Posts: 12,291
Joined: 6-December 03
From: Telegraph Hill


dazed.gif ohyeah.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 11:03 AM