Full Version: Dlookup Error
UtterAccess Forums > Microsoft® Access > Access Forms
adam
Hi all
when i used Dlookup Function i get Run-time error '8115': Arithmetic overflow error converting numeric to data type numeric. Could anyone tell me what I am doing wrong
ScottGem
Can we see the function and where its used ans what data types its using?
Jack Cowley
Post your code so someone here can see what you are doing....
ack
adam
Private Sub Item_Cost_GotFocus()
im x As String
x = DLookup("[Item_Key]", "Items_Balance", "[Item_Key] =" & Me.Item_Key)
If x = "" Then
Dim cmd As ADODB.Command
Dim paramYourParameter As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter = New ADODB.Parameter
With paramYourParameter
.Name = "@Param1"
.Type = adVarChar
.Size = 2
.Value = Me.Company_Code
End With
Dim paramYourParameter1 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter1 = New ADODB.Parameter
With paramYourParameter1
.Name = "@Param2"
.Type = adVarChar
.Size = 3
.Value = Me.Branch_Code
End With
Dim paramYourParameter2 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter2 = New ADODB.Parameter
With paramYourParameter2
.Name = "@Param3"
.Type = adVarChar
.Size = 4
.Value = Me.Store_Code
End With
Dim paramYourParameter3 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter3 = New ADODB.Parameter
With paramYourParameter3
.Name = "@Param4"
.Type = adVarChar
.Size = 2
.Value = Me.Coding_Group_Code
End With
Dim paramYourParameter4 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter4 = New ADODB.Parameter
With paramYourParameter4
.Name = "@Param5"
.Type = adVarChar
.Size = 40
.Value = Me.Item_Code
End With
Dim paramYourParameter5 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter5 = New ADODB.Parameter
With paramYourParameter5
.Name = "@Param6"
.Type = adVarChar
.Size = 3
.Value = Me.Location_Code
End With
Dim paramYourParameter6 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter6 = New ADODB.Parameter
With paramYourParameter6
.Name = "@Param7"
.Type = adVarChar
.Size = 4
.Value = Me.Lot_Number
End With
Dim paramYourParameter7 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter7 = New ADODB.Parameter
With paramYourParameter7
.Name = "@Param8"
.Type = adVarChar
.Size = 8
.Value = Me.Srial_No
End With
Dim paramYourParameter8 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter8 = New ADODB.Parameter
With paramYourParameter8
.Name = "@Param9"
.Type = adDate
.Size = 8
.Value = Me.Expired_Date
End With
Dim paramYourParameter9 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter9 = New ADODB.Parameter
With paramYourParameter9
.Name = "@Param10"
.Type = adVarChar
.Size = 50
.Value = Me.Dimension1
End With
Dim paramYourParameter10 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter10 = New ADODB.Parameter
With paramYourParameter10
.Name = "@Param11"
.Type = adVarChar
.Size = 50
.Value = Me.Dimension2
End With
Dim paramYourParameter11 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter11 = New ADODB.Parameter
With paramYourParameter11
.Name = "@Param12"
.Type = adVarChar
.Size = 50
.Value = Me.Dimension3
End With
Dim paramYourParameter13 As ADODB.Parameter
Set cmd = New ADODB.Command
Set paramYourParameter13 = New ADODB.Parameter
With paramYourParameter13
.Name = "@Param14"
.Type = adVarChar
.Size = 100
.Value = Me.Item_Key
End With
With cmd
.Parameters.Append paramYourParameter
.Parameters.Append paramYourParameter1
.Parameters.Append paramYourParameter2
.Parameters.Append paramYourParameter3
.Parameters.Append paramYourParameter4
.Parameters.Append paramYourParameter5
.Parameters.Append paramYourParameter6
.Parameters.Append paramYourParameter7
.Parameters.Append paramYourParameter8
.Parameters.Append paramYourParameter9
.Parameters.Append paramYourParameter10
.Parameters.Append paramYourParameter11
.Parameters.Append paramYourParameter13
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "insert_New_ItemBalance" 'storedprocedure
.Execute
End With
Else
Exit Sub
End If
End Sub
Jack Cowley
Does your code choke on:

x = DLookup("[Item_Key]", "Items_Balance", "[Item_Key] =" & Me.Item_Key)

Why do you need to look up Item_Key as it appears that it is in a control on your form (Me.Item_key). Why no use:

If IsNull(Me.Item_Key) or Me.Item_Key = "" Then
...rest of your code...

If you are trying to find out if Item_Key exist in the table:

If IsNull(DLookup("[Item_Key]", "Items_Balance", "[Item_Key] =" & Me.Item_Key)) Then
...rest of your code...

hth,
Jack
Edited by: Jack Cowley on Fri Dec 9 15:00:12 EST 2005.
adam
thanks Jack
Oused
If IsNull(DLookup("[Item_Key]", "Items_Balance", "[Item_Key] =" & Me.Item_Key)) Then >>
but it Returened the sam Error
Jack Cowley
Is Item_Key a number? What type of number (Integer, Long Integer, etc.)? How many records are in the table Items_Balance? Are you trying to find out if a specific Item_Key exits in the table? If so, why not use Find in your ADO code as it will be much faster than DLookup()?
ack
adam
Item_Key Data type is Varchar
yes i trying to find if specific Item_Key exist in the table or not
Jack Cowley
Varchar? I do not have Access 2003 so I have no idea what a Varchar is as there is no such thing in previous versions of Access. Can you post one of the items in the Varchar field of your table?
ack
adam
its a sql server data type not access data type
I use access Project with sql Server database
freakazeud
Varchar is a regularly used data type of SQL columns. It can be used for strings (text). So any SQL statement like:
CREATE TABLE ... could use varchar
HTH
Good luck
Jack Cowley
I sorry that you did not mentioned this earlier as it would have saved you some time as I do not work with the SQL Servers or Access Projects. I would suggest you start a new post with just your DLookup code and the fact that you are using an Access Project and SQL server. Someone with knowledge in this area will probably have the answer you need.
My apologies for not having a solution for you on this.
Jack
adam
Are ther anther way to know if Item_Key exist in the table or not
Jack Cowley
I just saw Freakazeuds post. Try this:
CODE
If Not IsNull(DLookup("[Item_Key]", "Items_Balance", "[Item_Key] = '" & Me.Item_Key & "'") Then

hth,
Jack
adam
thanks Jack
you helped me befeor in many problems
thanks for your efforts
adam
yes Jack
thanks
adam
i solved the problem by useing recordset with this code
im rsRec AS ADODB.Recordset
Dim CNN As ADODB.Vonnection
CNN.CursirLocation=adUserClint
CNN.Connection="Connection String"
CNN.Open
rsRec.open"Select [Item_Key] From [Item_Balance] Where [Item_Key]=''' & Me.Item_Key&"",CNN
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.